Find the Column Name in a table or View in sql

With the help of below sql script we can find the column name and data type of the column in table or view.

use AdventureWorks2012 DECLARE @columnname VARCHAR(25) SET @columnname = 'Employee'--enter the search column name SELECT SCHEMA_NAME(t.schema_id) AS [Schema Name]        ,t.NAME AS [Table or View Name]        ,c.NAME AS [Column Name]        ,dt.NAME AS [Data Type]        ,c.max_length as size FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID INNER JOIN sys.types dt ON c.system_type_id = dt.system_type_id WHERE c.NAME LIKE '%' + @columnname + '%'       AND dt.NAME <> 'sysname'  UNION  --Get the same from all the views SELECT SCHEMA_NAME(v.schema_id) AS [Schema Name]        ,v.NAME AS [Table or View Name]        ,c.NAME AS [Column Name]        ,dt.NAME AS [Data Type]        ,c.max_length AS Size FROM sys.VIEWS AS v INNER JOIN sys.columns c ON v.OBJECT_ID = c.OBJECT_ID INNER JOIN sys.types dt ON c.system_type_id = dt.system_type_id WHERE c.NAME LIKE '%' + @columnname + '%'       AND dt.NAME <> 'sysname'

Here I am searching a column name which having column name like ‘Employee’.

See the Output

Let’s see the column name in table

  

Similarly we can see it in view also.