Simplify Database Searches
Create and learn to use a pair of search utilities that simplify the task of gathering information about a particular database or all the databases on a particular server.
Technology Toolbox: SQL Server
You have much to learn anytime you join a new team. For example, you discover existing databases, tables, stored procedures, and so on that underlie any business functionality. Similarly, you must be able to estimate, with some degree of confidence, the impact of any changes to the database system, such as changing the length of a field from 12 characters to 15 characters.
The tools that ship with Visual Studio and SQL Server out of the box don't really help you accomplish these tasks, but you can easily create a pair of utilities that ferret out this information for you. To that end, I'll show you how to create a pair of utilitiesSearch Database and Search Serverthat give you a better understanding of the scope and interaction of the database elements, including fields, stored procedures, and much more (download the two utilities here). The utilities themselves aren't complex, but they do have a couple wrinkles that you'll have to keep in mind as you use them. I'll also provide some background on the process of searching databases in SQL Server, because it affects how you must implement and use the utilities.
The Search Database utility enables you to discover where a given database element, such as a field, table, view, or stored procedure, is used within a database. It allows you to search for a comment or date (or any string) inside the database's main objects.
You can use Information Schema views to access information about the composition of a database in system tables, but it's generally recommended that you avoid accessing the system tables directly because their structure might change in future releases of SQL Server. That said, several of these views don't store the underlying data object definition if it's greater than nvarchar(4000) in size (in SQL Server 2000). To counter this restriction, one of the utilities inspects the system tables directly rather than using the Information Schema views. This shouldn't pose a problem here because the system table fields inspected are the same in both SQL Server 2000 and SQL Server 2005.
Inspecting the underlying system tables enables you to learn where a given string is used within a database (see Listing 1). The stored procedure accepts two parameters: a string to find (@StringToFind) and the name of the database to search (@DBName). You set the transaction isolation level to "read uncommitted" to prevent any unnecessary locking, such as holding locks and honoring locks.
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 stored procedure searches the whole definition of the various database objects, so use a LIKE statement within the SQL.
You build the SQL statement dynamically because it isn't possible to change the current database in a stored procedure using the USE statement. You need to change the database in order to search the relevant database's system tables.
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:
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 Listing2.aspx" target="_blank">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:
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.