获取存储过程存在的数据库名称

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.

我有SQL Server 2008，我有10个不同的数据库，现在我想搜索一个存储过程，存储过程存在于哪个数据库中。

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.

一些人提到重复.....并正确地阅读我的问题。我的要求是我需要验证'SP_Email'程序。我存在哪个数据库。

3 个解决方案

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

#2

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.

您需要查询master数据库的sys.databases以获取数据库列表，并且对于每个数据库名称，您需要查询db_name.sys.procedures以检查它是否存在。

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)

#3

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.

如果没有这样的过程，它将返回NULL。如果所有数据库都在同一实例上，这将起作用。

#1