In this post, we want to discuss about all tables containing column with specified name in SQL Server. Before we get started, if you want to know necessity of Store Procedure, please go through the following article: Why do we need use Stored Procedure?
Say the column name is “MyColumnName”. The SQL is as bellow
SELECT c.name AS ColName, t.name AS TableName FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyColumnName%'
For searching in Tables and View in same DB
SELECT COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%MyColumnName%'