Get the database name in which Stored Procedure exists

Question

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.

Furthermore

Answer 1

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'''

Corset

SELECT * FROM sys.objects will check only in the drop down selected database only but not all databases. – Furthermore

@Dineshalla:- This query works perfectly fine. Tested and verified! – Corset

Ya i tried it is showing Command(s) completed successfully. What i am suppose to do. Pls update your query – Furthermore

@Dineshalla:- Click on the Results tab of your result window! :) And you will find the database name there – Corset

NO it is showing only messages tab. which version you are using. – Furthermore

I am using the SQL Server 2008 version. What abt you? – Corset

Let us continue this discussion in chat. – Corset

Press [Ctrl] + 'R' and see what you get? – Corset

It's just showing Commands completed successfully. No output. – Tenor

Answer 2

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.

Tenor

Answer 3

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)

Cole

I have to search in all databases. – Furthermore

No, Just wait I'll give the complete query, I was hurry in posting (posted it before initial edit of question) – Cole

Ya i will wait for your answer – Furthermore

Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the UNION operation. – Furthermore

Well, just try "select @FullQuery" instead of "exec (@FullQuery)" (last line) and see if you get something – Cole

@Dineshalla I'm waiting for your feedback – Cole

@Dineshalla, you hurried up a little, I was trying to fix the propabebly issue but you didn't wait, ok anyway – Cole

Update your query i will chose best. if your query works fine then i will go with your answer. – Furthermore

great, I did a small edit can you test it and give a feedback? – Cole

sorry, again , there was a type – Cole

Even if the above query works but it would be still not a performance effective query as using cursor is always a overburden. And why not use the build in functions and procedures which MSDN is providing :) – Corset

@RahulTripathi, you are right, but I'm not using sp_msforeachdb , because you have used it and I don't want do a copy – Cole

@Farhęg:- Yes I am not saying its wrong. But I was just trying to make a point of avoiding cursors ;) – Corset

You tried for me. I am happy with your response. Thank you Very much. – Furthermore

You are very welcome. but the answer of @RahulTripathi is better, you did well to accept it. good luck – Cole

Nice solution... exactly what I was looking for, but there is missing some info, so I changed it a little bit find out the database, I will put a new answer down – Backfire

Answer 4

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.

Masinissa

Answer 5

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

Backfire