Find database that have a specific stored proc

GreggPeele
GreggPeele used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Steve WalesSenior Database Administrator
Commented:
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

Solution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
Commented:
EXEC dbo.sp_MSforeachdb  'USE [?]; IF exists (select 1 from sys.procedures where name like ''EZL_AVST'') select db_name(db_id())'
GreggPeele

Author

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;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial