Searching Column Names in Tables and Views in a Database in SQL

Searching Column Names in Tables and Views in a Database in SQL

Many times you have to Search Some column Names in Entire Database without Script it's very Difficult to get the table names or view Name you have to open Each table or View, then look for the Column you want which is  quite time consuming.

Below are the script to find Column Names in Table 

--To Search a Column Name in all Tables of a Database

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%Test%'

ORDER BY schema_name, table_name;

In above script Test is my ColumnName not sure exactly about column name therefore i have used like operator where columnName like '%Test'

It will display TableNames SchemaNames,ColumnName

--To Search a Column Name in all Views of a Database

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.views AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%Test%'

ORDER BY schema_name, table_name;

It will display ViewNames SchemaNames,ColumnName.