Solved

Find database that have a specific stored proc

Posted on 2012-12-26
3
208 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?
0
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
3 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 127 total points
ID: 38722479
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
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 128 total points
ID: 38722768
EXEC dbo.sp_MSforeachdb  'USE [?]; IF exists (select 1 from sys.procedures where name like ''EZL_AVST'') select db_name(db_id())'
0
 

Author Closing Comment

by:GreggPeele
ID: 38723580
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Star schema daily updates 2 38
Change this SQL to get all nodes 3 40
sql query 5 44
SQL Server Express or Standard? 5 34
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

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

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question