A handy search procedure

Updated October 2021 : an updated version that is shorter, handles apostrophes in database names just fine, and supports supplementary Unicode characters ("SC" collations) like the poop emoji – both in the searched string and in entity names.

I often see questions about searching stored procedures, functions, etc. for specific keywords. Maybe it's an object reference they're looking for, or all of the places where they use built-in functions like GETDATE() (I once had to replace all of these with GETUTCDATE() to cater to servers where we couldn't control the time zone settings).

Many people still suggest using syscomments or INFORMATION_SCHEMA.ROUTINES for this.

The problem with syscomments: for any procedure > 4000 characters, the body of the procedure is stored across multiple rows. This means your search phrase may straddle two rows and not turn up (example), or the procedure could turn up multiple times if the search string was found in multiple 4K chunks (example). It is also a pain to re-assemble the output.
The problem with INFORMATION_SCHEMA.ROUTINES (among many): things like triggers aren't included – and those are already usually forgotten about when troubleshooting.

So I've written a stored procedure that overcomes a lot of these limitations. It supports case sensitive searches, a comma-separated database list, inclusion/exclusion of system databases (and system objects in user databases), and also optionally searches jobs, column names, and parameter names.

The body of a matching module or job step is presented as clickable XML (I talk about this technique here). While the result goes to an XML file window that isn't color-coded and that you can't execute (though I tried to change this), you can right-click the output and copy and paste the result into a new query window. Line breaks and other formatting will be maintained accurately, though I can't guarantee you don't have some characters in your code that will mess that up.

Requirements:

An instance running SQL Server 2012+ (for Latin1_General_100_CS_AS_SC collation)
At the moment, database names in @database_list must be XML-friendly (e.g. no < and &)
fn_split (or simply Andy Mallon's entire dba-database)
A login with sufficient permissions to read definitions in each database (or at least the ability to sign it; Erland Sommarskog has a great write-up on that here).

Known issues:

If you enable both job parameters, all steps will be returned for a job that only matches on the job name.

You can put this stored procedure in any utility database (and it is also now part of Andy Mallon's DBA Database, which is where you should get this to make sure you have the most recent version); it does not need to go in master and it does not need to be marked as a system object. fn_split – or a synonym to it – needs to exist in the same database.

CREATE PROCEDURE dbo.FindStringInModules
   @search_string               nvarchar(4000),
   @database_list               nvarchar(max) = NULL,
   @case_sensitive              bit = 0,
   @search_jobs                 bit = 0,
   @search_job_and_step_names   bit = 0,
   @search_schema_names         bit = 0,
   @search_object_names         bit = 0,
   @search_column_names         bit = 0,
   @search_parameter_names      bit = 0,
   @search_system_objects       bit = 0,
   @search_system_databases     bit = 0,
   @search_everything           bit = 0,    
   @debug                       bit = 0
AS
BEGIN
  SET NOCOUNT ON;
 
  IF @search_everything = 1
  BEGIN
    SELECT
      @search_jobs               = 1,
      @search_job_and_step_names = 1,
      @search_object_names       = 1,
      @search_schema_names       = 1,
      @search_column_names       = 1,
      @search_parameter_names    = 1,
      @search_system_objects     = 1,
      @search_system_databases   = 1;
  END
 
  DECLARE @sql        nvarchar(max),
          @template   nvarchar(max),
          @exec       nvarchar(1024),
          @all_text   nvarchar(128),
          @coll_text  nvarchar(128);
 
  SELECT @sql       = N'',
         @template  = N'',
         @all_text  = CASE @search_system_objects 
                      WHEN 1 THEN N'all_' ELSE N'' END,
         @coll_text = CASE @case_sensitive
                      WHEN 1 THEN N'Latin1_General_100_CS_AS_SC'
                      WHEN 0 THEN N'Latin1_General_100_CI_AS_SC' 
                      END;
 
  CREATE TABLE #o
  (
    [database]   nvarchar(130), 
    [schema]     nvarchar(130), 
    [object]     nvarchar(130), 
    [type]       nvarchar(130), 
    create_date  datetime, 
    modify_date  datetime, 
    column_name  nvarchar(130), 
    param_name   nvarchar(130), 
    definition   xml
  );
 
  SET @search_string = N'%' + @search_string + N'%';
 
  SET @template = N'
SELECT [database]     = DB_NAME(),
       [schema]       = s.name,
       [object]       = o.name, 
       [type]         = o.type_desc, 
       o.create_date, 
       o.modify_date,
       [column_name]  = $col$,
       [param_name]   = $param$,
       definition     = CONVERT(xml, ''<?query -- '' + QUOTENAME(DB_NAME())
                        + CHAR(13) + CHAR(10) + OBJECT_DEFINITION(o.object_id) 
                        + CHAR(13) + CHAR(10) + ''--'' + CHAR(63) + ''>'')
  FROM sys.$all$objects AS o
  INNER JOIN sys.schemas AS s 
    ON o.[schema_id] = s.[schema_id]';
 
 
  SET @sql = @sql + REPLACE(REPLACE(@template, N'$col$', N'NULL'), N'$param$', N'NULL')
      + N'
    ' + N' WHERE OBJECT_DEFINITION(o.[object_id]) COLLATE $coll$ 
    ' + N'                                LIKE @s COLLATE $coll$';
 
  SET @sql = @sql + CASE @search_schema_names WHEN 1 THEN N'
      OR s.name COLLATE $coll$ 
        LIKE @s COLLATE $coll$' ELSE N'' END;
 
  SET @sql = @sql + CASE @search_object_names WHEN 1 THEN N'
      OR o.name COLLATE $coll$ 
        LIKE @s COLLATE $coll$' ELSE N'' END;
 
  SET @sql = @sql + CASE @search_column_names WHEN 1 THEN N';
'     + REPLACE(REPLACE(@template, N'$col$', N'c.name'),N'$param$',N'NULL')
      +  N'
         INNER JOIN sys.$all$columns AS c ON o.[object_id] = c.[object_id]
         AND c.name COLLATE $coll$ 
            LIKE @s COLLATE $coll$;' ELSE N'' END;
 
  SET @sql = @sql + CASE @search_parameter_names WHEN 1 THEN N';
'     + REPLACE(REPLACE(@template, N'$col$', N'NULL'),N'$param$',N'p.name')
      +  N'
         INNER JOIN sys.$all$parameters AS p ON o.[object_id] = p.[object_id]
         AND p.name COLLATE $coll$ 
            LIKE @s COLLATE $coll$;' ELSE N'' END;
 
  SET @sql = REPLACE(REPLACE(@sql, N'$coll$', @coll_text), N'$all$', @all_text);
 
  DECLARE @db sysname, @c cursor;
 
  SET @c = cursor FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT QUOTENAME(name) FROM sys.databases AS d
      LEFT OUTER JOIN dbo.fn_split(@database_list, N',') AS s ON 1 = 1
      WHERE
      (
        LOWER(d.name) = LOWER(LTRIM(RTRIM(s.value)))
        OR NULLIF(RTRIM(@database_list), N'') IS NULL
      )
      AND d.database_id >= CASE @search_system_databases
          WHEN 1 THEN 1 ELSE 5 END
      AND d.database_id < 32767
      AND d.state = 0;
 
  OPEN @c;
 
  FETCH NEXT FROM @c INTO @db;
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @exec = @db + N'.sys.sp_executesql';
 
    IF @debug = 1
    BEGIN
      RAISERROR(N'Running dynamic SQL on %s:', 1, 0, @db);
      PRINT @sql;
    END
    ELSE
    BEGIN
      INSERT #o
      (
        [database], 
        [schema], 
        [object], 
        [type], 
        create_date, 
        modify_date, 
        column_name, 
        param_name, 
        definition
      )
      EXEC @exec @sql, N'@s nvarchar(4000)', @s = @search_string;
    END
 
    FETCH NEXT FROM @c INTO @db;
  END
 
  IF @debug = 0
  BEGIN
        SELECT [database], 
             [schema], 
             [object], 
             [type], 
             create_date, 
             modify_date, 
             column_name, 
             param_name, 
             definition
      FROM #o
      ORDER BY [database], [schema], [object], [column_name], [param_name];
  END
 
  /* jobs */
 
  IF @search_jobs = 1
  BEGIN
    SET @template = N'SELECT
                job_name = j.name,
                s.step_id,
                s.step_name,
                j.date_created,
                j.date_modified,
                [command_with_use] = CONVERT(xml, N''<?query -- '' 
                                     + QUOTENAME(s.database_name)
                                     + CHAR(13) + CHAR(10) + s.[command]
                                     + CHAR(13) + CHAR(10) + ''--'' + CHAR(63) + ''>'')
            FROM msdb.dbo.sysjobs AS j
            INNER JOIN msdb.dbo.sysjobsteps AS s
            ON j.job_id = s.job_id
            WHERE s.command COLLATE $coll$
                    LIKE @s COLLATE $coll$'
    + CASE @search_job_and_step_names WHEN 1 THEN
      N' OR j.name      COLLATE $coll$ 
                LIKE @s COLLATE $coll$
         OR s.step_name COLLATE $coll$ 
                LIKE @s COLLATE $coll$'
      ELSE N'' END
    + N' ORDER BY j.name, s.step_id;';
 
    SET @sql = REPLACE(@template, N'$coll$', @coll_text);
 
    IF @debug = 1
    BEGIN
      PRINT N'Running this for jobs:';
      PRINT @sql;
    END
    ELSE
    BEGIN
      EXEC sys.sp_executesql @sql, N'@s nvarchar(4000)', @s = @search_string;
    END
  END
END
GO

There are alternatives to this process, of course. There are free tools that integrate into Management Studio, but I have yet to see a free tool that also searches jobs – not that that part is hard, just that it's not out there to my knowledge. Like triggers, jobs are often forgotten when trying to track down an issue.

The Old Version

I have left the previous version intact in case you are nostalgic or stuck on SQL Server 2005.

The requirements for this procedure are slightly different:

An instance running SQL Server 2005+;
No database with an apostrophe in its name (this really makes a mess of dynamic SQL);
A login with sufficient permissions to read definitions in each database;

A helper function called dbo.SplitStrings_XML – you can use any generic string splitting function of course (including CLR approaches or STRING_SPLIT on 2016+), but this is the one I chose to use here (there are many to choose from):

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       nvarchar(max),
   @Delimiter  nvarchar(3)
)
RETURNS table
AS
  RETURN 
  (
    SELECT Item = CONVERT(nvarchar(128), Item)
    FROM
    (
      SELECT Item = x.i.value('(./text())[1]', 'NVARCHAR(128)')
        FROM
        (
          SELECT [XML] = CONVERT(XML, '<i>' 
                       + REPLACE(@List, @Delimiter, '</i><i>') 
                       + '</i>').query('.')
        ) AS a
        CROSS APPLY [XML].nodes('i') AS x(i)
     ) AS y
     WHERE Item IS NOT NULL
  );

And here is the actual code for the procedure:

CREATE PROCEDURE dbo.FindString
   @search_string            nvarchar(4000),     
   @database_list            nvarchar(max) = NULL,
   @case_sensitive           bit = 0,
   @include_jobs             bit = 0,
   @include_columns          bit = 0,
   @include_parameters       bit = 0,
   @include_system_objects   bit = 0,
   @include_system_databases bit = 0
AS
BEGIN
   SET NOCOUNT ON;
 
   DECLARE @init_sql  nvarchar(max),
           @run_sql   nvarchar(max),
           @dbname    nvarchar(128),
           @all_text  nvarchar(10),
           @coll_text nvarchar(50);
 
   CREATE TABLE #t
   (
       [database]      sysname,
       [schema]        sysname,
       [object]        sysname,
       [type]          sysname,
       [create_date]   datetime,
       [modify_date]   datetime,
       [definition]    nvarchar(max)
   );
 
   CREATE TABLE #j
   (
       [job_name]      sysname,
       [step_id]       int,
       [step_name]     sysname,
       [create_date]   datetime,
       [modify_date]   datetime,
       [definition]    nvarchar(max)
   );
 
   CREATE TABLE #cp
   (
       [database]      sysname,
       [schema]        sysname,
       [object]        sysname,
       [type]          sysname,
       [create_date]   datetime,
       [modify_date]   datetime,
       [param]         nvarchar(128),
       [column]        nvarchar(128)
   );
 
   SELECT
       @all_text = CASE @include_system_objects 
           WHEN 1 THEN N'all_' ELSE N'' END,
       @coll_text = CASE @case_sensitive
	   WHEN 1 THEN N'COLLATE Latin1_General_BIN' ELSE N'' END;
 
   SET @init_sql = N'SELECT 
           [database] = N''$db$'',
           [schema]   = QUOTENAME(s.name),
           [object]   = QUOTENAME(o.name),
           [type]     = o.type_desc,
           o.create_date,
           o.modify_date,
           m.[definition]
       FROM 
           $db$.sys.$all$sql_modules AS m
       INNER JOIN 
           $db$.sys.$all$objects AS o
           ON m.[object_id] = o.[object_id]
       INNER JOIN 
           $db$.sys.schemas AS s
           ON o.[schema_id] = s.[schema_id]
       WHERE 
           m.definition $coll$ 
            LIKE N''%'' + @search_string + ''%'' $coll$;';
 
   SET @init_sql = REPLACE(REPLACE(@init_sql, 
       '$all$', @all_text), '$coll$', @coll_text);
 
   SET @search_string = REPLACE(@search_string, '''', '''''');
 
   DECLARE c CURSOR
       LOCAL STATIC FORWARD_ONLY READ_ONLY
       FOR 
           SELECT QUOTENAME(d.name)
               FROM 
                   sys.databases AS d
               LEFT OUTER JOIN 
                   dbo.SplitStrings_XML(@database_list, N',') AS s
                   ON 1 = 1
               WHERE 
                   (
                     LOWER(d.name) = LOWER(s.Item)
                     OR NULLIF(RTRIM(@database_list), N'') IS NULL
                   )
                   AND d.database_id BETWEEN CASE @include_system_databases 
                   WHEN 1 THEN 1 ELSE 5 END AND 32766
               ORDER BY d.name;
   OPEN c;
 
   FETCH NEXT FROM c INTO @dbname;
 
   WHILE @@FETCH_STATUS = 0
   BEGIN
       SET @run_sql = REPLACE(@init_sql, N'$db$', @dbname);
 
       INSERT #t 
       EXEC sp_executesql 
           @run_sql, 
           N'@search_string NVARCHAR(4000)', 
           @search_string;
 
       IF @include_columns = 1
       BEGIN
           SET @run_sql = N'SELECT 
				[database] = ''$db$'',
				[schema]   = QUOTENAME(s.name),
				[object]   = QUOTENAME(o.name),
				[type]     = o.type_desc,
				o.create_date,
				o.modify_date,
				NULL,
				c.name
			FROM 
				$db$.sys.$all$columns AS c
			INNER JOIN 
				$db$.sys.$all$objects AS o
				ON c.[object_id] = o.[object_id]
			INNER JOIN
				$db$.sys.schemas AS s
				ON o.[schema_id] = s.[schema_id]
			WHERE
				c.name $coll$ 
				  LIKE N''%'' + @search_string + ''%'' $coll$;';
 
           SET @run_sql = REPLACE(REPLACE(REPLACE(@run_sql, 
               '$all$', @all_text), '$coll$', @coll_text), '$db$', @dbname);
 
           INSERT #cp
           EXEC sp_executesql
               @run_sql, 
               N'@search_string NVARCHAR(4000)',
               @search_string;
       END
 
       IF @include_parameters = 1
       BEGIN
           SET @run_sql = N'SELECT 
				[database] = ''$db$'',
				[schema]   = QUOTENAME(s.name),
				[object]   = QUOTENAME(o.name),
				[type]     = o.type_desc,
				o.create_date,
				o.modify_date,
				p.name,
				NULL
			FROM 
				$db$.sys.$all$parameters AS p
			INNER JOIN 
				$db$.sys.$all$objects AS o
				ON p.[object_id] = o.[object_id]
			INNER JOIN
				$db$.sys.schemas AS s
				ON o.[schema_id] = s.[schema_id]
			WHERE
				p.name $coll$ 
				  LIKE N''%'' + @search_string + ''%'' $coll$;';
 
           SET @run_sql = REPLACE(REPLACE(REPLACE(@run_sql, 
               '$all$', @all_text), '$coll$', @coll_text), '$db$', @dbname);
 
           INSERT #cp
           EXEC sp_executesql
               @run_sql, 
               N'@search_string NVARCHAR(4000)',
               @search_string;
       END
 
       FETCH NEXT FROM c INTO @dbname;
   END
 
   CLOSE c;
   DEALLOCATE c;
 
   SELECT 'Objects:';
 
   SELECT 
       [database],
       [schema],
       [object],
       [type],
       [definition] = CONVERT(XML, '<?query --
           USE ' + [database] + ';' 
           + CHAR(13) + CHAR(10) + 'GO' 
           + CHAR(13) + CHAR(10) + [definition] + ' --?>'),
       first_line = (DATALENGTH(abbrev_def)
           -DATALENGTH(REPLACE(abbrev_def, CHAR(13), '')))/2 + 1,
       create_date,
       modify_date
   FROM
   (
       SELECT 
           *, 
           [count] = (DATALENGTH([definition]) 
               - DATALENGTH(REPLACE([definition], @search_string, '')))
               /DATALENGTH(@search_string),
           abbrev_def = SUBSTRING([definition], 1, 
               CHARINDEX(@search_string, [definition]))
       FROM #t
   ) AS x
   ORDER BY [database], [schema], [object];
 
   IF @include_jobs = 1
   BEGIN
       SELECT 'Jobs:';
 
       SET @run_sql = N'SELECT 
				job_name = j.name, 
				s.step_id, 
				s.step_name, 
				j.date_created,
				j.date_modified,
				[definition] = s.command
			FROM msdb.dbo.sysjobs AS j
			INNER JOIN msdb.dbo.sysjobsteps AS s
			ON j.job_id = s.job_id
			WHERE s.command $coll$ 
			  LIKE ''%'' + @search_string + ''%'' $coll$
			ORDER BY j.name, s.step_id;';
 
       SET @run_sql = REPLACE(@run_sql, '$coll$', @coll_text); 
 
       INSERT #j EXEC sp_executesql
           @run_sql,
           N'@search_string NVARCHAR(4000)',
           @search_string;
 
       SELECT 
           job_name,
           step_id,
           step_name,
           [command] = CONVERT(XML, '&lt;?query --
               ' + [definition] + ' --?&gt;'),
           create_date,
           modify_date
       FROM #j;
   END
 
   IF @include_columns = 1 OR @include_parameters = 1
   BEGIN
       SELECT 'Columns/parameters';
 
       SELECT 
           [database],
           [schema],
           [object],
           [type],
           [param],
           [column],
           create_date,
           modify_date
       FROM #cp
       ORDER BY [database], [schema], [object], [param], [column];
   END
 
   DROP TABLE #t, #j, #cp;
END
GO

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

Phil says:

March 19, 2013 at 5:05 AM

Oh, this is very, very nice! You don't know how many times I've wanted to just select a jobstep with the line-breaks… I am installing this proc in my utils db for keeps. 🙂
Thanks so much for contributing this!
Enric says:

August 22, 2012 at 11:13 AM

It is very useful. Brilliant job!!!
Vijay says:

October 12, 2011 at 12:41 AM

Very Nice . Thank you.
Uri Dimant says:

October 11, 2011 at 9:33 AM

Thanks Aaron, works just fine.
AaronBertrand says:

October 11, 2011 at 1:35 AM

Greg, I think I've made the proper correction now. That has spurred me to create a new policy for myself: never try to correct a code sample, from a phone, while on a bus…
Greg Faulk says:

October 10, 2011 at 11:50 PM

I'm getting an error on the join between sys.databases and dbo.SplitStrings_XML. SSMS syntax highlighter says the WHERE clause is invalid, I suspect because there's no ON clause.
I changed the join from
LEFT OUTER JOIN dbo.SplitStrings_XML(@database_list, N',') AS s
to
dbo.SplitStrings_XML(@database_list, N',') AS s on (LOWER(d.name) = LOWER(s.Item))
and it now compiles and in a very quick test seems to produce the correct results (with regard to the database list).
AaronBertrand says:

October 10, 2011 at 3:15 PM

Caught me Uri, slightly misplaced clause when testing. Please grab the code again and try it now…
Uri Dimant says:

October 10, 2011 at 9:10 AM

Hi Aaron
First of all I would like to thank you for that handy stored procedure as till now I have been using Vyas's utility but your looks very promising
I have a question, running the below command
EXEC dbo.FindString @search_string='Projects',@database_list='mydb'
It returned all objects where a word 'projects' appears BUT in all databases (I have more than 500), I looked for that word in specific one… am I missing something?
Thanks
AaronBertrand says:

October 6, 2011 at 6:18 PM

Sorry, that was copy-and-paste stupidity while making quick changes to remove <![CDATA and to improve the syntax highlighting / coloring. That instance of @coll_t should actually have been @init_sql. Sorry about that. The code above has been corrected.
Mike Milligan says:

October 6, 2011 at 6:09 PM

I get an error b/c @coll_t is not declared.
SET @coll_t = N'SELECT
I changed it to @coll_text and I get errors when I execute the procedure.
SELECT
[database] = '[AdventureWorks]'
,[schema] = QUOTENAME(s.NAME)
,[object] = QUOTENAME(o.NAME)
,[type] = o.type_desc
,o.create_date
,o.modify_date
,NULL
,c.NAME
FROM [AdventureWorks].sys.COLUMNS AS c
INNER JOIN [AdventureWorks].sys.OBJECTS AS o ON c.[object_id] = o.[object_id]
INNER JOIN [AdventureWorks].sys.schemas AS s ON o.[schema_id] = s.[schema_id]
WHERE c.NAME SELECT
[database] = '[AdventureWorks]'
,[schem LIKE N'%' + @search_string + '%' SELECT
[database] = '[AdventureWorks]'
,[schem;
Msg 4145, Level 15, State 1, Line 13
An expression of non-boolean type specified in a context where a condition is expected, near 'SELECT'.
Msg 105, Level 15, State 1, Line 17
Unclosed quotation mark after the character string 'schem;'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'schem;'.