earching for specific content or a specific column is a common task for a SQL Server administrator or developer. If you're familiar with the database or it's documented, searches aren't a problem. However, you're not always that lucky. Sometimes the database you need to search is unfamiliar or undocumented, which makes finding specific content or a specific column a challenge. This article is here to help. It presents a stored procedure that searches columns for content and a SELECT
statement solution that matches a pattern or keyword in column names.
A quick Internet search will turn up some decent but limited solutions for searching unidentified tables and columns for specific content. Some don't consider all character data types, while others promote the unwise approach of using cursors. You should avoid cursors because of their potential for memory leaks if you don't close them properly. In many cases, you can handle a runaway cursor by searching for and killing the SPID (the connection ID), but runaway cursors are unpredictable. You might need to restart the SQL Server service to restore resources, which to your users might as well be the whole server.
A Better Solution: All Character Data Types, No Cursors
The stored procedure in Listing 1
uses a single WHILE
loop instead of a cursor, and it considers all character data types except the XML data type (for SQL Server 2005 and later). First, the code creates a temporary table for storing the schema, table, and column names from the information schema tables, along with the search string values. Next, the loop steps through each column found of the following data types:
When you execute the stored procedure, pass a search string as follows:
EXEC SearchAllTablesAllColumns searchstring
|Figure 1. Result of Passing the Search String: The stored procedure SearchAllTablesAllColumns accepts one parameter, a search string.|
Figure 1 shows the results of searching for the string "brake" in AdventureWorks, the sample database that comes with SQL Server. The procedure found 11 instances of the search string. Using this stored procedure, you find not only the values that match your search string but also where they reside (in which table and in which column).
A bit of parsing using PATINDEX and SUBSTRING divides the resulting string. In this case, SUBSTRING is more flexible than LEFT because it works with all string column types. LEFT will work with char data types but is less useful with text data types. Using the SUBSTRING function allows for a single pass to retrieve the results. Then, a subquery passes the values to the @columname variable, allowing for nulls when the end of the recordset is reached and for a graceful exit from the loop.
The code stores the table and column names in a column named columnname. The parameters are the table name (in this case # for a temporary table), the column names, and their respective data types. The extra character space (256) allows for longer column and table names. The @SearchStr = QUOTENAME component sets text as a valid Unicode representation. The MIN function grabs the smallest column (e.g., 1 before a, a before b, and so on) and runs the searchstring search against it. If the column contains the keyword value, the code stores the value in the temporary table. If not, the code moves on to the next column name, because it's searching for the smallest column name that is larger than @ColumnName.
SearchAllTablesAllColumns searches for strings within a given column, allowing you to locate the correct column if you know specific content keywords but are unsure of which column(s) might store that sort of data.