Geek Answers Handbook

How to find text inside stored procedures in multiple databases

I have 40+ databases and I want to find procedures in all databases that use 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 will lead to procedures only for the current database. Is there any way to improve this type of query for searching in each server database without manually changing the current database?

+4
source share
2 answers
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 need procedures, then this is a little easier (functions, triggers, even views will also be included above):

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';
+9
source

Take a look at sp_MSForEachDB .

-1

More articles:


All Articles