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)

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, Thanks for all the articles you produce. Like Paul above, I too look here first for help. Using your original script, I developed this to traverse all databases in an instance. I hope it helps someone out.

    /************** Drop table if it exists **************************/
    USE [MASTER]
    If OBJECT_ID(‘PIITable’,’U’) IS NOT NULL
    DROP TABLE PIITABLE

    /************** Create temp table in master **************************/

    Create table PIITable(ID Int Identity (1,1),
    Database_Name NVARCHAR(100),Table_Name NVARCHAR(100),SCHEMA_NAME NVARCHAR(20),
    COLUMN_NAME NVARCHAR(100),Data_Type NVARCHAR(100))

    /************** Create table variable to store database names **************************/
    Declare @Databases table
    (id int identity (1,1),DatabaseName nvarchar(100))

    /************** Insert database names into table variable **************************/
    Insert into @Databases

    SELECT name [Database Name]
    FROM [model].[sys].[databases]
    where (database_id > 4 and Name Not In(‘Distribution’))
    order by name

    –select * from @Databases –Select all databases from table variable (For testing only)

    /***************************** Set up variables for loop **********************/

    Declare @row int
    Declare @rows int
    Declare @database_name Nvarchar(MAX)
    Declare @Execdatabase Nvarchar(MAX)

    /***************************** Set up loop execution *****************************/
    Select @rows = (Select MAX(id) from @Databases)
    Set @row = 1

    While @row <= @rows
    Begin
    Set @database_name = (Select DatabaseName from @Databases where id = @row)

    Set @Execdatabase =
    'Use '+ ' '+ '['+ @database_name +']'+
    'Select ('''+ @database_name +''')as Database_Name, t.name AS table_name ,
    SCHEMA_NAME( t.schema_id
    )AS schema_name , c.name AS column_name,sys.types.name Data_Type INTO ##PrePIITable
    FROM sys.tables AS t INNER JOIN
    sys.columns AS c ON t.object_id = c.object_id INNER JOIN
    sys.types ON c.system_type_id = sys.types.system_type_id

    WHERE c.name IN( ''SSN'', ''FirstName'' , ''LastName'' , ''First_Name'' ,''Last_Name'' , ''MiddleName''
    , ''Name_First'' , ''Name_Last''
    )
    OR c.name LIKE ''%Form%'' OR c.name LIKE ''%addr%''

    GROUP BY t.name ,
    SCHEMA_NAME( t.schema_id
    ) , c.name,sys.types.name
    ORDER BY schema_name , table_name; '

    /***************** Execute above script **************************/
    exec (@Execdatabase)
    /********* Insert results into temporary Table (Master.dbo.PIITable) **************/
    INSERT INTO Master.dbo.PIITable
    Select * from ##PrePIITable

    /***************** Drop temp table for next itteration of loop **************************/
    If OBJECT_ID('tempdb..##PrePIITable','U') IS NOT NULL
    DROP TABLE ##PrePIITable

    Set @row = @row + 1
    End
    /***************** Select entire result set from PIITable **************************/

    Select Serverproperty('Servername') as 'Server',
    Isnull(Serverproperty('Instancename'),'Default')'Instance_Name',
    database_Name,Table_Name,Schema_Name,Column_Name,Data_Type

    from master.dbo.PIITable
    Where Data_Type ‘uniqueidentifier’
    Group By database_Name,Table_Name,Schema_Name,Column_Name,Data_Type
    order by Database_Name

    /***************** Select Database/table result set from PIITable *******************/

    Select Serverproperty(‘Servername’) as ‘Server’,
    Isnull(Serverproperty(‘Instancename’),’Default’)’Instance_Name’,
    database_Name,Table_Name,Schema_Name,Data_Type

    from master.dbo.PIITable
    Where Data_Type ‘uniqueidentifier’
    Group by database_name,Table_Name,Schema_Name,Data_Type
    order by Database_Name

    /********************** Clean up temp table in master ************/
    USE [MASTER]
    If OBJECT_ID(‘PIITable’,’U’) IS NOT NULL
    DROP TABLE PIITABLE

    Reply
  • This is beautiful, thank you so much. Works perfectly!

    Reply
    • Also, when searching for ethnic this is the query I used:

      USE [INSERT 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 ‘%ethnic%’
      ORDER BY schema_name, table_name;

      Reply
  • Hi, I would like to know how search for a column name with instances in several tables within a database and return the column value for each.

    Reply
  • Great help but you could solve capital sensitivity with
    WHERE UPPER(c.name) LIKE ‘%VOLUME%’

    Reply
  • If I just know a Database Name alone and wanted to know all maximum used columnnames across tables, is there any query to find that? this helps in understanding any Database for a first person logging into that DB

    Reply
  • Shreyas Jawalikar
    March 8, 2014 4:50 pm

    i want to find a column in a table when that column is updated by update query
    thanks in advance…….

    Reply
  • Shreyas Jawalikar
    March 8, 2014 4:52 pm

    and insert that record in another table
    thanks in advance….

    Reply
  • I am so annoyed with this error

    SELECT `groups`.`name`, `groups`.`title` FROM `v242_config_groups` `groups` JOIN `v242_config` `config` ON `groups`.`name` = `config`.`group_name` GROUP BY `groups`.`name` ORDER BY `groups`.`order` ASC

    Reply
  • Thanks for another very useful query PD! Appreciate all the work you do sharing your knowledge.

    Reply
  • Great post and responses. I’ve learned a lot from reading it.

    Reply
  • awsome

    Reply
  • You can simple use select * from all_Tab_Columns where COLUMN_NAME like ‘%XYZ%’

    Reply
  • Please Ans Me Question Serch NAme Plus Quantity

    Reply
  • Simple solution:
    Select * From INFORMATION_SCHEMA.COLUMNS
    Where COLUMN_NAME =’Enter Column Name Here’

    Reply
  • Simple solution:
    select * from INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME =’Enter Column Name Here’

    Reply
  • Very useful, thanks a lot.

    Reply
  • You can also RUN this one line Query To get the Result on your SCHEMA…
    select Table_name from USER_TAB_COLUMNS where column_name LIKE ‘%EMP_ID%’;

    Reply
  • Hello,

    Can anyone help me with the following.

    I want to find column value = ‘%Trans%’ in all the tables in Test database.
    Is there any easiest way to find that? There are lots of scripts and stored procedures available on the internet but I am looking for some simplest way.

    Any help would be appreciated.

    Thanks.

    Reply
  • Very Helpful
    Thanks.

    Reply
  • Super, thanks

    Reply

Leave a Reply