Get Tables and Views containing Column with Specified Name

Here, I will explain how to get the list of table names which contains specific name. I feel this query is very helpful in real scenarios.

For example, We have one key in our tables and which is named as Unique_Key, now there is requirement to change this column name to Master_Key, so there is two way either we have to check all the tables and views and by one to make sure which table and view is using this column name or we can use the below query to identify the table name and and view name within seconds.

Method 1 (Search Tables): 

SELECT      
sc.name  AS 'ColumnName',
    st.name AS 'TableName'
FROM        
sys.columns sc
JOIN
sys.tables  st   ON sc.object_id = st.object_id
WHERE       
sc.name LIKE '%coulmn_name%'
ORDER BY    
TableName,
ColumnName;


Method 2 (Search Tables & Views):

SELECT      
COLUMN_NAME AS 'ColumnName',
TABLE_NAME AS  'TableName'
FROM        
INFORMATION_SCHEMA.COLUMNS
WHERE       
COLUMN_NAME LIKE '%coulmn_name%'
ORDER BY    
TableName,
ColumnName;