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 Thursday, October 1, 2009 10:31 AM by Madhivanan

Comments

# The problem with len()?

Saturday, October 3, 2009 11:09 PM 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!