Column Max Length

From my “Spreadsheet sizer” script, this one helped me move sensibly away from pesky varchar(max) columns.

-- ColumnMaxLength.sql

DECLARE @TableName VARCHAR(255) = 'customers' --<< input
DECLARE @SchemaName VARCHAR(255) = 'dbo' 
DECLARE @sqlcmd varchar(max) 

select @sqlcmd = stuff((SELECT ' union all
select ' 
+ QUOTENAME(table_schema,'''') + ' [Schema], ' 
+ QUOTENAME(TABLE_NAME,'''') + ' [Table], ' 
+ quotename(column_name,'''') + ' [Column],
max(datalength(' + quotename(column_name) + ')) MaxLength 
from ' + quotename(table_schema) + '.' + quotename(table_name)
from information_schema.columns
where 1=1
AND table_name =  @TableName
AND table_schema = @SchemaName
order by column_name
for xml path(''),type).value('.','varchar(max)'),1,11,'')

exec(@sqlcmd)