Often we need to find all the tables in our database containing column with specific names.As we don’t know which tables contains the columns so we find ourselves manually searching for the columns in the tables.This can be a time consuming and error prone approach.
To find all tables containing columns with a specific name or pattern we can query the following SQL Server system tables
System tables are the tables which are supposed to be used by SQL server components.These tables mostly start with sys.
sys.columns This table contains a row for every column of each table in the database.The important columns in this table are
- name Name of the database object(table) to which this column belongs
- object_id Id of the database object(table in our case) to which this column belongs
sys.tables This table contains a row for each table in the database.The important columns in this table are
- name Name of the table
- id Id of the table
Following will return all the tables which contains a column containing User as a part of its name
SELECT columns.name AS ColumnName, tables.name AS TableName
FROM sys.columns columns
JOIN sys.tables tables ON columns.object_id = tables.object_id
WHERE columns.name LIKE '%User%'
object_id is an object identification number of an object.We match the table and column using this identification number.
Another way we can find the column with a specific name or pattern is by using Information_schema views.These views contains the metadata about the objects in the database.
Using Information_schema views we can find all the tables containing a column with a specific name as:
SELECT TABLE_NAME ,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%User%'