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

  • How can I search a value in a particular table without knowing column name? Would it be possible to get table name, column name and schema name it is in?

    Reply
  • Hello Pinal, is there any way to use sql queries to determine possible relationships between databases ( I know you cannot enforce key constraints between databases). probably querying the statistics object of both databases and matching column data between databases?

    Reply
  • Hi i am looking all blank column from all tables of a server.

    Please help.

    Reply
  • I need to extract all blank column from all tables of a single sql database..

    Reply
  • Hi Pinal,

    Noticed a small correction. in the order by, it should be t.name instead of t.table_name.

    Reply
  • Thanks….it helped me.

    Reply
  • Hi Pinal,

    Great blog. I visit here often through google looking for answers.
    This time, I couldnt find one…

    I am looking for a specific column name across all tables and all databases in SQL Server.

    Can you please help!?

    Reply
  • That helped. Thank you.

    Reply
  • Akhila Toleti
    May 23, 2017 11:50 pm

    Hi Pinal,
    now how do i rename this column from all the tables?

    Reply
  • Hi Pinal
    How can i get all blank column list from particular table with single query

    Reply
  • Sumit Ringane
    August 2, 2017 4:30 pm

    Hello pinal sir,
    i fetch table name from another table column and store table in variable.
    eg.
    declare @TableName nvarchar(50);

    select @TableName=Table_Name from TableRelation where id=1;
    print @TableName;

    how to select table base on table name which are assign in @TableName;

    Reply
    • You need to use Dynamic SQL which is not recommended in this case

      select @TableName=’SELECT * FROM +@TableName;
      EXEC(TableName);

      Reply
  • Curt Spnburgh
    July 6, 2018 8:30 am

    Great Script. Saved me lots of time today. Thank you old friend.

    Reply
  • Search all over for this kind of script. Yours is the only one that works. Thanks!

    Reply
  • Joan Catalan M
    May 15, 2019 1:45 am

    Hello, Have you ever do this in a linked server? discard previous comment

    Reply
  • Thanks a lot !! I always get answers from your posts .

    Reply
  • Find all tables containing column with specified name within linked server

    Reply
  • Find all tables containing column with specified name in linked server of sqlserver

    Reply
  • Keith Rowland
    April 9, 2020 7:35 pm

    Thanks for this. Very useful.

    Reply
  • Hi, Im trying to run this query on multiple servers but still getting error ” invalid column_name” any suggestions, if thes query can work with sp_MSforeachdb

    Reply
  • 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 ReplyCancel reply

Exit mobile version