Find Column in All Databases

Occasionally, there comes a requirement to search all databases on a particular server for either columns with a specific name, or columns relating to a specific subject. In the most recent case, I had to find all similar columns in all databases because the company plans to change the datatype of these columns. My task began with having to locate each of these columns in order to determine the impact of the change, and what actually needed to change.

For this, I used the undocumented sp_MSForEachDB procedure. It is a great procedure that actually takes a SQL string as an input parameter. The SQL used for the input parameter must then contain a question mark (?) in which each database replaces the question mark and the SQL is executed.

Here is an example:
[cc lang=”sql”]
sp_MSForEachDB @command1=’USE ?;

SELECT
Table_Catalog
,Table_Schema
,Table_Name
,Column_Name
,Data_Type
,Character_Maximum_Length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Data_Type = ”int” AND COLUMN_NAME like ”%ColumnNameHere%”’
[/cc]

To utilize correctly, replace the ColumnNameHere with the name of the column you want to find (or what the column name should be like).

It will then return you all the columns on the server that are named like the column you specified.