CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008t-sqlstored-procedures
Dinesh Reddy Alla
Dinesh Reddy Alla

Reputation: 1717

Get the database name in which Stored Procedure exists

I am having SQL server 2008 and i am having 10 different databases in it and now i want to search one stored procedure that in which database the stored procedure is present.

Mentioned as duplicate by some ..... with out reading my question properly. My Requirement is i need to verify 'SP_Email' procedure. I which database is this procedure exists.

Upvotes: 13

Views: 33996

Answers (5)

Martin
Martin

Reputation: 363

The solution from @void was what I was looking for, but I think is missing the point.
So I made some changes to add the database name that was the purpose of the question and I put a variable for the SP name.
All you need to do is to replace the your_stored_procedure_name name with yours.

use master;
go
declare @procName varchar(50) = 'your_stored_procedure_name';
declare @dbName varchar(50);

-- temporary table to collect the info
create table #procinfo (
    dbname varchar(256),
    procname varchar(256)
)

declare rsDb cursor for 
    select name 
    from sys.databases 
    where 
        database_id > 4 
        and state = 0;

open rsDb;
fetch next from rsDb into @dbName;

while( @@fetch_status = 0 and @@error = 0 )
begin
    
    exec ('insert into #procinfo select ''' + @dbName + ''' as dbname, name from ' + @dbName + '.sys.procedures where name like ''%' + @procName + '%''');
    
    fetch next from rsDb into @dbName;
end
close rsDb
deallocate rsDb

select * from #procinfo
drop table #procinfo

Upvotes: 0

Pedram
Pedram

Reputation: 6508

Please try this.

SELECT name DatabaseName
FROM sys.databases
WHERE OBJECT_ID(QUOTENAME(name) + '.dbo.ProcedureNameHere', 'P') IS NOT NULL;

This will return the database(s) name in which this particular object exist.

Replace ProcedureNameHere with your procedure name. In your case it would be SP_Email Keep rest of the things as it is.

Upvotes: 7

void
void

Reputation: 7890

you need to query sys.databases of master database to get list of databases and for each database name you get you need to query the db_name.sys.procedures to check if it exists.

try below query and give a feedback:

use master
go
declare @FullQuery varchar(max)
declare @DBName varchar(50)
set @FullQuery=''
declare cr cursor for select name from sys.databases where database_id > 4
open cr
fetch next from cr into @DBName
while(@@fetch_status=0)
begin
set @FullQuery=@FullQuery+
    ' select name  COLLATE SQL_Latin1_General_CP1_CI_AS from '+@DBName+'.sys.procedures where name like ''%proc_name%'' union'
fetch next from cr into @DBName
end
close cr
deallocate cr
set @FullQuery=substring(@FullQuery,1,len(@FullQuery)-5)
exec (@FullQuery)

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

You can try this:

EXEC sp_msforeachdb 
'if exists(select 1 from [?].sys.objects where name=''SP_Email'')
select ''?'' as FoundInDatabase from [?].sys.objects where name=''SP_Email'''

Upvotes: 21

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

SELECT OBJECT_ID('DataBase1.SchemaName.StoredProcedureName') / 
OBJECT_ID('DataBase2.SchemaName.StoredProcedureName') / 
OBJECT_ID('DataBase3.SchemaName.StoredProcedureName') /
...

It will return NULL if there is no such procedure. This will work if all databases are on same instance.

Upvotes: 0

Related Questions