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;

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;

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)

SQL Data Storage, SQL Scripts, SQL Stored Procedure, SQL System Table
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
Exit mobile version