T-SQL: Search SQL Code

• Download source - 13.9 KB

Introduction

T-SQL code to search SQL objects, including SQL Agent Jobs, and display properly formatted results in the "Messages" pane of SQL Server Management Studio (SSMS). Handy for those times where you want to see if that temp table you created is used by any other subroutines called within a given stored procedure, or just plain finding patterns in your code.

Key features include:

• Simple in-line syntax, e.g. PR_FIND '%Hello World%'.
• Functionally formatted results that make skimming easy and practical.
• Results are displayed in Messages pane, so as secondary Find (CTRL+F) to search within results is easy.
• Results display enhanced details, including:
  • Match count.
  • Object details, like type and create date.
  • Line number match is found on (based on offset from CREATE statement).
  • Windowed result showing 40 characters to the left and right of the match, so you get immediate context.
• Reasonably fast, with several hundred objects, results usually return in a second or less.
• Different display modes depending on need.
• Searches SQL Agent Job Steps.
• Can explicitly include/exclude objects based on a name pattern.

Background

There are 4 key parts to make this solution work, all will need to be created on the database to be searched, in this order:

1. VW_OBJ_TXT. Basically just exposes object code from syscomments, concatenating together the bodies of bigger objects into one giant text entry to be searched.
2. PR_PRINT. Used to display results to the Messages tab where the results may be more than the truncation character limit.
3. FN_SPLIT. Used to split optional the comma separated list of include/exclude object patterns.
4. PR_FIND. Executes the search and formats the results.

Using the Code

Basic Usage

EXEC [dbo].[PR_FIND]
    @TextToFind nvarchar(max) -- Pattern to search on
    @DisplayLevel tinyint = 0, -- 0 = Object Name Only, 1 = Windowed Results, 2 = Full text
    @Lead int = 40, -- Text amount to show the left and right of the result in Windowed mode
    @SearchJobsToo bit = 1, -- When true, search SQL Agent Job Steps
    @IncludeObjects nvarchar(max) = null, -- Comma separated list of object name patterns in which to search
    @ExcludeObjects nvarchar(max) = null -- Comma separated list of object name patterns to exempt from search

Example 1

Search for the variable name "@TextToFind" in all objects in the database, displaying results in windowed mode, showing the code surrounding the match, with an additional 40 characters of text to the left and right of the results.

EXEC [dbo].[PR_FIND] '@TextToFind'

Example 1 Results

The header is always displayed, no matter what display mode, to give a quick snapshot of what was found. In the text return results, objects are separated by the *** horizontal rules, and matches within each object are separated by the --- horizontal rules. The header block starts with additional object details, like name, type and create date. The object footer for each objects shows the match count and search time for that particular object. (Note that I removed matches 2-10 in this example for brevity). The match results display the actual matched code and surrounding code, wrapped in … at both ends, as well as the match number and line number as offset from the object CREATE statement.

id      modify_date      type_desc      full_name
1461580245 2013-10-11 15:46:18 SQL_STORED_PROCEDURE [dbo].[PR_FIND]

/******************************************************************************/
ID: 1461580245
NAME: [dbo].[PR_FIND]
TYPE: SQL_STORED_PROCEDURE
CREATED: Oct 11 2013  3:46PM, MODIFIED: Oct 11 2013  3:46PM
SEARCH: "@TextToFind"
--------------------------------------------------------------------------------

Match 1 on line 16 within [dbo].[PR_FIND]
--------------------------------------------------------------------------------
...--------------------------------*
(
    @TextToFind nvarchar(max),  -- Pattern to search on
...

Match 2 on line 36 within [dbo].[PR_FIND]
--------------------------------------------------------------------------------
...(@Text)
            , @lenTextToFind int -- LEN(@TextToFind)
            , @StartTime DateTime -- Runtime Sta...

--------------------------------------------------------------------------------
"@TextToFind" Found 10 Times in [dbo].[PR_FIND] in 3ms
/******************************************************************************/

Example 2

In this example, we want to search on the text "agent_name", which may be a column name or a variable or a piece of dynamic SQL or whatever. By setting @DisplayLevel = 0, we will not return the found matches, we will only display objects that have a match (quicker and easy to read). We are not searching SQL Agent Jobs, and we are only searching jobs that have CUST or EMP in the object name, but excluding any object that begins with FN.

EXEC [dbo].[PR_FIND]
    @TextToFind = 'agent_name'
    @DisplayLevel = 0 -- Only display summary
    @SearchJobsToo = 0 
    @IncludeObjects = '%CUST%,%EMP%'
    @ExcludeObjects = 'FN%'

Code Deep Dive

For details about FN_SPLIT, review the posting on the subject T-SQL: Most Practical Split Function.

VW_OBJ_TXT is basically a select * from [syscomments]. The magic happens on the concatenate statement:

select 
    o.[object_id] as [id],
    …
    (
        (
            -- Concatenate together all the sections of code that make up a given object
            SELECT CAST(c2.text as nvarchar(max))
            FROM syscomments c2
            WHERE c2.id = o.[object_id]
            ORDER BY colid
            FOR XML PATH('') , TYPE
        ).value('.', 'nvarchar(max)')
    ) AS [txt]
from sys.objects o

PR_PRINT just breaks the passed in text into 4K blocks of text and prints each block using the PRINT statement. To try and improve the readability of the results, the code looks for carriage returns and tries to break on them so the print out is more natural. If none are found for a given block, it then looks for the space character to break on as a second best option. If none is found, the break is arbitrary at the edge of the 4K block.

-- Try to break on a new line
SET @char_idx = CHARINDEX(CHAR(10), @txt)
 
-- If we can't find a new line, try to break on a space where the space is near
-- the end of the current page of text
IF NOT (@char_idx between 1 AND @pg_sz)
BEGIN
    -- Get the size of the page of text
    SELECT @char_idx = CASE WHEN (@txt_len < @pg_sz) THEN @txt_len ELSE @pg_sz END
 
    -- Look for the last space character in the window
    SET @temp_char_idx = CHARINDEX(' ', REVERSE(SUBSTRING(@txt, 1, @char_idx)))
 
    -- If found, set the position of the found character on the non-reversed string
    IF @temp_char_idx > 0
        SET @char_idx = (@char_idx - @temp_char_idx) + 1 -- +1 here since we -1 later on
		ELSE -- Indicate character is still not found
        SET @char_idx = 0
END

PR_FIND is mostly about formatting, but some of the more interesting blocks of code include the following. This temp table holds the objects matching our search criteria.

DECLARE @oids TABLE 

The include and exclude object name patterns are split on comma, and a case insensitive (UPPER) comparison does the matching.

SELECT o.id
FROM sysobjects o
INNER JOIN [dbo].[FN_SPLIT](@IncludeObjects,',') f
    ON (UPPER(o.name) LIKE LTRIM(RTRIM(UPPER([item]))))
    AND RTRIM([item]) <> ''

The primary search is basically just done with a like clause against [VW_OBJ_TXT]. Note that the search is also case insensitive since we LOWER() both the search text and the like clause. The big advantage here is that we are pre-filtering results before we enter into the cursor later on, which is the slower part. Basically, we should not open the cursor to an object that does not have a matched pattern.

-- Find matches 
INSERT INTO @oids (id, name, [schema_name], [type_desc], txt, [create_date], [modify_date])
SELECT DISTINCT c.id, c.name, [schema_name], [type_desc], c.txt, [create_date], [modify_date]
FROM [dbo].[VW_OBJ_TXT] c
 
-- That case insensitive match our search text
WHERE LOWER(c.[txt]) like @TextToFind
 
-- And are either in our include list, or no list was passed
and 
(
    @IncludeObjects is null
    OR
    c.id IN (select [object_id] from @scope_objects where [include] = 1)
)
 
-- And are not in our exclude list, or no list was passed
and
(
    @ExcludeObjects is null
    OR
    c.id NOT IN (select [object_id] from @scope_objects where [include] = 0)
)

Adding in job steps requires a hit to msdb. If this raises security concerns for your team, you could wrap this section in a try/catch block or do some proactive sniffing to see if the current user has rights to execute this part of the query. It should be noted that the primary intent of this procedure is to be used in development environments, so security was not a design concern, so adjust as needed.

-- If they have indicated to search job text, do so here.
IF @SearchJobsToo = 1
BEGIN
    INSERT INTO @oids (id, name, [schema_name], [type_desc], txt, [create_date], [modify_date])
    SELECT DISTINCT
        0, -- Since job_id's are not int, just set to 0 here
        ISNULL('JOB: ' + j.name, 'Unknown Job') + ISNULL(', STEP: ' + js.step_name, 'Unknown Step'),
        'job',
        'SQL Agent Job Step',
        js.command, -- Job Step Text
        j.date_created,
        j.date_modified
    FROM msdb.dbo.sysjobs j
    JOIN msdb.dbo.sysjobsteps js
        ON js.job_id = j.job_id 
    WHERE LOWER(js.command) like @TextToFind
END

Once we have the results, we open a cursor to loop though them to display in a pretty way. For each object in the matched set, we display the header details:

-- Object match template, add details here to display information about the match per object
PRINT 'ID: ' + CAST(@id as varchar(64))
PRINT 'NAME: ' + @name
PRINT 'TYPE: ' + @type_desc
PRINT 'CREATED: ' + CAST(@create_date as nvarchar(max)) + ', _<br />MODIFIED: ' + CAST(@modify_date as nvarchar(max))
PRINT 'SEARCH: "' + @OriginalTextToFind + '"'

Then we look for independent matches within the object body of text:

SELECT @index = PATINDEX(@TextToFind, LOWER(@Text))
WHILE @index > 0
BEGIN
    ….  Format Results …
END

In the match results section, some interesting pieces of code include the logic to find the line number by doing a length delta when we remove new line characters:

-- Get the count of new line characters, then adding on matches from previous blocks of text
SELECT @current_line_number = (LEN(SUBSTRING(@Text, 1, @index)) - 
   LEN(REPLACE(SUBSTRING(@Text, 1, @index), CHAR(10), ''))) + @total_lines

Near the end of the loop, we shrink the searchable body of text with SUBSTRING and re-search for the next match using PATINDEX again. We also keep a running total of lines from the top of the object so our line numbers stay intact.

SELECT @Text = SUBSTRING
(
    @Text, 
    @match_index + @Lead, 
    @lenText - (@match_index + @Lead)
)
SELECT @index = PATINDEX(@TextToFind, LOWER(@Text))
SELECT @total_lines = @total_lines + @current_line_number

At the end of each object parsing in the cursor, we display the current object details and move to the next object in the cursor.

EXEC PR_PRINT @DisplayText

Points of Interest

For complicated searches where you really need to tailor your filter criteria, it may be easier to just query against VW_OBJ_TXT.

You can adjust PR_PRINT to display as an XML comment block, which is a common work-around to the display truncation issue. However, I find the formatting on that harder to read, so my preference was to use PRINT to display to messages. Feel free to adjust as you see fit.

In the include/exclude matching, where we compare on object name only, you could easily add an OR statement to compare on [type_desc] or [xtype] or other object descriptors if you choose to do so. I simply just never had a need for it since we follow naming practices that help me solve that problem, i.e., I can search where name like FN% for functions. In either case, it might be a nice bell and whistle to add in.

The code is intended for developers and not intended to be run in a production system, although there would be limited harm in doing so, outside of intellectual property concerns you might have.

While there are 3^rd party plug-ins for SSMS that do this well, having it available in line in T-SQL is just plain handy.

History

• 12 October 2013: Initial public draft
• 20 January 2014: Fixed bug in line numbers where some numbers were incorrect
• 24 January 2014: Added a few small features like match on name as well as body text, and summary counts