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.
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!
It is very useful. Brilliant job!!!
Very Nice . Thank you.
Thanks Aaron, works just fine.
Thanks Aaron, works just fine.
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…
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).
Caught me Uri, slightly misplaced clause when testing. Please grab the code again and try it now…
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
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
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.
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;'.