How to Find Tables with Columns Matching a Specific Name in SQL?

Find the table containing the column with the specified name

Tables in a database contain columns with specific names. Useful to find all tables that contain a column with a specific name. This query allows you to identify these tables:

SQL query:

<code class="language-sql">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;</code>

Instructions:

This query joins the sys.columns and sys.tables tables to find the column name (c.name) and table name (TableName) that match the specified name ('MyName'). The SCHEMA_NAME function is used to include the schema name in the TableName result. Tables containing multiple matching columns can be easily identified by sorting by TableName and ColumnName.

Extended functions:

You can also include views in the search by replacing the sys.tables table with sys.objects:

<code class="language-sql">SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;</code>

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