Get Size of SQL Columns

ColumnSize.sql

declare @results table
(
	ID varchar(36),
	TableName varchar(250),
	ColumnName varchar(250),
	DataType varchar(250),
	MaxLength varchar(250),
	Longest varchar(250),
	SQLText varchar(250)
)
INSERT INTO @results(ID,TableName,ColumnName,DataType,MaxLength,Longest,SQLText)
SELECT 
    NEWID(),
    Object_Name(c.object_id),
    c.name,
    t.Name,
    CASE 
        WHEN t.Name NOT LIKE '%char' THEN 'NA'
        WHEN c.max_length = -1 THEN 'MAX' 
        ELSE CAST(c.max_length AS VARCHAR)
    END,
    'NA',
    'SELECT MAX(LEN(' + c.name + ')) FROM ' + OBJECT_SCHEMA_NAME(c.object_id) + '.' + Object_Name(c.object_id)
FROM    
    sys.columns c
 		INNER JOIN sys.objects o ON (o.object_id = c.object_id)
		INNER JOIN sys.schemas s ON (s.schema_id = o.schema_id)
		INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE
    s.name = 'dbo'
	AND t.name IN ('varchar', 'char', 'nvarchar', 'nchar')


DECLARE @id varchar(36)
DECLARE @sql varchar(200)
declare @receiver table(theCount int)

DECLARE length_cursor CURSOR
    FOR SELECT ID, SQLText FROM @results WHERE MaxLength != 'NA'
OPEN length_cursor
FETCH NEXT FROM length_cursor
INTO @id, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @receiver (theCount)
    EXEC(@sql)

    UPDATE @results
    SET Longest = (SELECT theCount FROM @receiver)
    WHERE ID = @id

    DELETE FROM @receiver

    FETCH NEXT FROM length_cursor
    INTO @id, @sql
END
CLOSE length_cursor
DEALLOCATE length_cursor

SELECT 
    TableName, 
    ColumnName, 
    DataType, 
    [MaxLength], 
    Longest,
	FORMAT((Longest / CAST(IIF(ISNUMERIC([MaxLength])=1, [MaxLength], 8000) AS decimal(18,2))) * 100, '0.0') AS [Filled]
FROM 
    @results
ORDER BY
	(Longest / CAST(IIF(ISNUMERIC([MaxLength])=1, [MaxLength], 8000) AS decimal(18,2))) ASC