Script to find the list of stored procedures in all databases

Question

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?

Answer 1

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.

Answer 2

You have to iterate through all databases to do that. If you're adventurous enough, you could use the undocumented stored procedure sp_MSForEachDB, which is buggy and unreliable.

Another possibility is using one of its replacements: Aaron Bertrand's or my humble attempt.

Yet another possibility is using a cursor to loop through all databases:

USE master;

DECLARE @name sysname;
DECLARE @sql nvarchar(max) = '
    SELECT 
        DB_NAME() AS [database_name],
        OBJECT_SCHEMA_NAME(object_id) AS [schema_name],
        name AS [procedure_name]
    FROM sys.procedures
';
DECLARE @theSQL nvarchar(max);

DECLARE @results TABLE (
    [database_name] sysname,
    [schema_name] sysname,
    [procedure_name] sysname
);

DECLARE dbs CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY 
FOR
SELECT name 
FROM sys.databases;
-- you may want to exclude system databases here
-- WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution')

OPEN dbs;
FETCH NEXT FROM dbs INTO @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @theSQL = 'EXEC ' + QUOTENAME(@name) + '.sys.sp_executesql @sql';

    INSERT @results
    EXEC sys.sp_executesql @theSQL, N'@sql nvarchar(max)', @sql

    FETCH NEXT FROM dbs INTO @name; 
END

CLOSE dbs;
DEALLOCATE dbs;

SELECT *
FROM @results;

Answer 3

this script looks for all databases to find every place where a specific stored procedure lives, but if you leave the parameter out, you would get a complete list of all stored procedures.

--=====================================================================================
-- looking at all databases to find a stored procedure named @spName
-- marcelo miorelli
-- 1-april-2014
--=====================================================================================
DECLARE @SQL NVARCHAR(max)
    ,@spName VARCHAR(100) = 'usp_sel_CAEval4_comments' -- THE PROCEDURE THAT I AM LOOKING FOR

SELECT @SQL = STUFF((
            SELECT CHAR(10) + ' UNION ALL '           + CHAR(10) +  
' SELECT ' + quotename(NAME, '''') + ' AS DB_NAME '   + CHAR(10) + 
'         , SCHEMA_NAME(s.schema_id)  AS THE_SCHEMA ' + CHAR(10) + 
'         , s.name  COLLATE Latin1_General_CI_AS AS THE_NAME  ' + CHAR(10) + 
'  FROM ' + quotename(NAME) + '.sys.procedures s '    + CHAR(10) +   
' WHERE s.name = @spName 
  AND s.[type] = ''P'''
            FROM sys.databases
            ORDER BY NAME
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'nvarchar(max)'), 1, 11, '')

--PRINT @SQL

EXECUTE sp_executeSQL @SQL
    ,N'@spName varchar(100)'
    ,@spName

Answer 4

EXEC
    sys.sp_msforeachdb 
    'SELECT ''?'' DatabaseName, Name FROM [?].sys.procedures WHERE Name LIKE ''%companymaster%'''

Replace "companymaster" with whatever name you are looking for.