[SQLServer]テーブル内の実データ長を一覧するクエリ

declare @SCHEMA_NAME nvarchar(100)='<調べたいテーブルのスキーマ名>'
declare @TABLE_NAME nvarchar(100)='<調べたいテーブルの名前>'

declare @COLUMNS table(
    COLUMN_NAME nvarchar(100)
)
insert into @COLUMNS
    SELECT
        col.name
    from sys.tables as tab
    inner join sys.columns as col on tab.object_id=col.object_id
    where
        tab.name=@TABLE_NAME
    order BY
    col.column_id


declare cur CURSOR for select * from @COLUMNS;
open cur;
declare @column_name nvarchar(100);
declare @SQL2 nvarchar(max);
IF OBJECT_ID(N'tempdb..#COLUMN_LENGTH_DATA', N'U') IS NOT NULL
begin
    drop table #COLUMN_LENGTH_DATA;
end
create table #COLUMN_LENGTH_DATA(
    column_name nvarchar(100),
    data_length int
)
fetch next from cur into @column_name;
WHILE @@FETCH_STATUS=0
BEGIN 
    set @sql2='
        insert into #COLUMN_LENGTH_DATA
        select
            ''' + @column_name + ''' as column_name,
            max(len([' + @column_name + '])) as data_length
        from ' + @SCHEMA_NAME + '.'+ @TABLE_NAME + '
    '
    exec sp_sqlexec @sql2

    fetch next from cur into @column_name;
END
close cur;
DEALLOCATE cur;

select * from #COLUMN_LENGTH_DATA;


--resource cleanup
IF OBJECT_ID(N'tempdb..#COLUMN_LENGTH_DATA', N'U') IS NOT NULL
begin
    drop table #COLUMN_LENGTH_DATA;
end