How do I list all non-system stored procedures?
- by bubbassauro
I want to create a query to list of all user defined stored procedures, excluding the ones that are system stored procedures, considering that:
Checking the name like "sp_" doesn't work because there are user stored procedures that start with "sp_".
Checking the property is_ms_shipped doesn't work because there are system stored procedures that have that flag = 0, for example: sp_alterdiagram (it is not MSShipped but appears under System Stored Procedures in SQL Server Management Studio).
There must be a property, or a flag somewhere since you can see the "System Stored Procedures" in a separate folder in SQL 2005. Does anyone know?
Edit: A combination of the suggestions below worked for me:
select *
from
sys.objects O LEFT OUTER JOIN
sys.extended_properties E ON O.object_id = E.major_id
WHERE
O.name IS NOT NULL
AND ISNULL(O.is_ms_shipped, 0) = 0
AND ISNULL(E.name, '') <> 'microsoft_database_tools_support'
AND O.type_desc = 'SQL_STORED_PROCEDURE'
ORDER BY O.name
