[SQLServer]列サイズ一覧を取得する

with ColumnDatas as(
    select distinct
        col1.name as col_name
        ,case
            when typ.name in ('bigint', 'int', 'smallint', 'tinyint', 'bit', 'datetime2') then
                typ.name
            when typ.name in ('nchar', 'nvarchar', 'varbinary') then
                case 
                    when col1.max_length=-1 then concat( typ.name, '(max)')
                    else concat( typ.name, '(', cast(col1.max_length/2 as nvarchar(5)), ')')
                end
            when typ.name in ('decimal') then
                concat( '"', typ.name, '(',cast(col1.precision as nvarchar(5)), ',', cast(col1.scale as nvarchar(5)), ')"' ) 
            else concat( typ.name, '(', cast( col1.max_length as nvarchar(5)), ')')
	    end as column_length
    from sys.columns as col1
    inner join sys.types as typ on col1.user_type_id=typ.user_type_id
),
ColumnNames as (
    select distinct
        col_name
    from ColumnDatas
)
select
    cn.col_name as column_name
    ,stuff((
        select
            concat(',', cd.column_length)
        from ColumnDatas as cd
        WHERE
            cn.col_name=cd.col_name
        order by cd.column_length
        for XML Path ('')
    ),1,1,'') as column_sizes
from ColumnNames as cn
order by cn.col_name

with ColumnDatas as(
    select distinct
        col1.name as col_name
        ,typ.name as type_name
        ,case
            when typ.name in ('nchar', 'nvarchar') then
                case 
                    when col1.max_length=-1 then 'max'
                    else cast(col1.max_length/2 as nvarchar(5))
                end
            when typ.name in ('decimal') then
                concat( cast(col1.precision as nvarchar(5)), '(', cast(col1.scale as nvarchar(5)), ')' ) 
            else cast( col1.max_length as nvarchar(5))
	    end as column_length
    from sys.columns as col1
    inner join sys.types as typ on col1.user_type_id=typ.user_type_id
),
ColumnNames as (
    select distinct
        col_name
        ,type_name
    from ColumnDatas
)
select
    cn.col_name as column_name
    ,cn.type_name
    ,stuff((
        select
            concat(',', cd.column_length)
        from ColumnDatas as cd
        WHERE
            cn.col_name=cd.col_name
            and
            cn.type_name=cd.type_name
        order by cd.column_length
        for XML Path ('')
    ),1,1,'') as column_sizes
from ColumnNames as cn
order by cn.col_name