The Problem

From time to time we need to search through the database code to research the scope of impact of certain changes. For example, if you are wanting to remove a Table, Column, Function, or Stored Procedure that is not used anymore (as far as you know), you first want to make sure that no code is pointing to it. In order to accomplish this, most people use a query similar to the following example that does a basic scan of the "text" field in syscomments (the following works in both 2000 and 2005):

 SELECT    DISTINCT OBJECT_NAME(sc.id) 
 FROM      dbo.syscomments sc 
 WHERE     sc.text LIKE '%some text%' 
 ORDER BY  OBJECT_NAME(sc.id) 

While the above query does find objects that have the specified text, it does have a few problems, namely:

1. it does not find all matches
2. it does not show how many matches or exactly where they are
3. it does not search job steps
4. it does not match on column names within tables (views have definitions so those could be matched by the above query)
5. it does not distinguish the context of the match (a match within a comment is truly not an object dependency)
6. it does not search DTS (or SSIS) packages

Most of the above reasons make sense enough as they are stated but I am sure that the first reason is less obvious. The problem has to do with how the object code is stored in the syscomments table. The "text" field is defined as NVARCHAR(4000) so each row can only hold up to 4000 characters. However, it is not uncommon to have object code that is much larger than 4000 characters. So, SQL Server breaks up the code into 4000 character segments and orders them by the "colid" field. The problem, then, is that the query stated above only looks at each row as an individual entity and cannot match strings that span two rows within the syscomments table; meaning, if you are searching for a string that is 10 characters long and it starts at character 3997 of row 1 of the object and ends at character 6 of row 2 of the object, the above query will never find it and you might miss a valid match.

The Solution

In order to avoid this problem I came up with a method that reconstructs the object code into a line-by-line structure broken on newlines (well, really CRLF or CHAR(13) + CHAR(10) or \r\n or 0x0D0A). This method is partially due to it originally being designed for SQL Server 2000 which does not have NVARCHAR(MAX) and does not allow a local variable of the NTEXT datatype. However, the method does provide for the added benefit of solving the problem of the above query being too general and not telling you how many times the string matched or exactly where. That takes care of the first two problems noted above. The third problem, that of not searching the SQL Jobs, was taken care of by searching msdb.dbo.sysjobs and msdb.dbo.sysjobsteps. The fourth problem was solved by searching syscolumns. Solving the fifth problem took a bit more logic to distinguish between comments, literals (code within single-quotes), and regular ol' code. Unfortunately I do not yet have a solution to the sixth problem, that of not searching within DTS or SSIS packages (maybe someday).

Before we get into the code I want to give a general overview of how to use the Stored Procedure that I came up with as there are several options. The basic syntax is:

EXEC SearchObjectsForText @LinkedServer NVARCHAR(128), @DBName NVARCHAR(128), @ObjectName NVARCHAR(128), @Search NVARCHAR(2000), @Query NVARCHAR(4000), @IncludeJobs BIT, @IgnoreComments BIT, @IgnoreStrings BIT

• @LinkedServer is optional and can be left as empty ''. If specified, it needs to be an exact LinkedServer name defined on your server. It cannot contain wildcards (% or _) and most likely should be enclosed in square-brackets ([ and ]) to avoid problems.
• @DBName is required. It needs to be an exact database name on your server or on the LinkedServer. It cannot contain wildcards (% or _).
• @ObjectName is optional and can be left as empty ''. If specified, the search will only look at objects with names that match the pattern. It can be an exact object name or it can include wildcards (% and _). Default = '%'.
• @Search is optional and can be left as empty ''. If specified, the search will only return results that match the pattern. It can be an exact string or it can include wildcards (% and _). Default = '%'.
• @Query is optional and can be left as empty ''. If specified, you have full access to the temp tables holding the search data so you can do custom queries. For example, you can create your own temp tables to hold the results of several searches before doing an analysis. The three tables are:
• 1. #Matches ([DBId] INT NOT NULL, [ObjectId] INT NOT NULL, [LineNum] SMALLINT, [ObjectType] CHAR(2), [Code] NVARCHAR(4000))
  2. #MatchesFiltered ([DBId] INT NOT NULL, [ObjectId] INT NOT NULL, [LineNum] SMALLINT, [ObjectType] CHAR(2), [CodeFiltered] NVARCHAR(4000)) -- only available if ignoring comments or strings
  3. #ObjectNames ([DBId] INT NOT NULL, [ObjectId] INT NOT NULL, [DBName] SYSNAME NOT NULL, [ObjectName] NVARCHAR(260) NOT NULL)
• @IncludeJobs is optional. If set to 1 (true), it will search job steps as well as standard DB objects. Default = 1.
• @IgnoreComments is optional. If set to 1 (true), it will remove all comments (both single-line and block comments) and put the filtered code into the #MatchesFiltered temp table. Default = 1.
• @IgnoreStrings is optional. If set to 1 (true), it will remove all text within single-quotes (including the single-quotes) except for anything that is part of a comment. Keep in mind when using this option that Dynamic SQL will also be removed. Default = 0.

The Code

And now for the fun stuff.

NOTES:

• Be sure to replace the {db_name} in the first line after USE
• While this was designed for SQL Server 2000, it also works in SQL Server 2005.
• Records marked as Object Type of U (meaning User-Table) are either column names or computed-column definitions

Download the code from the Resources section below.

Examples

/* Display all code in a DB on the local server including Job Steps and ignoring comments: */

EXEC dbo.SearchObjectsForText '', 'SQL#'

/* Display all code in a DB on another server NOT including Job Steps: */

EXEC dbo.SearchObjectsForText '[SomeServer\SomeInstance]', 'SQL#', '', '', '', 0

/* Display all code containing a "string" on the local server: */

EXEC dbo.SearchObjectsForText '', 'SQL#', '', '%string%'

/* Do three searches for two different strings across two different DBs saving the results to temp tables so aggregate the results */

CREATE TABLE #AllMatchesFiltered ([DBId] INT NOT NULL, [ObjectId] INT NOT NULL, [LineNum] SMALLINT, [ObjectType] CHAR(2), [CodeFiltered] NVARCHAR(4000))

CREATE TABLE #AllObjectNames ([DBId] INT NOT NULL, [ObjectId] INT NOT NULL, [DBName] SYSNAME NOT NULL, [ObjectName] SYSNAME NOT NULL)

-- yes, you should typically (99.99% of the time) specify column names in SELECT and INSERT statements but this isn't production code

EXEC dbo.SearchObjectsForText '', 'SQL#', '', '%string1%', 'INSERT INTO #AllMatchesFiltered SELECT * FROM #MatchesFiltered ; INSERT INTO #AllObjectNames SELECT * FROM #ObjectNames'

EXEC dbo.SearchObjectsForText '', 'SQL#', '', '%string2%', 'INSERT INTO #AllMatchesFiltered SELECT * FROM #MatchesFiltered ; INSERT INTO #AllObjectNames SELECT * FROM #ObjectNames'

EXEC dbo.SearchObjectsForText '', 'AnotherDB', '', '%string2%', 'INSERT INTO #AllMatchesFiltered SELECT * FROM #MatchesFiltered ; INSERT INTO #AllObjectNames SELECT * FROM #ObjectNames', 0 -- don't need jobs this time

SELECT *
FROM #AllMatchesFiltered amf
INNER JOIN #AllObjectNames aon
ON aon.DBId = amf.DBId
AND aon.ObjectId = amf.ObjectId

Conclusion

Hopefully this code helps you perform better quality searches and reduce missed matches. In a follow-up to this I will update this code to work more specifically with SQL Server 2005. I will likely update it to allow for searching all DBs instead of just one at a time (I have already been moving the code in this direction). And at some point I will add this feature to the SQL# library of CLR functions as a User-Defined Function so that you can select directly from the results rather than having to use the @Query parameter.

Copyright © April, 2008 by Solomon Rutzky