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.

Msg

Answer 1

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_')

Idola

If you create database diagrams you might get a bunch of procs starting with 'dt_' in your database which you can also filter out. – Cake

+1 for information schema. worth a read: msdn.microsoft.com/en-us/library/ms186778.aspx – Ketonuria

It should be "As long as you're not in the [master] or [msdb] databases,..." – Trapezius

On some versions of SQL Server (such as Azure SQL Database), you have to remove the database name from the first query for it to work: SELECT * FROM INFORMATION_SCHEMA.ROUTINES – Mouthwash

Answer 2

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

Fertility

This worked for me in a shared environment in MS-SQL 2008; the previous two did not... – Pop

Anyone using SQL Server 2005 or newer should move away from the dbo.sys* views. This query also: filters out CLR stored procedures, does not filter out system stored procs, and returns [type] when it is known that [type] will always be 'P' since it is the WHERE condition. – Trapezius

it will not work if the objects in the database has different schema – Gallantry

Answer 3

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

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

Elishaelision

the returned records don't seem to have a way to differentiate the system stored procedures – Whichever

Answer 4

The following will Return All Procedures in selected database

SELECT * FROM sys.procedures

Wist

this has got modified and create date, etc. which is very useful – Holbrooke

Answer 5

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

Scutcheon

Answer 6

You can use one of the below queries to find the list of Stored Procedures in one database :

Query1 :

    SELECT 
        *
    FROM sys.procedures;

Query2 :

    SELECT 
        * 
    FROM information_schema.routines 
    WHERE ROUTINE_TYPE = 'PROCEDURE' 

If you want to find the list of all SPs in all Databases you can use the below query :

    CREATE TABLE #ListOfSPs 
    (
        DBName varchar(100), 
        [OBJECT_ID] INT,
        SPName varchar(100)
    )

    EXEC sp_msforeachdb 'USE [?]; INSERT INTO #ListOfSPs Select ''?'', Object_Id, Name FROM sys.procedures'

    SELECT 
        * 
    FROM #ListOfSPs

Kowalczyk

IMO your example using the sp_msforeachdb is gold and should be the answer. Here is a link I found talking more about this sproc: weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx – Roanne

Answer 7

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

select *
  from sys.procedures
 where is_ms_shipped = 0

Thies

this will give the wrong result and include system like diagram stored procedure (sp_upgraddiagrams) in sql 2008 – Teahouse

@Piyush True that it will return the diagram procs, but someone might not consider them to be "system" procs since they didn't come with the standard install. The O.P. didn't specify how to handle that so not filtering them out is not necessarily wrong. – Trapezius

@srutzky:- but still these are not sp's created by user – Teahouse

@Piyush True, but as I said, the O.P. didn't specify how to handle procs that are neither "user created" nor "system". And nobody has asked. – Trapezius

Great answer. Thank you for including "is_ms_shipped = 0." – Dygal

Answer 8

Select All Stored Procedures and Views

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

Auvil

Answer 9

Just the names:

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

Munniks

Answer 10

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

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

Kokoschka

There is no reason to use sys.all_objects since you are filtering on is_ms_shipped=0. It can contain DDL Triggers, but those would be filtered out by type='p'. You might as well use sys.objects. – Trapezius

Answer 11

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

Beller

Why would you use this instead of sys.procedures where is_ms_shipped = 0? And why would you run a function objectproperty(object_id, N'IsMSShipped') for every row when there is a field is_ms_shipped that contains that value? Along those same lines, why run that function again when [type] IN ('P', 'PC') does the same thing? This method is unnecessarily complicated and inefficient. – Trapezius

Answer 12

I've tweaked LostCajun's excellent post above to exclude system stored procedures. I also removed "Extract." from the code because I couldn't figure out what it's for and it gave me errors. The "fetch next" statement inside the loop also needed an "into" clause.

use <<databasename>>
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' 
    and LEFT(p.name,3) NOT IN ('sp_','xp_','ms_')
    order by p.name;
open allSP;
fetch next from allSP into @spName;
while (@@FETCH_STATUS = 0)
begin
    set @aQuery = 'sp_helptext [' + @spName + ']';
    exec sp_executesql @aQuery;
    fetch next from allSP into @spName;
end;
close allSP;
deallocate allSP;

Fruge

Answer 13

the best way to get objects is use sys.sql_modules. you can find every thing that you want from this table and join this table with other table to get more information by object_id

SELECT o. object_id,o.name AS name,o.type_desc,m.definition,schemas.name scheamaName
FROM sys.sql_modules        m 
    INNER JOIN sys.objects  o ON m.object_id=o.OBJECT_ID
    INNER JOIN sys.schemas ON schemas.schema_id = o.schema_id
    WHERE [TYPE]='p'

Garate

Answer 14

I wrote this simple tsql to list the text of all stored procedures. Be sure to substitute your database name in 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;

Jilleen

please see @Fruge 's rewrite of this.. which worked for me. This one didn't work as it had lot of errors. – Holbrooke

Answer 15

This will returned all sp name

Select * 
FROM sys.procedures where [type] = 'P' 
     AND is_ms_shipped = 0 
     AND [name] not like 'sp[_]%diagram%'

Teahouse

The condition on [type] should be [type] IN ('P', 'PC') else you are filtering out any CLR stored procs that are potentially there. – Trapezius

At least this eliminates diagram procedures. better than others. – Roar

Answer 16

This will give just the names of the stored procedures.

select specific_name
from information_schema.routines
where routine_type = 'PROCEDURE';

Horizon

Answer 17

This is gonna show all the stored procedures and the code:

select sch.name As [Schema], obj.name AS [Stored Procedure], code.definition AS [Code] from sys.objects as obj
    join sys.sql_modules as code on code.object_id = obj.object_id
    join sys.schemas as sch on sch.schema_id = obj.schema_id
    where obj.type = 'P'

Hardbitten

Answer 18

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

Neurath

status > 0 doesn't seem to differentiate between system stored procedures and those created – Domingo

Hmm. It does for us -- I don't know why. – Neurath

Anyone using SQL Server 2005 or newer should move away from the dbo.sys* views. This query also filters out CLR stored procedures. – Trapezius

Answer 19

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

Horseplay

There is no reason to use, or benefit from using, sp_tables. Also, "PROCEDURE" is not a valid option for sp_tables. The only options for @table_type are: 'SYSTEM TABLE', 'TABLE', and 'VIEW'. – Trapezius

Answer 20

Try this codeplex link, this utility help to localize all stored procedure from sql database.

https://exportmssqlproc.codeplex.com/

Knockout

Answer 21

select * from DatabaseName.INFORMATION_SCHEMA.ROUTINES where routine_type = 'PROCEDURE'

select * from DatabaseName.INFORMATION_SCHEMA.ROUTINES where routine_type ='procedure' and left(ROUTINE_NAME,3) not in('sp_', 'xp_', 'ms_')


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

Rubicund

Answer 22

USE DBNAME

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


GO 

This will work on mssql.

Punk

Answer 23

Select list of stored procedure in SQL server. Refer here for more: https://coderrooms.blogspot.com/2017/06/select-list-of-stored-procedure-in-sql.html

Selfreliant

Hello, and welcome. This code doesn’t appear to do what the title suggests. It appears to just create a stored procedure which returns a list of records from a PaymentDetails table. The OP wants a list of actual stored procedures. – Plumy

Answer 24

exec sp_stored_procedures; Docs.Microsoft.com

Easy to remember.

Woodsum

Answer 25

List All Store Procedures in SQL Server:

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

List All Store Tables in SQL Server:

select * from sysobjects where type='U' order by name

Abnegate

Hi. What does this answer add to all other answers on this post? – Troyes