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:

• char
• varchar
• text
• nchar
• nvarchar
• ntext

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.

A SELECT Solution for Finding Column Names

Sometimes you will be familiar with the database’s structure, but won’t recall a table or column’s full name. When this happens, run the following SELECT statement in SQL Server 2000:

SELECT c.Table_Schema, c.Table_name, c.Column_nameFROM INFORMATION_SCHEMA.COLUMNS cINNER JOIN INFORMATION_SCHEMA.TABLES t ON c.Table_schema = t.Table_SCHEMA AND c.Table_name = t.Table_nameWHERE TABLE_TYPE = 'BASE TABLE' AND c.Column_name LIKE searchstring

The above script will work in SQL Server 2000, 2005, and 2008. The following script is based on the new schema tables for 2005 and 2008, and it guarantees forward compatibility:

SELECT t.name AS TableName,sc.name AS SchemaName,c.name AS ColumnName,types.name AS TypeName,st.name AS TypeSchemaName,t.type AS typeFROM sys.all_columns c INNER JOIN sys.all_objects t WITH (NOLOCK) ON c.object_id=t.object_idLEFT JOIN sys.schemas sc WITH (NOLOCK) ON t.schema_id=sc.schema_idLEFT JOIN sys.types types WITH (NOLOCK) ON c.user_type_id=types.user_type_idLEFT JOIN sys.schemas st WITH (NOLOCK) ON st.schema_id=types.schema_idWHERE t.type IN ('U') AND c.Column_name LIKE searchstringORDER BY c.object_id, c.column_id

Figure 2. Searching SELECT Against Northwind: Run this SELECT statement to find specific columns.

To search a SQL Server database for a table or column whose full name you can’t remember, you can run a SELECT statement like this:

SELECT c.Table_Schema, c.Table_name, c.Column_nameFROM INFORMATION_SCHEMA.COLUMNS cINNER JOIN INFORMATION_SCHEMA.TABLES t ON c.Table_schema = t.Table_SCHEMA AND c.Table_name = t.Table_nameWHERE TABLE_TYPE = 'BASE TABLE' AND c.Column_name LIKE '%order%'

Figure 2 shows the results of running the above statement against Northwind.

As you can see, the SELECT returned several instances of column names that contained the string “order.” BASE TABLE refers to user-created tables and not system tables; that’s how tables are defined within the information_schema table.

Intuitive Searches When You’re Missing Information

Searching known entities is simple. When you don’t know the names of tables and columns, a search requires more work. You can use a stored procedure to search unidentified tables for specific values. Or, you can use a SELECT statement to locate columns where the name matches a pattern or keyword. Both solutions are useful tools to add to your arsenal.