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

Saturday, February 20, 2010 |

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