如何找到例如Sp_SystemAlert
存在于任何数据库中的存储过程,并列出具有该过程的数据库的名称?
你可以尝试这样的事情,
CREATE TABLE #Temptable
(
dbname varchar(250)
)
EXEC sp_msforeachdb 'USE [?];
INSERT INTO #Temptable
Select ''?''
FROM sys.procedures
Where name = ''<SP_NAME>'' '
SELECT *
FROM #Temptable
下面的代码将告诉包含存储过程的数据库。
CREATE TABLE ##DatabasesContainingSP(dbname sysname, SPName SYSNAME);
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
INSERT INTO ##DatabasesContainingSP
SELECT DISTINCT
db_name() as dbname, o.name AS Object_Name
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
WHERE o.name =''Sp_SystemAlert'';
'
SELECT * FROM ##DatabasesContainingSP
使用Information_schema.routines
SELECT * FROM DatabaseName.information_schema.routines
WHERE routine_type = 'PROCEDURE' and Routine_Name LIKE 'Sp_SystemAlert%'