Query to list all stored procedures

Question

What query can return the names of all the stored procedures in a SQL Server database

If the query could exclude system stored procedures, that would be even more helpful.

Answer 1

You can try this query to get stored procedures and functions:

SELECT name, type
FROM dbo.sysobjects
WHERE type IN (
    'P', -- stored procedures
    'FN', -- scalar functions 
    'IF', -- inline table-valued functions
    'TF' -- table-valued functions
)
ORDER BY type, name

Answer 2

Just the names:

SELECT SPECIFIC_NAME  
FROM YOUR_DB_NAME.information_schema.routines  
WHERE routine_type = 'PROCEDURE'

Answer 3

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

Answer 4

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

Answer 5

Select All Stored Procedures and Views

select name,type,type_desc
from sys.objects
where type in ('V','P')
order by name,type

Answer 6

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

select *
  from sys.procedures
 where is_ms_shipped = 0