Find out column name, data type and maximum number of character in that column in SQL Server 2008

Yesterday I got one question about how to find column name, data_type and maximum number of character used in that column if it is varchar and if it is numeric than maximum number in that column. Basically you can find column_name and Data_type from the Information_Schema.Columns but it won’t return length of real data inserted into table so I tried to achieve it logically. Look at the script.
–using AdventureWorks database for demo
–if you don’t have AdventureWorks db
–you can use your own
use adventureworks
declare @sql varchar(max), @table sysname
–going to show you demo on Person.Contact table
–in adventureworks database,
–you can change your own table name
select @table=‘Contact’, @sql=
select @sql=
case when character_maximum_length is null then
    ”’+column_name+”’ as column_name,
    ”’+data_type+”’ as data_type,
    max(‘+column_name+‘) as column_length
    from ‘+Table_Schema + ‘.’+ Table_Name+‘ union all ‘
    ”’+column_name+”’ as column_name,
    ”’+data_type+”’ as data_type,
    max(len(‘+column_name+‘)) as column_length
    from ‘+Table_Schema + ‘.’+ Table_Name+‘ union all ‘
from information_schema.columns
where table_name=+@table+ and  data_type not in (‘xml’,‘bit’,‘datetime’,‘uniqueidentifier’)
set @sql=left(@sql,len(@sql)-9)
–print @sql
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
Microsoft SQL Server Blog. Fight the fear of SQL with Founder is Ritesh Shah