Searching Unidentified Tables and Columns for Specific Content : Page 2

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_name
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t 
ON c.Table_schema = t.Table_SCHEMA 
AND c.Table_name = t.Table_name
WHERE 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 type
FROM sys.all_columns c INNER JOIN sys.all_objects t WITH (NOLOCK) ON c.object_id=t.object_id
LEFT JOIN sys.schemas sc WITH (NOLOCK) ON t.schema_id=sc.schema_id
LEFT JOIN sys.types types WITH (NOLOCK) ON c.user_type_id=types.user_type_id
LEFT JOIN sys.schemas st WITH (NOLOCK) ON st.schema_id=types.schema_id
WHERE t.type IN ('U') AND c.Column_name LIKE searchstring
ORDER 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_name
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t 
ON c.Table_schema = t.Table_SCHEMA 
AND c.Table_name = t.Table_name
WHERE 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.

Previous Page