In MS SQL Server, you sometimes need to discover all the tables that contain some specific column. This is largely used when you are working in large databases that have many other tables with the same column names. SQL Server gives you its system views such as INFORMATION_SCHEMA.COLUMNS and sys.columns, for querying the metadata and getting all the details of the table.
Table of Contents
Methods to Find Tables Containing a Specific Column
SQL Server gives you the INFORMATION_SCHEMA.COLUMNS view, which contains all the details about the columns present in the database. Let’s understand this with an example.
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'YourColumnName';
Example: You need to find all the tables that contain a column named order_id.
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'customer_id';
Output:
|
TABLE_SCHEMA |
TABLE_NAME |
COLUMN_NAME |
| dbo | Orders | customer_id |
| sales | Transactions | customer_id |
It works in all the SQL Server versions and returns schema names along with the table name. It can be filtered further by using the TABLE_SCHEMA. But the only limitation of this is it doesn’t provide detailed metadata like data types.
2. Using sys.columns and sys.tables
The sys.columns and sys.tables are the system views that give a more direct way to find the tables that contains a specific column.
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name = 'YourColumnName';
Example: Find all the tables that contain column email.
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name = 'email';
Output:
|
TableName |
ColumnName |
| Customers | email |
| Users | email |
| Employees | email |
It is more effective than INFORMATION_SCHEMA.COLUMNS and works well with large datasets. But the only setback is it does not include a schema name.
For including schema names in the result, you can join sys.schemas, sys.tables, and sys.columns.
SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.name = 'YourColumnName';
Example: You want to find all the tables containing order_date with schema information.
SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.name = 'order_date';
Output:
|
SchemaName |
TableName |
ColumnName |
| dbo | Orders | order_date |
| sales | Transactions | order_date |
| reports | Invoices | order_date |
It includes the schema name for better identification and works well for databases with multiple schemas.
4. Finding Tables with Partial Colum Name Matches
Suppose you are unsure about the exact column name, in that case, you can use the LIKE operator for partial matches.
SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.name LIKE '%YourPartialColumnName%';
Example: You need the tables that contain any column with ‘date’ in its name
SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.name LIKE '%date%';
Output:
|
SchemaName |
TableName |
ColumnName |
| dbo | Orders | order_date |
| sales | Transactions | transaction-date |
| finance | Payments | payment_date |
It helps find similar column names, but it also has a drawback. For example, searching for created_date or updated_date may return unwanted results if multiple columns contain the specified partial name.
Practical Examples
Example 1: Find all tables containing a “username” column
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'username';
The above query helps to identify all tables that contain the username column.
SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.name LIKE '%price%';
This query retrieves all tables and schemas containing at least one column with “price” in its name.
Conclusion
Choosing appropriate system views is critical for efficient metadata retrieval. Of course, INFORMATION_SCHEMA.COLUMNS is useful for easy column name searches, but sys.columns and sys.tables perform better. For schema-specific information, joining sys.schemas will give you additional information. For partial column names, using the LIKE operator can be useful, but optimizing queries through indexing the metadata ensures your database performs faster lookups. By following these best practices, you’ll improve query efficiency and enhance database performance.