Solved

Find database that have a specific stored proc

Posted on 2012-12-26

Microsoft SQL Server

2 solutions

211 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 127 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 128 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;

Featured Post

Question has a verified solution.

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

Loading XML Using SSIS

Article by: ValentinoV

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 …

Installing SQL Server vNext on Linux: Part 2

Article by: Yashwant

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.

Executing a SQL Server Function from Within Access

Video by: TechMommy

Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

Connecting To SQL Server From Oracle Using Heterogeneous Services Generic Gateway

Video by: Steve

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.