October 6, 2011 | SQL Server

A handy search procedure

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 problems with syscomments: for any procedure > 4000 characters, the body of the procedure is stored over multiple rows. This means your search phrase may straddle two rows and not turn up. INFORMATION_SCHEMA.ROUTINES uses OBJECT_DEFINITION() behind the scenes (it used to use syscomments), so it's safe there, but the view performs a lot of other calculations that you probably don't need – and it doesn't include triggers (which are already usually forgotten about when troubleshooting). I also found that I had to run several different scripts if I was looking for a word that could be part of a column or parameter name in addition to being embedded into the main body of procedures or functions.

At my previous job, I remember writing a stored procedure that overcame a lot of these limitations. Recently I saw a rash of these questions come, up so I thought I would re-visit it. The following procedure 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. For procedures, functions and triggers it also indicates the line number for the first occurrence of the search string.

My original version produced simple 'EXEC sp_helptext' commands for each object, which you could copy and paste to the top pane and then execute selectively. But I thought it would be simpler to output clickable XML – the result goes to an XML file window that you can't execute and is not color-coded (see Connect #425990 for my suggestion that was shot down). But you can easily right-click the output column and copy and paste the result into a new query window – line breaks and other formatting will be maintained accurately.

The only requirements for this procedure:

  • An instance is running SQL Server 2005+;
  • No database with an apostrophe in its name (this really makes a mess of dynamic SQL);
  • User 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), but this is the one I chose to use here:
    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] = ''$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, '<?query --
               ' + [definition] + ' --?>'),
           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

You can put this stored procedure in your utility database; it does not need to go in master and it does not need to be marked as a system object. Feel free to change the default parameter values (and/or make parameter names shorter) so that your most typical calls to this procedure are easier.

There are alternatives to this, 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.

Please let me know if you have any questions, comments or suggestions.
 

12 comments on this post

    • Mike Milligan - October 6, 2011, 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;'.

    • AaronBertrand - October 6, 2011, 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.

    • Uri Dimant - October 10, 2011, 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

    • Uri Dimant - October 10, 2011, 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 - October 10, 2011, 3:15 PM

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

    • Greg Faulk - October 10, 2011, 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 - October 11, 2011, 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…

    • Uri Dimant - October 11, 2011, 9:33 AM

      Thanks Aaron, works just fine.

    • Uri Dimant - October 11, 2011, 9:33 AM

      Thanks Aaron, works just fine.

    • Vijay - October 12, 2011, 12:41 AM

      Very Nice . Thank you.

    • Enric - August 22, 2012, 11:13 AM

      It is very useful. Brilliant job!!!

    • Phil - March 19, 2013, 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!

Comments are closed.