Find stored procedure in all databases

Are you looking for a way to find procedure in all databases?

In this post you will find a way to search procedure in all databases.

There are some other ways to do so but here it will be demonstrated by using dynamic SQL. The attached procedure will prepare a query with respect to all databases and combine them into a single result using UNION operator.

It will look something like:

select DatabaseName, ProcName COLLATE DATABASE_DEFAULT 
from <databasename>.sys.procedures where name <searchcriteria>   UNION ...

Script:

The below script is a bit tricky and complex in a sense as it combines support of “exact name”,”a part of procedure name”, “start of procedure name” and “end of procedure name” in a single statement.

Here is the full script:

if exists (select * from sys.procedures where name='sp_Search_procedure_all_DB')
drop procedure sp_Search_procedure_all_DB
GO

CREATE PROCEDURE sp_Search_procedure_all_DB @procName varchar(300), @searchType varchar(30)
AS
BEGIN
--Declare variables
declare @sql varchar(max)

set @procName   = REPLACE(@procName,'''','''''')
set @searchType = REPLACE(@searchType,'''','''''')

IF @searchType NOT IN('startswith','contains','endswith','exact')
Begin 
   PRINT 'Lookup failed. Expected search types are ''startswith'',''contains'',''endswith'',''exact'''
   Return
End

-- Define case to run as per the search type
SET @sql = (SELECT 'select ''' + replace([name],'''','''''') + ''' as [DatabaseName], [name] ' +
                   'COLLATE DATABASE_DEFAULT as [Procedure] from ' + QUOTENAME([name]) + '.sys.procedures where ' +
                   '[name] ' + CASE WHEN @searchType='exact' THEN '=' ELSE ' like ' END  + '''' + 
                   CASE WHEN @searchtype IN ('endswith','contains') THEN '%' ELSE '' END + 
                   @procName + CASE WHEN @searchtype IN ('startswith','contains') THEN '%' ELSE '' END + '''' + 
                   ' UNION' + Char(10) from sys.databases for xml path(''))

-- Trim unnecessary pattern
SET @sql = (SELECT replace(@sql + ' ','UNION' + char(10) + ' ',''))

-- Run query
EXEC (@sql)

END
GO

sp_Search_procedure_all_DB 'insert', 'startswith'
GO

Limitation:
This will work for all the databases that belongs to the same server.

A Related Issue:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Turkish_CS_AI_KS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.

Assume that “Turkish_CS_AI_KS” is the collation of one of the database and “SQL_Latin1_General_CP1_CI_AS” are rest of database collations.

In query above query you will find this ” COLLATE DATABASE_DEFAULT ” part. It is important in a case when you have databases with different collations. This highlighted part overrides the collation to default database collation and made the above query to compatible over different database collation.

If we remove this part we will get the error mentioned above.

Recommended Reads:

COLLATE

Collation Precedence

Hope it helps!

Your feedback and suggestions are welcome.

Regards,

Shahan Ayyub