如何在多个数据库中查找存储过程内的文本

Question

我有40多个数据库，我想在所有数据库中找到使用文本sp_reset_data的存储过程。这个查询对我很有帮助：

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

但是，这只获取当前数据库的过程。有没有一种方法可以改进这种查询方式，以查看每个服务器的所有数据库而不手动更改当前数据库?

- Vinc 웃

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

严格来说，如果您只想要存储过程，那么就更简单一些（以上还包括函数、触发器，甚至视图）：

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

- Aaron Bertrand

---

哇！谢谢您提供这么棒的答案，它非常强大而且非常快！ - Vinc 웃

Answer 2

看一下sp_MSForEachDB。

- Joel Coehoorn

---

不，不，不，不，不……这是未记录、不受支持的，并且已知会跳过数据库。http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/ https://sqlblog.org/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior.aspx https://sqlblog.org/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx - Aaron Bertrand