Finding a Column with a Specific Name in TSQL

I just recently ran into a situation where I needed to find a table that contained a specific column in the database. I’m not looking for a column value, I’m looking for the name of the column. So, like any good programmer would do, I spent some time Googling…

There were a number of unhelpful articles and answers on the subject. I sifted through everything and finally landed upon an answer that worked! Unfortunately I forgot where it came from, so if any of you know where this little trick came from feel free to let me know.

In order to find a column with a specific name in TSQL you can use the following query:

USE [YOUR_DATABASE_NAME]
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%[WHAT YOU ARE LOOKING FOR]%'
ORDER BY schema_name, table_name;

Just replace [YOUR_DATABASE_NAME] with the name of the database you are using and [WHAT YOU ARE LOOKING FOR] with the full column name or a part of the column name you are looking for.

Leave a Reply

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