Gets the max size of every column in the specified table

Max Column Length.sql

--# Max Column Size
--## gets the max sizes of every column in the specified table
--### forgive me I do not remember where I got this, but it ain't mine

DECLARE @TableName sysname = 'table_name', @TableSchema sysname = 'schema'
DECLARE @SQL NVARCHAR(MAX)
 
SELECT @SQL = STUFF((SELECT 
'
UNION ALL 
select ' + QUOTENAME(Table_Name,'''') + ' AS Table_Name, ' + 
QUOTENAME(Column_Name,'''') + ' AS ColumnName, MAX(' + 
CASE WHEN DATA_TYPE IN ('XML','HierarchyID','Geometry','Geography','text','ntext')
     THEN 'DATALENGTH(' ELSE 'LEN(' END + QUOTENAME(Column_Name) + 
')) as [Max_Length], ' + QUOTENAME(C.DATA_TYPE,'''') + ' AS Data_Type, ' + 
CAST(COALESCE(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_SCALE,0) AS VARCHAR(10)) + 
'  AS Data_Width FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(Table_Name)
FROM INFORMATION_SCHEMA.COLUMNS C 
WHERE TABLE_NAME = @TableName 
AND table_schema = @TableSchema
AND DATA_TYPE IN ('varchar', 'char', 'nchar', 'nvarchar')
--AND DATA_TYPE NOT IN ('XML','HierarchyID','Geometry','Geography')
ORDER BY COLUMN_NAME 
FOR XML PATH(''),Type).value('.','varchar(max)'),1,11,'')  
--print @SQL
EXECUTE (@SQL)