email article printer friendly get the code more resources

Simplify Database Searches
Create a pair of search utilities that simplify the task of gathering information about a particular database or all the databases on a particular server.
by Ian Stirk

November 18, 2005

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 utilities—Search Database and Search Server—that 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.