Find Occurrences of a String Anywhere on an Instance

Have you ever had a conversation like this?

Developer: “Hey, we’d like to know every place a particular column is used”.

You: “On which database?”

Developer: “Ummm….all of them, please.”

And then just like that, the sun goes behind a cloud, and darkness gathers over your cubicle. Only your cubicle, mind you.

This is what I use when these moments happen. This script will go out and search for occurrences of the search parameter in every table, view, synonym, stored procedure and function on the instance and will return the results in a single set.

CREATE TABLE #Results
(
    DatabaseName sysname,
    ObjectName sysname,
    ObjectType sysname
);
DECLARE @searchParam NVARCHAR(50);
SET @searchParam = N'WhatAreYouLookingFor'; -- Do not include []. 
DECLARE @dbName sysname;
DECLARE @dbCursor CURSOR;
DECLARE @sql NVARCHAR(MAX);
SET @dbCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT name
FROM sys.databases
WHERE source_database_id IS NULL
      --AND database_id > 4
      AND is_read_only = 0
      AND state_desc = 'ONLINE'
ORDER BY name;
OPEN @dbCursor;
FETCH NEXT FROM @dbCursor
INTO @dbName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sql
        = N'USE [' + @dbName + N'];
INSERT INTO #Results
SELECT ''' + @dbName
          + N''' AS DatabaseName, 
SPECIFIC_CATALOG + ''.'' + SPECIFIC_SCHEMA + ''.'' + SPECIFIC_NAME AS ObjectName,
              ROUTINE_TYPE AS ObjectType
FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE N''%' + @searchParam + N'%''
UNION ALL 
SELECT '''     + @dbName
          + N''' As DatabaseName,
TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.'' + TABLE_NAME AS ObjectName,
''TABLE'' AS ObjectType
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ''%' + @searchParam + N'%''
UNION ALL
SELECT '''     + @dbName + N''' AS DatabaseName, 
   ''' + @dbName
          + N''' + ''.'' + SCHEMA_NAME(schema_id) + ''.'' + name AS ObjectName, 
  ''SYNONYM'' AS ObjectType
FROM sys.synonyms 
WHERE base_object_name LIKE N''%' + @searchParam + N'%''
UNION ALL 
SELECT '''     + @dbName
          + N''' AS DatabaseName,  
  name COLLATE DATABASE_DEFAULT AS ObjectName, 
  ''Linked Server'' AS ObjectType
  FROM sys.servers WHERE name = ''' + @searchParam + N'''
UNION ALL 
SELECT '''     + @dbName
          + N''' AS DatabaseName,  
  TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.'' + TABLE_NAME AS ObjectName, 
  ''VIEW'' AS ObjectType
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE N''%' + @searchParam + N'%''
ORDER BY ObjectType, ObjectName;';
    FETCH NEXT FROM @dbCursor
    INTO @dbName;
    PRINT @sql;
    EXECUTE sp_executesql @sql;
END;
CLOSE @dbCursor;
DEALLOCATE @dbCursor;
SELECT *
FROM #Results
ORDER BY DatabaseName, ObjectType, ObjectName;
DROP TABLE #Results;

Presto! Problem solved. I wish I could tell you this runs quickly – it doesn’t. But it does get the job done.