开发者

Retrieving a filtered list of stored procedures using t-sql

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 [t开发者_Go百科ype] = 'P'
AND name NOT LIKE 'sp_%'
AND name NOT LIKE 'dt_%'
ORDER BY name ASC;


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


Select items from the sysobjects table and use a where clause type = 'P' for stored procedures and filter on name.


SELECT [Routine_Name]
FROM   [INFORMATION_SCHEMA].[ROUTINES]
WHERE  [ROUTINE_TYPE] = 'PROCEDURE'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜