Find Strings in Database Code, SQL Server Agent Jobs, and SSIS Packages

After reading Michael Berry's article "Find Any String in Any SQL Code in Any Database," I was inspired to try his Find stored procedure, which lets you find a string of data in any T-SQL code stored in any database on the given SQL Server instance. To call this stored procedure, you type

Find 'mystring'

where mystring is the string you want to search for. The stored procedure then searches for that string in all the databases on the server and returns a list of the objects that contain it.

After sharing the Find stored procedure with my coworkers, we decided to tweak it. In our jobs, we often need to search for strings in not only database code but also SQL Server Agent jobs and SQL Server Integration Services (SSIS) packages, so we adapted the stored procedure, which we renamed sp_Find, to perform these additional searches. The sp_Find stored procedure searches SQL Server Agent jobs by looking through the text stored in all job steps and searches SSIS packages saved to msdb. (It won't search through SSIS packages if they're saved to a file system or through DTS packages.) You can perform one type of search (e.g., only SSIS packages), all three types of searches (i.e., database code, SQL Server Agent jobs, and SSIS packages), or any combination thereof (e.g., database code and SQL Server Agent jobs).

We also adapted the stored procedure so that the output includes 100 characters of the code surrounding the search string (i.e., the 50 characters preceding the string and the 50 characters following the string). You can change the number of characters if desired. Finally, we adapted the stored procedure so that you can search a specific database rather than all the user databases on the server.

To run sp_Find, you follow the syntax

sp_Find 'SearchText', DBName, PreviewTextSize, SearchDBsFlag, SearchJobsFlag, SearchSSISFlag

where

So, for example, the code

sp_Find 'track'

tells the stored procedure to search through all the databases, SQL Server Agent jobs, and SSIS packages for the string track. The statement

sp_Find 'track', 'Common', 50

tells the stored procedure to search through the database named Common, the SQL Server Agent jobs, and the SSIS packages for the string track and include only 50 characters surrounding the string in the output. Figure 1 shows sample results from this statement.

Figure 1: Sample results from the sp_Find stored procedure (click to enlarge)

The code

sp_Find 'Track', 'Common', Null, 'Y', 'N', 'Y'

tells the stored procedure to search through the database named Common and the SSIS packages for the string track and include 100 characters surrounding the string in the output. As this example shows, you need to use Null if there's a parameter you don't want to set sandwiched between two parameters that you are setting.

Because our shop is completely on SQL Server 2005, we changed the stored procedure's queries to reflect the latest system catalog. Thus, sp_Find won't work on earlier SQL Server versions. If you prefer to have a search tool that works on earlier versions, it wouldn't be difficult to remove the SSIS package logic and revert the database tables back to the previous catalog version, using the code provided in "Find Any String in Any SQL Code in Any Database" as an example.

You can download the sp_Find.sql file by clicking the "Download the Code" link at the top of the page. You don't need to customize the code at all, provided you're going to run it on SQL Server 2008 or SQL Server 2005.

Note that we named this stored procedure sp_Find so that we could store it in the Master system database and call it from any database context. Use this name at your own risk—Microsoft could come out with a system procedure with the same name in a future release.

Learn more from "T-SQL 101: Stored Procedures" and "Relevant T-SQL Stored Procedures."