Solved

Find database that have a specific stored proc

Posted on 2012-12-26

Microsoft SQL Server

2 solutions

Medium Priority

215 Views

Last Modified: 2012-12-27

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

Question by:GreggPeele

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

3 Comments

LVL 22

Assisted Solution

by:Steve Wales

Steve Wales earned 381 total points

ID: 387224792012-12-26

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

LVL 21

Accepted Solution

by:Alpesh Patel

Alpesh Patel earned 384 total points

ID: 387227682012-12-26

EXEC dbo.sp_MSforeachdb 'USE [?]; IF exists (select 1 from sys.procedures where name like ''EZL_AVST'') select db_name(db_id())'

Author Closing Comment

by:GreggPeele

ID: 387235802012-12-27

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;

COURSE of the MONTH

Find database that have a specific stored proc

Welcome to Experts Exchange

Featured Post

Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Enjoyed your answer?

Keep in touch with Experts Exchange

Live Consultants