How to use INFORMATION_SCHEMA.COLUMNS View In SQL
Using INFORMATION_SCHEMA.COLUMNS view, you can find all tables that contain a specific column name in the SQL database.
We can use the INFORMATION_SCHEMA.COLUMNS to get the table names where the column name is like ‘%Err%’.
Syntax
Syntax to find tables that contain a specific column name using INFORMATION_SCHEMA.COLUMNS views.
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION as org_pos, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH as CML FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like ‘%Err%’ ORDER BY TABLE_NAME
Output
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | org_pos | DATA_TYPE | CML |
|---|---|---|---|---|---|---|
| SQL_DBA | dbo | tbl_Errors_Stats | Errodate | 6 | datetime | NULL |
| SQL_DBA | dbo | tbl_Errors_Stats | ErrorMsg | 5 | varchar | -1 |
Find all Tables that contain a specific Column name in SQL Server
When managing a database in SQL Server, it’s often necessary to find out which tables include a specific column name. This task is essential for database administrators and developers, especially when updating database structures, migrating data safely, or getting a better understanding of how information is organized.
There are two ways to find all tables that contain a specific column name in SQL Server:
- Using sys.column and sys.table view
- Using INFORMATION_SCHEMA.COLUMNS View