how to return all store procedure names from a databases?

Hi all,

I require a script to get all store procedure names from a database. I managed to find a script on how to return all the tables names from a db . I was thinking there could be script that could do the same thing but instead it returns the sp names

Thanks

Matthew

One way would be to search the sysobjects table:

select name
from sysobjects
where type = 'P'
order by name

Another (better) alternative is:

select routine_name
from information_schema.routines
where routine_type = 'PROCEDURE'
order by routine_name

|||

With SQL 2005, use:

SELECT Name
FROM sys.procedures

|||

The examples below will return all non-system stored procedures.

Chris

--SQL Server 2000
SELECT [name]
FROM dbo.sysobjects
WHERE OBJECTPROPERTY([id], 'IsProcedure') = 1
AND OBJECTPROPERTY([id], 'IsMSShipped') = 0

--SQL Server 2005
SELECT [name]
FROM sys.procedures
WHERE OBJECTPROPERTY([object_id], 'IsMSShipped') = 0