How to use sys.column and sys.table view In SQL

Q: How to use sys.column and sys.table view In SQL

Using sys.columns and sys.tables views, users can find all tables that contain a specific column name.

Using sys.columns and sys.tables views, users can find all tables that contain a specific column name.

We can use sys.column view to get the column information, and sys.tables view to get the database table names. Using JOIN we can merge the results of these views.

Syntax

Syntax to find tables that contain a specific column name using sys.column and sys.table views.

SELECT col.name AS [Column Name], tab.name AS [Table Name] FROM sys.columns col INNER JOIN sys.tables tab ON col.object_id = tab.object_id WHERE col.name LIKE ‘%Name%’ ORDER BY [Table Name], [Column Name]

Output:

Column Name	Table Name
DatabaseName	tbl_Errors_Stats
SQLInstanceName	tbl_Errors_Stats
StatisticsName	tbl_Errors_Stats
DatabaseName	tbl_QualifiedDBs
DatabaseName	tbl_Statistics_Update_Trans
SQLInstanceName	tbl_Statistics_Update_Trans
StatisticsName	tbl_Statistics_Update_Trans
TableName	tbl_Statistics_Update_Trans
DatabaseName	tbl_Statistics_Update_Trans_Post
SQLInstanceName	tbl_Statistics_Update_Trans_Post
StatisticsName	tbl_Statistics_Update_Trans_Post
TableName	tbl_Statistics_Update_Trans_Post

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: