Maximum length of each column

 

How do I find out the maximum length of each column in a table?

Here is the answer

declare @sql varchar(8000), @table sysname

select @table='your_table', @sql=''

select
        @sql=@sql+'select '''+@table+''' as table_name,'''+column_name+''' as column_name, 
                                max(len('+column_name+')) as column_length from '+@table+' union all '
from 
        information_schema.columns where table_name=''+@table+''

set @sql=left(@sql,len(@sql)-9)

exec(@sql)

Published 01 October 2009 10:31 by Madhivanan
Filed under: , ,

Comments

# The problem with len()?

03 October 2009 23:09 by Martin Bell UK SQL Server MVP

Sometimes you forget about the specific behaviour of a function such as LEN and scratch you head when things don't add up, until you go back and re-read the documentation!