You're reading...
Challenges, SQL, Tips/Tricks

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 ...


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

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

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

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

-- 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)


sp_Search_procedure_all_DB 'insert', 'startswith'

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:


Collation Precedence

Hope it helps!

Your feedback and suggestions are welcome.


Shahan Ayyub

About Shahan Ayyub

Shahan is a Microsoft Certified Professional of .Net. Mostly self taught programmer and a quick learner, who just love to code and always eager to find unique solutions to the common problems. Currently working as an iOS developer.


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


LinkedIn Profile

Volunteer Expert at EE

Code Project Profile

My Code project

Stackoverflow Profile

profile for NeverHopeless on Stack Exchange, a network of free, community-driven Q&A sites

BrainBench Certified


Elance Testimonials
%d bloggers like this: