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_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 '%EmployeeID%'
ORDER BY schema_name, table_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_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
ORDER BY schema_name, table_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.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2005 – Get Field Name and Type of Database Table
Next Post
SQL SERVER – EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle

Related Posts

191 Comments. Leave new

  • Pinal sir, please help me find out list of columns/tables in a database based on a given column value.
    running through migration and due to nomenclature changes, couldn’t able to figure out corresponding attribute.
    Hoping if i can matchup based on column value, can match up.

    Reply

Leave a Reply

Menu