Query to list all stored procedures

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.

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

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


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

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


select *
from sys.procedures
where is_ms_shipped = 0

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

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

This can also help to list procedure except the system procedures:

select * from sys.all_objects where type='p' and is_ms_shipped=0

This, list all things that you want

In Sql Server 2005, 2008, 2012 :


Use [YourDataBase]

EXEC sp_tables @table_type = "'PROCEDURE'"
EXEC sp_tables @table_type = "'TABLE'"
EXEC sp_tables @table_type = "'VIEW'"


OR

SELECT * FROM information_schema.tables
SELECT * FROM information_schema.VIEWS

I wrote this simple tsql to list the text of all stored procedures. Be sure to substitute your database name in <database> field.

use << database name >>
go

declare @aQuery nvarchar(1024);
declare @spName nvarchar(64);
declare allSP cursor for
select p.name from sys.procedures p where p.type_desc = 'SQL_STORED_PROCEDURE' order by p.name;
open allSP;
fetch next from allSP into @spName;
while (@@FETCH_STATUS = 0)
begin
set @aQuery = 'sp_helptext [Extract.' + @spName + ']';
exec sp_executesql @aQuery;
fetch next from allSP;
end;
close allSP;
deallocate allSP;

The following will Return All Procedures in selected database

SELECT * FROM sys.procedures