How Can I Find Tables Containing Columns with a Specific Name Pattern in SQL?

Locating Tables Based on Column Name Patterns in SQL

Efficiently managing large SQL databases often requires identifying tables containing columns that match specific naming conventions. This article presents two methods to achieve this.

Approach 1: Targeting Tables Only

This approach focuses solely on table definitions to find columns matching a particular pattern. The following T-SQL example demonstrates this:

SELECT
    c.name AS ColumnName,
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName
FROM
    sys.columns c
JOIN
    sys.tables t ON c.object_id = t.object_id
WHERE
    c.name LIKE '%MyName%'
ORDER BY
    TableName,
    ColumnName;

Approach 2: Including Tables and Views

This broader method searches across both tables and views for columns fitting the specified pattern. The query below illustrates this:

SELECT
    COLUMN_NAME AS ColumnName,
    TABLE_NAME AS TableName
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMN_NAME LIKE '%MyName%'
ORDER BY
    TableName,
    ColumnName;

Both queries return a list of tables (and views in the second case) containing columns matching the '%MyName%' pattern. Replace '%MyName%' with your desired pattern. This technique is invaluable for database administration tasks such as audits, schema optimization, and dependency tracking.

The above is the detailed content of How Can I Find Tables Containing Columns with a Specific Name Pattern in SQL?. For more information, please follow other related articles on the PHP Chinese website!