Query that returns list of all Stored Procedures in an MS SQL database

Question

Is there a query that returns the names of all the stored procedures in an MS SQL database (excluding "System Stored Procedures" would be a nice touch)?

Answer 1

select *
from dbo.sysobjects where xtype = 'P' and status >0

Answer 2

SELECT name, type FROM dbo.sysobjects WHERE (type = 'P')

Answer 3

From my understanding the "preferred" method is to use the information_schema tables:

select * from information_schema.routines where routine_type = 'PROCEDURE'

Answer 4

As Mike stated, the best way is to use information_schema. As long as you're not in the master database, system stored procedures won't be returned.

select * from DatabaseName.information_schema.routines where routine_type = 'PROCEDURE'

If for some reason you had non-system stored procedures in the master database, you could use the query (this will filter out MOST system stored procedures):

select * from master.information_schema.routines where routine_type = 'PROCEDURE' and
Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')

Answer 5

sql server 2005 has views that let you grab sprocs, parameters, tables, columns etc.

Its all built in now!

Answer 6

Unfortunately INFORMATION_SCHEMA doesn't contain info about the system procs.

SELECT *
FROM sys.objects
WHERE objectproperty(object_id, N'IsMSShipped') = 0
    AND objectproperty(object_id, N'IsProcedure') = 1

Answer 7

If you are using SQL Server 2005 the following will work:

select * from sys.procedures where is_ms_shipped = 0

Answer 8

i tried the above query in sql server 2005. its giving invalid object name sys.procedures

Answer 9

If you want to do someting to them check this link:

Answer 10

Hi

Can u tell me how can i get Store proc execution Time and it executed by whom (log details)