Simplify Database Searches (Continued)

Build Your Dynamic Search
You build dynamic SQL in two sections that you "UNION" together to produce a single resultset, with any duplicates removed. The first section of the dynamic SQL looks for the search string inside any table definitions. It does this by looking for the search string inside the column named "name" within the syscolumns table. The second section of the dynamic SQL looks for the search string inside any view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure definitions. It looks for the search string inside the column named "text" within the syscomments table.

You execute the dynamic SQL using the sp_ExecuteSQL command. The results are sorted by object type, such as table or view, and then object name. It makes sense to store this database-wide utility in the master database of the server you use it with. Running the dba_SearchDB utility is easy. Assume that you want to search for the string "OrderDate" in the Northwind database. Fire up SQL Analyzer and use this command:

EXEC YourServerName.master.dbo.dba_SearchDB 
   '%OrderDate%', 'Northwind'

This command returns two stored procedures, one table, and four views (see Figure 1).

The server-wide utility builds on the functionality of the first utility. Search Server uses the Search Database utility to search for a given string within the database objects in all the databases on a given server (see Listing 2).

The first part of the stored procedure validates the string you want to search for. This string should contain at least one character wrapped by percentage symbols. The second part of the stored procedure uses the undocumented Microsoft stored procedure "sp_MSForEachDB" to iterate over all of the databases on the current server. The code in Listing 2 executes the Search Database utility for each database, based on the parameters dictated at the outset.

As with the Search Database utility, you run the Search Server inside SQL Analyzer as well. Using the Search Server utility is easy. Assume you want to search for OrderDate against all your databases on a given server, rather than against a single database. Simply type this command into SQL Analyzer:

EXEC YourServerName.master.dbo.dba_SearchDBServer 
   '%OrderDate%'

In this case, the results returned occur against multiple databases on the server. These utilities are stored procedures, so it's easiest to run them using SQL Query Analyzer; however, you could use any suitable container (such as a .NET program).

Performing the searches themselves is easy, but you have to keep in mind some caveats. First, the utilities don't inspect all database objects, such as some "minor" object indices and jobs. Further examination of the system tables should help correct this. While not comprehensive, these utilities should prove extremely useful for practical, everyday use. Another caveat: The utilities use partial matching, so it's possible to get some unwanted results. For example, searching for "person" might also return database objects that contain the word "personal" in the results. Regardless, the utilities described should help give you a better understanding of the scope and interaction of the database elements, including fields, stored procedures, and so on.

About the Author
Ian Stirk has been working in IT as a developer, designer, and architect since 1987. He holds M.Sc., B.Sc., MCSD.NET, MCAD, MCDBA, MCSD, MCP, and SCJ2P certifications. He is a freelance .NET consultant working in London. Reach Ian at .