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.
These articles present a detailed introduction to SQL basics and commonly used commands.-
SQL ORDER BY Clause Tutorial – The article on ‘SQL ORDER BY Clause Tutorial’ helps you understand how to sort records based on one or more fields in a query.
SQL GROUP BY Clause Tutorial – In the ‘SQL GROUP BY Clause Tutorial’, you’ll find detailed instructions on grouping rows to perform aggregate calculations efficiently.
How to Delete NULL Values in SQL – This article on ‘How to Delete NULL Values in SQL’ outlines effective strategies for identifying and removing null entries from your data.
Convert NVARCHAR Column to INT in SQL – The guide titled ‘Convert NVARCHAR Column to INT in SQL’ offers practical examples of safely converting data types in SQL.
Display Constraint Names in Oracle SQL – Learn how to retrieve all constraint names applied to tables by reading the article on ‘Display Constraint Names in Oracle SQL’.
Invalid SQL Type – In ‘Invalid SQL Type’, you’ll gain insight into the causes of type-related errors and learn how to fix them in your SQL code.
Original Purpose of SQL – The article on ‘Original Purpose of SQL’ takes you through the origins of SQL and its evolution into a standard language for relational databases.
SQL Privileges – Explore how user roles and access levels work in SQL by reading the article on ‘SQL Privileges’.
Download SQL Server 2012 – Follow the article on ‘Download SQL Server 2012’ to learn how to install and set up this version of SQL Server for development or practice.