Query in Sql server to list all stored procedures in all databases

There can be possibility that you might need to know list all stored procedures in all databases of your SQL server system, so in this article, I have provided simple query to list all stored procedures of all database.

Script to list all stored procedure in SQl server

Here is the query which you can execute to get all stored procedures of all database in SQL server:

CREATE TABLE #SPs (db_name varchar(100), name varchar(100), object_id int)

EXEC sp_msforeachdb 'USE [?]; INSERT INTO #SPs select ''?'', name, object_id from sys.procedures'

SELECT * FROM #SPs

In the above query, we are creating a new table "SPs", where we will store "database name as db_name, "Stored procedure name as name" and it's "object_id".

Once we create the above table, we are executing "SELECT from sys.procedures" for each database, loading the data into a temp table (#SPs).

sys.procedures lists out all of the stored procedures in the database and sp_msforeachdb will run the code on each database (use ? for the database name in the code).

When I executed the above query in my local SQL Server, I got the following output:

If you don't want to use "sp_msforeachdb ", you use the below simple query to get stored procedures list

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

Query to get stored procedure list for single database

You can also get list of all stored procedures from specific database, using query as below

SELECT * FROM  AdventureWorks2012.sys.procedures;

Where "AdventureWorks2012" = database name.

Sample output, when executed above query

OR

If you want to list all procedures with schema name

SELECT [schema] = OBJECT_SCHEMA_NAME([object_id]),
  name
FROM sys.procedures;

OR

You can also use below query if you are using older version of database like SQL Server 2005

select *
  from sys.procedures
 where is_ms_shipped = 0

OR using information_schema

SELECT * 
  FROM YourDatabaseName.INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE'

That's it, hope it helps.

You may also like to read:

Stored procedure in sql server (With Example)

Understanding SQL server switch case (With Example)

How to back up SQL Server database ? (Various Ways explained)