Fetch Columns and datatypes from a given table

 The following query fetches  a list of all columns from the given table with their data types, data lengths and the  length of the longest value in that column


SELECT
   
    c.name 'Column Name',
    t.name 'Data type',
    c.max_length 'Max Length'

FROM  
    sys.columns c

INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id

WHERE
    c.object_id = OBJECT_ID('tblName')

GROUP BY 
    Object_Name(c.object_id),
    c.name ,
    t.name ,
    c.max_length