Search for Column Name in Tables or Views

There be times when you are trying to find information in SQL, but not knowing where to find relevant data makes things even more challenging. I some times use the filter option in SQL Management Studio to narrow down the name of tables or views. However, it may also be useful to display all tables or views that have a specific column name.

For example, I was recently trying to find all tables or views that had a column name of “CI_UniqueID”. I was trying find more detailed information about drivers and knew that the “CI_UniqueID” was a specific identifier linking information about driver versions and source path locations. Running the below query assisted with exploring as many views as possible to find everything I needed to know about drivers in the CM database.

Here is a SQL query you can run to help find tables or views having a specific column name. Simply replace the variable with the name of the column.

DECLARE @Variable nvarchar(15)
SET @Variable = 'Column_Name'

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE
FROM INFORMATION_SCHEMA.columns
WHERE Column_Name=@Variable
order by TABLE_NAME

Example when searching for tables or views with “CI_UniqueID” as a column name:

Search for Column Name in Tables or Views

Search for Column Name in Tables or Views