sql-server - 如何跨多个数据库在存储过程中查找文本

Question

我有 40 多个数据库，并且我想在所有数据库中查找使用文本 sp_reset_data 的过程。 This query helped me a lot :

DECLARE @Search varchar(255)
SET @Search='sp_reset_data'

SELECT DISTINCT
    o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY 2,1

但是，这个获取程序仅适用于当前数据库。有没有办法改进这种查询以查看每个服务器的数据库而无需手动更改当前数据库？

Answer 1

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + 'SELECT db = ''' + name + ''', o.name, o.type_desc
  FROM ' + QUOTENAME(name) + '.sys.sql_modules AS m
  INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
  ON m.[object_id] = o.[object_id]
  WHERE m.definition LIKE N''%'' + @Search + ''%'' 
  ORDER BY o.type_desc, o.name;'
FROM sys.databases
WHERE database_id > 4 AND state = 0; -- online

EXEC sp_executesql @sql, N'@Search NVARCHAR(255)', N'sp_reset_data';

严格来说，如果你只想要过程，那么就简单一点(上面还会包括函数，触发器，甚至 View ):

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + 'SELECT db = ''' + name + ''', o.name
  FROM ' + QUOTENAME(name) + '.sys.sql_modules AS m
  INNER JOIN ' + QUOTENAME(name) + '.sys.procedures AS o
  ON m.[object_id] = o.[object_id]
  WHERE m.definition LIKE N''%'' + @Search + ''%'' 
  ORDER BY o.name;'
FROM sys.databases
WHERE database_id > 4 AND state = 0; -- online

EXEC sp_executesql @sql, N'@Search NVARCHAR(255)', N'sp_reset_data';