Retrieving a filtered list of stored procedures using t-sql
15,590
Solution 1
Rather than using the Stored Procedure you can use the following views:
Select * From sys.procedures
Where [Type] = 'P'
or
Select * From Information_Schema.Routines
Solution 2
Select items from the sysobjects table and use a where clause type = 'P'
for stored procedures and filter on name
.
Solution 3
SELECT [Routine_Name]
FROM [INFORMATION_SCHEMA].[ROUTINES]
WHERE [ROUTINE_TYPE] = 'PROCEDURE'
Author by
DanDan
Visit my delicious website at WebBiscuit. Contact me at: [email protected] Find my writing on codeproject.
Updated on June 11, 2022Comments
-
DanDan about 2 years
I'm trying to get a list of stored procedures in t-sql. I am using the line:
exec sys.sp_stored_procedures;
I would like to filter the results back though, so I only get user created stored procedures. I would like to filter out sp_*, dt_*, fn_*, xp_* and everything else that is a system stored procedure and no interest to me. How can I manipulate the result set returned?
Using Sql Server 2008 express.
Solved! Here is what I used:
SELECT name FROM sys.procedures WHERE [type] = 'P' AND name NOT LIKE 'sp_%' AND name NOT LIKE 'dt_%' ORDER BY name ASC;