Tag: tsql

SQL Server – Search Tables for a Column

You can use the following SQL to find all tables with a specific column name within your SQLServer database.

SELECT TABLE_NAME, COLUMN_NAME
FROM YourDbName.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'YourColumnName'
ORDER BY COLUMN_NAME

Or, use the LIKE() method if you don’t know your specific column name. If I use the LIKE() method I try to only throw the wildcard at the end of the string… it performs a bit better that way.

SELECT TABLE_NAME, COLUMN_NAME
FROM YourDbName.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE('SomeColumn%')
ORDER BY COLUMN_NAME

Figured I’d post this here because I’ll probably forget and want to know again in the future.

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.