Scribd
Upload
68 views1 page

Selecting Tables Containing A Column Name: Catalog Views Like

The document explains how to query a database to find tables containing a specific column name using SQL. It demonstrates a basic SELECT statement with a JOIN between sys.columns and sys.tables, filtering results based on an exact column name match. Additionally, it shows how to modify the query to find partial matches using the LIKE statement with wildcard characters.

Uploaded by

shishir174
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
68 views1 page

Selecting Tables Containing A Column Name: Catalog Views Like

The document explains how to query a database to find tables containing a specific column name using SQL. It demonstrates a basic SELECT statement with a JOIN between sys.columns and sys.tables, filtering results based on an exact column name match. Additionally, it shows how to modify the query to find partial matches using the LIKE statement with wildcard characters.

Uploaded by

shishir174
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Selecting Tables Containing a Column Name

With our basic knowledge of both catalog views and the LIKE statement, we are now


equipped to lookup all the tables in our system that contain a particular column
name:

SELECT
sys.columns.name AS ColumnName,
tables.name AS TableName
FROM
sys.columns
JOIN sys.tables ON
sys.columns.object_id = tables.object_id
WHERE
sys.columns.name = 'ColumnName'

We need to combine information from two catalogs, sys.tables and sys.columns, so


we’re using a JOIN statement. The two are associated by the object_id field, so
we JOIN on that field.

From there, it’s a simple matter of selecting the ColumnName and TableName of our


results, and finally, of course, only looking up records where sys.columns.name is equal
to our ColumnName string.

However, this query will only find exact matches of the column name. If we want to
find partial matches, we can use LIKE and % wildcard characters instead:

SELECT
sys.columns.name AS ColumnName,
tables.name AS TableName
FROM
sys.columns
JOIN sys.tables ON
sys.columns.object_id = tables.object_id
WHERE
sys.columns.name LIKE '%ColumnName%'

You might also like