How to find a text inside stored procedures across multiple databases

Question

I have 40 + databases, and I want to find procedures in all databases that use the text 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

But, this get the procedures only for the current database. Is there a way to improve this kind of query to look in every server's DB without manually changing the current DB?

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

Strictly speaking, if you only want procedures, then it is a little simpler (the above will also include functions, triggers, even views):

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