Sql-server – Script to find the list of stored procedures in all databases

sql serverstored-procedurest-sql

I need to pull out the list of stored procedures which are available in my instance. I used the following T-SQL statement to get the stored procedures in a given database.

select * 
from MyDatabase.information_schema.routines 
where routine_type = 'Procedure'

Is there is any script to obtain the all stored procedures or to check the database name of the stored procedure by using the stored procedure name?

Best Answer

You can use the following:

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

The code above runs a USE and then a SELECT from sys.procedures for each database, loading the data into a temp table. sys.procedures lists out all of the stored procedures in the database and sp_msforeachdb will run the code on each database (use a ? for the databasename in the code). Once the code is run you can query the temp table to get the consolidated list.

sp_msforeachdb is known to have issues so you may want to use Aaron Bertrand's improved version located here.

Related Question