How To Find Table With Column Name in SQL Server

As a database admin, I find the table with the column name daily to fix the issues. Since it is one of my daily tasks, I have identified a few quick methods to perform this. In this article, let us discuss all these methods one by one.

How to find table by column name in SQL Server

Let us dive deeply into all the methods individually.

Method-1: Using INFORMATION_SCHEMA.COLUMNS

We can query the INFORMATION_SCHEMA.COLUMNS for this task. Find the below query.

Syntax

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'ColumnName'
AND TABLE_SCHEMA = 'SchemaName';

Example

We can execute the below query to get the lists of tables that contain the column name ProductID and table schema dbo.

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'ProductID'
AND TABLE_SCHEMA = 'dbo';

After executing the above query, I got the expected output below.

Check out How To Find Column Name In Table SQL Server

Method-2: Using sys.columns and sys.tables

You can also query the sys.columns and sys.tables for this purpose.

Syntax

SELECT 
    c.name AS 'ColumnName',
    t.name AS 'RespectiveTableName'
FROM 
    sys.columns c
JOIN 
    sys.tables t ON c.object_id = t.object_id
WHERE 
    c.name = 'Your column name'
AND 
    SCHEMA_NAME(t.schema_id) = 'Your table schema name';

Example

You can execute the below query to get the lists of tables that contain the table name ProductID and table schema dbo.

SELECT 
    c.name AS 'ColumnName',
    t.name AS 'RespectiveTableName'
FROM 
    sys.columns c
JOIN 
    sys.tables t ON c.object_id = t.object_id
WHERE 
    c.name = 'ProductID'
AND 
    SCHEMA_NAME(t.schema_id) = 'dbo';

After executing the above query, I got the expected output, as shown below.

Check out How To Check Table Description In SQL Server

Method-3: Using multiple columns at once

We can execute the below query to get the lists of tables that contain the column names VehicleID, VehicleName, VehicleBrand.

DECLARE @MyColumnNames NVARCHAR(MAX) = '''VehicleID'', ''VehicleName'', ''VehicleBrand'''
DECLARE @MySQL NVARCHAR(MAX)

SET @MySQL = '
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN (' + @MyColumnNames + ')
AND TABLE_SCHEMA = ''dbo'''

EXEC sp_executesql @MySQL

After executing the above query, I got the expected output, as shown below.

Conclusion

As a developer, it is essential to know how to retrieve the table name from a column name to fix the respective issues quickly. You can utilize the above methods based on your requirements.

You may also like following the articles below.

• How To Change Column Name In SQL Server
• How To Check If CDC Is Enabled On A Table In SQL Server
• How To Update Statistics On A Table In SQL Server