Find all tables containing column with specified name

Locate Tables by Column Name in SQL Server

Discover how to efficiently find all tables within a SQL Server database that contain a column with a specified name using system views and T-SQL queries.

When working with complex SQL Server databases, it's common to need to identify which tables contain a specific column. This can be crucial for schema analysis, impact assessment before making changes, or simply understanding data relationships. Manually inspecting each table can be time-consuming and error-prone. Fortunately, SQL Server provides powerful system views that allow you to query metadata about your database objects programmatically.

Understanding SQL Server System Views

SQL Server stores all its metadata (information about databases, tables, columns, indexes, etc.) in system views. These views are part of the sys schema and are designed to provide a consistent and structured way to access database catalog information. For finding columns, the most relevant views are sys.columns and sys.tables (or sys.objects filtered for tables). Joining these views allows us to link column definitions back to their parent tables.

erDiagram
    "sys.tables" ||--o{ "sys.columns" : "contains"
    "sys.tables" { 
        int object_id PK
        nvarchar name
        nvarchar schema_id
    }
    "sys.columns" { 
        int object_id FK
        int column_id PK
        nvarchar name
        int system_type_id
    }

Entity-Relationship Diagram of relevant SQL Server system views

Method 1: Using sys.columns and sys.objects

This is the most common and recommended approach. It involves joining sys.columns (which lists all columns in the database) with sys.objects (which lists all database objects, including tables) and filtering by object type and column name. We also include sys.schemas to get the schema name, which is good practice for fully qualifying table names.

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    TYPE_NAME(c.system_type_id) AS DataType
FROM
    sys.tables AS t
INNER JOIN
    sys.columns AS c ON t.object_id = c.object_id
WHERE
    c.name = 'YourColumnName'
ORDER BY
    SchemaName, TableName;

SQL query to find tables by column name using sys.tables and sys.columns

Method 2: Using INFORMATION_SCHEMA.COLUMNS

The INFORMATION_SCHEMA views provide an ANSI-standard, database-independent way to retrieve metadata. While generally less performant than sys views for SQL Server-specific tasks, they are useful for cross-database compatibility. The INFORMATION_SCHEMA.COLUMNS view contains details about all columns in all tables and views.

SELECT
    TABLE_SCHEMA AS SchemaName,
    TABLE_NAME AS TableName,
    COLUMN_NAME AS ColumnName,
    DATA_TYPE AS DataType
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMN_NAME = 'YourColumnName'
ORDER BY
    TABLE_SCHEMA, TABLE_NAME;

SQL query to find tables by column name using INFORMATION_SCHEMA.COLUMNS

Searching for Partial Column Names

If you're not sure of the exact column name or want to find columns that contain a specific string, you can use the LIKE operator with wildcards (%).

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    TYPE_NAME(c.system_type_id) AS DataType
FROM
    sys.tables AS t
INNER JOIN
    sys.columns AS c ON t.object_id = c.object_id
WHERE
    c.name LIKE '%PartofColumnName%'
ORDER BY
    SchemaName, TableName;

SQL query to find tables by partial column name using LIKE

This article provides robust methods for locating tables based on column names in SQL Server. By leveraging system views, you can efficiently manage and understand your database schema, which is a fundamental skill for any database professional.