Find a specific stored procedure in all databases - SQL Server User Group Exchange

Got a request to find a specific stored procedure in all the databases.

Instead of scrolling thru each database and stored procedures manually to find the stored procedure, here is a SQL script that will search all the databases and list the databases that have that stored procedure. Hope this helps.

--Search for a specific stored procedure in all the databases on the sql instance
DECLARE @SQL NVARCHAR(max)
    ,@spName VARCHAR(200) = 'name of stored procedure' -- The name of the procedure you are 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(200)'
    ,@spName