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

Query in SQL Server to find tables whose column names match a specific pattern

In a SQL Server database, it is possible to search for tables containing columns with a specified name pattern. This task can be performed efficiently using the LIKE operator.

To query table names containing column names similar to '%myName%':

Search form:

<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>

This query retrieves the column name and table name of each table that contains a column name that matches the specified pattern.

Search tables and views:

To extend the search to include views, you can use the INFORMATION_SCHEMA.COLUMNS table:

<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>

This query provides the same information as the previous query, but also includes columns from views that match the specified name pattern.

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