データベース内の列のサイズを、列名ごとに出力するクエリです。
1 | with ColumnDatas as( |
2 | select distinct |
3 | col1.name as col_name |
4 | ,case |
5 | when typ.name in ('bigint', 'int', 'smallint', 'tinyint', 'bit', 'datetime2') then |
6 | typ.name |
7 | when typ.name in ('nchar', 'nvarchar', 'varbinary') then |
8 | case |
9 | when col1.max_length=-1 then concat( typ.name, '(max)') |
10 | else concat( typ.name, '(', cast(col1.max_length/2 as nvarchar(5)), ')') |
11 | end |
12 | when typ.name in ('decimal') then |
13 | concat( '"', typ.name, '(',cast(col1.precision as nvarchar(5)), ',', cast(col1.scale as nvarchar(5)), ')"' ) |
14 | else concat( typ.name, '(', cast( col1.max_length as nvarchar(5)), ')') |
15 | end as column_length |
16 | from sys.columns as col1 |
17 | inner join sys.types as typ on col1.user_type_id=typ.user_type_id |
18 | ), |
19 | ColumnNames as ( |
20 | select distinct |
21 | col_name |
22 | from ColumnDatas |
23 | ) |
24 | select |
25 | cn.col_name as column_name |
26 | ,stuff(( |
27 | select |
28 | concat(',', cd.column_length) |
29 | from ColumnDatas as cd |
30 | WHERE |
31 | cn.col_name=cd.col_name |
32 | order by cd.column_length |
33 | for XML Path ('') |
34 | ),1,1,'') as column_sizes |
35 | from ColumnNames as cn |
36 | order by cn.col_name |
こんな風に出力します。
旧バージョン
decimalは 有効桁数(小数点桁数) の形式で表示します。
1 | with ColumnDatas as( |
2 | select distinct |
3 | col1.name as col_name |
4 | ,typ.name as type_name |
5 | ,case |
6 | when typ.name in ('nchar', 'nvarchar') then |
7 | case |
8 | when col1.max_length=-1 then 'max' |
9 | else cast(col1.max_length/2 as nvarchar(5)) |
10 | end |
11 | when typ.name in ('decimal') then |
12 | concat( cast(col1.precision as nvarchar(5)), '(', cast(col1.scale as nvarchar(5)), ')' ) |
13 | else cast( col1.max_length as nvarchar(5)) |
14 | end as column_length |
15 | from sys.columns as col1 |
16 | inner join sys.types as typ on col1.user_type_id=typ.user_type_id |
17 | ), |
18 | ColumnNames as ( |
19 | select distinct |
20 | col_name |
21 | ,type_name |
22 | from ColumnDatas |
23 | ) |
24 | select |
25 | cn.col_name as column_name |
26 | ,cn.type_name |
27 | ,stuff(( |
28 | select |
29 | concat(',', cd.column_length) |
30 | from ColumnDatas as cd |
31 | WHERE |
32 | cn.col_name=cd.col_name |
33 | and |
34 | cn.type_name=cd.type_name |
35 | order by cd.column_length |
36 | for XML Path ('') |
37 | ),1,1,'') as column_sizes |
38 | from ColumnNames as cn |
39 | order by cn.col_name |
出力結果はこんな感じになります。
