SQL Puzzle 7: How to Find All SQL Server Tables Containing a Specific Column Name

In real-world coding scenarios, we are often tasked with identifying all tables that contain a specific column name. This is a critical step when preparing for the next phase of changes or development in a project. In this blog, let's explore how we can efficiently retrieve the list of such tables, as illustrated in the image below

Expected Output:

Tap For Closer Look

Please try to answer the question, if you still not able to get it click below show button to see the answer.

---Using System Tables
SELECT
	S.Name AS SchemaName,
	T.Name AS TableName,
	C.Name AS ColumnName,
	CONCAT('[',S.name,'].[',T.name,'].[',C.Name,']') AS CompleteTableName
FROM sys.Tables AS T
INNER JOIN sys.columns AS C
	ON T.Object_ID=C.Object_Id
INNER JOIN sys.schemas AS s
	ON T.schema_id = S.schema_id
WHERE C.Name = 'Actor'


----Using Infomration_Schem Tables

SELECT 
	TABLE_SCHEMA AS SchemaName,
	TABLE_NAME AS TableName,
	COLUMN_NAME AS ColumnName,
	CONCAT('[',TABLE_SCHEMA,'].[',TABLE_NAME,'].[',COLUMN_NAME,']') AS CommpleteTableName
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Actor'