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

GO

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

@sql+'select

    '''+column_name+''' as column_name,

    '''+data_type+''' as data_type,

    max('+column_name+') as column_length

    from '+Table_Schema + '.'+ Table_Name+' union all '

else

    @sql+'select

    '''+column_name+''' as column_name,

    '''+data_type+''' as data_type,

    max(len('+column_name+')) as column_length

    from '+Table_Schema + '.'+ Table_Name+' union all '

end

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

exec(@sql)

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com