Profile table varchar lengths T-SQL

profile_table_varchar_lengths.sql

CREATE PROCEDURE profile_table_varchar_lengths @table nvarchar(256)
AS BEGIN
    IF OBJECT_ID(@table, 'U') IS NULL
        RAISERROR ('Table %s does not exist', 16, 1, @table) WITH NOWAIT;
    DECLARE @sql nvarchar(max);

    with varchar_cols as (
        SELECT c.name, t.name as type, c.max_length
        from sys.columns c
        join sys.types t on t.system_type_id = c.system_type_id
        where c.object_id = object_id(@table)
        AND t.name IN ('varchar', 'nvarchar')
    )

    SELECT @sql = string_agg(cast(
        'SELECT ''' + name + ''' as name, ' +
        '''' + type + ''' as type, ' +
        'CAST(''' + CAST(max_length as nvarchar(128)) + ''' as int) as declared_max_length, ' +
        'max(len(' + name + ')) as max_length ' +
        'FROM ' + @table
        as nvarchar(max)), ' UNION ALL ') + ' ORDER BY 1'
    from varchar_cols
    ;

    EXEC sp_executesql @sql;

END