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
