Tuesday, September 08, 2020

SQL SERVER – Query to Find Column From All Tables of Database

 One question came up just a day ago while I was writing SQL SERVER – 2005 – Difference Between INTERSECT and INNER JOIN – INTERSECT vs. INNER JOIN.

How many tables in database AdventureWorks have column name like ‘EmployeeID’?

It was quite an interesting question and I thought if there are scripts which can do this would be great. I quickly wrote down following script which will go return all the tables containing specific column along with their schema name.

USE AdventureWorks
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_idAS 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 '%EmployeeID%'
ORDER BY schema_nametable_name;

SQL SERVER - Query to Find Column From All Tables of Database GetColumn

In above query replace EmployeeID with any other column name.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_idAS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID c.OBJECT_ID
ORDER BY schema_nametable_name;

SQL SERVER - Query to Find Column From All Tables of Database AllColumns

If you want to find all the column name from your database run following script. You can down any condition in WHERE clause to get desired result.