Finding Columns with specific names in SQL Server

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

• sys.columns
• sys.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.

Information_schema views

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

 

Follow on: