Find database that have a specific stored proc

I need a line of SQL that will return only the databases on my SQL Server instance that have a specific stored procedure defined.   If the proc is named "sp_custom_proc", how would I enumerate all the databases where that proc is defined?
GreggPeeleAsked:
Who is Participating?
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
EXEC dbo.sp_MSforeachdb  'USE [?]; IF exists (select 1 from sys.procedures where name like ''EZL_AVST'') select db_name(db_id())'
0
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
This will do it, but you will get a row back for each database it searches with a null result for those it doesn't find.   If you do output to text instead of grid, you can turn column headings off in Tools / Options / Query Results / SQL Server / Output to Text.

Also. sp_MSForEachDB is unsupported as far as I know, but it gets the job done.

EXEC dbo.sp_MSforeachdb 'USE [?]; SELECT SPECIFIC_CATALOG from ?.INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = ''sp_custom_proc''';

Open in new window

0
 
GreggPeeleAuthor Commented:
Another option...

CREATE TABLE #x(db varchar(30), obj SYSNAME);
 
EXEC sp_msforeachdb
       @command1 ='INSERT #x SELECT ''?'',name
               FROM ?.sys.procedures
               WHERE name =''PX_ReleaseBOL37'';';
     
SELECT * FROM #x;
 
DROP TABLE #x;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here