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 your brain on string search.

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.

5 comments

  1. Be aware that information_schema.routines only returns the first 4000 characters – see this snip from Books on Line

    ROUTINE_DEFINITION nvarchar(4000) Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL.

    To ensure that you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s