Search SQL Server database for column name

This post describes how to search SQL Server for a column name.

Occasionally when I’m working in an unfamiliar SQL Server database I want to search for a column name or where a column is referenced.  Rather than trawling through multiple tables we can query the database metadata to find all uses of this column name.  There are many ways of doing this and this blog explains two methods.

This query searches the Information Schema view called INFORMATION_SCHEMA.COLUMNS for an exact column name.  This will inform us which views and tables contain the column name we are searching for:

SELECT col.TABLE_CATALOG, col.TABLE_SCHEMA, tab.TABLE_TYPE, col.TABLE_NAME, col.COLUMN_NAME, col.DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS col
INNER JOIN INFORMATION_SCHEMA.TABLES tab ON tab.TABLE_NAME = col.TABLE_NAME AND tab.TABLE_SCHEMA = col.TABLE_SCHEMA
WHERE COLUMN_NAME = ‘Column Name’
ORDER BY 2,3,4,5

We can also search for a column name within all stored procedures in a database. To do this we must query the system view called SYS.PROCEDURES.  This view contains CREATE procedure scripts (which will include the developer comments) for all stored procedures in the database. As we are effectively searching for a string within a string we have to use LIKE in the WHERE clause:

SELECT OBJECT_NAME(OBJECT_ID) AS SP_NAME, OBJECT_DEFINITION(OBJECT_ID) AS SP_TEXT
FROM SYS.PROCEDURES
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%Column Name%’
ORDER BY 1

When executing both of these statement in SQL Server Management Studio they will be executed against the database already selected.  To run against a different database then the database name could be specified like this:

FROM DATBASENAME.INFORMATION_SCHEMA.COLUMNS OR DATBASENAME.SYS.PROCEDURES

Leave a Reply

Your email address will not be published. Required fields are marked *