(8.0.32)
drop procedure ddl2json;
delimiter //
create procedure ddl2json(
in p_schema_name varchar(4000)
, in p_table_name varchar(4000)
)
begin
declare v_comments varchar(4000);
declare v_partition_type varchar(4000);
declare v_partition_key varchar(4000);
declare i_col_count integer;
declare i_partition_count integer;
declare i_primary_count integer;
declare i_index_count integer;
declare v_wk1 varchar(4000);
declare v_wk2 varchar(4000);
declare v_wk3 varchar(4000);
declare v_wk4 varchar(4000);
declare v_wk5 varchar(4000);
declare v_wk6 varchar(4000);
declare v_wk7 varchar(4000);
declare i_wk1 bigint;
declare i_wk2 bigint;
declare i_wk3 bigint;
declare i_wk4 bigint;
-- カーソル関連
declare done int;
declare cur2 cursor for select ordinal_position
, column_name
, data_type
, character_maximum_length
, numeric_precision
, numeric_scale
, is_nullable
, column_comment
from information_schema.columns
where table_schema= p_schema_name
and table_name = p_table_name
order by ordinal_position
;
declare cur3 cursor for select partition_ordinal_position
, partition_name
, partition_description
from information_schema.partitions
where table_schema= p_schema_name
and table_name = p_table_name
order by partition_ordinal_position
;
declare cur4 cursor for select index_name,group_concat(concat('"',column_name,'"') order by seq_in_index separator ',') column_name
from information_schema.statistics
where table_schema = p_schema_name
and table_name = p_table_name
and index_name = 'PRIMARY'
group by index_name
order by index_name
;
declare cur5 cursor for select index_name,non_unique,group_concat(concat('"',column_name,'"') order by seq_in_index separator ',') column_name
from information_schema.statistics
where table_schema = p_schema_name
and table_name = p_table_name
and index_name != 'PRIMARY'
group by index_name,non_unique
order by index_name,non_unique
;
declare continue handler for sqlstate '02000' set done = 1;
-- テンポラリテーブルを作成し、JSON情報を格納する
drop temporary table if exists ttab1;
create temporary table ttab1(col1 int not null auto_increment primary key,col2 varchar(4000) );
insert into ttab1(col2) values('{');
insert into ttab1(col2) values(concat(' "schema_name": "',p_schema_name,'",') );
insert into ttab1(col2) values(concat(' "table_name": "',p_table_name,'",') );
-- テーブルコメント情報
set v_comments = ( select table_comment
from information_schema.tables
where table_schema = p_schema_name
and table_name = p_table_name );
insert into ttab1(col2) values(concat(' "table_desc": "',v_comments,'",') );
-- カラム情報
set i_col_count = ( select count(*)
from information_schema.columns
where table_schema= p_schema_name
and table_name = p_table_name );
insert into ttab1(col2) values(' "cols": [');
set done = 0;
open cur2;
fetch cur2 into i_wk1
, v_wk1
, v_wk2
, i_wk2
, i_wk3
, i_wk4
, v_wk3
, v_wk4;
while done != 1 do
if i_wk1 != i_col_count then
set v_wk5 = ',';
else
set v_wk5 = '';
end if;
if v_wk3 = 'NO' then
set v_wk6 = 'true';
else
set v_wk6 = 'false';
end if;
case
when v_wk2 in ('int','bigint','datetime') then
set v_wk7 = v_wk2;
when v_wk2 = 'decimal' then
set v_wk7 = concat(v_wk2,'(',cast(i_wk3 as char),',',cast(i_wk4 as char),')');
when v_wk2 = 'varchar' then
set v_wk7 = concat(v_wk2,'(',cast(i_wk2 as char),')');
else
select 'unsupported data type found';
end case;
insert into ttab1(col2) values(concat(' { "col_name": "',v_wk1,'", "col_type": "',v_wk7,'", "not_null": "',v_wk6,'", "col_desc": "',v_wk4,'" }',v_wk5) );
fetch cur2 into i_wk1
, v_wk1
, v_wk2
, i_wk2
, i_wk3
, i_wk4
, v_wk3
, v_wk4;
end while;
close cur2;
insert into ttab1(col2) values(' ],');
-- パーティション情報
set i_partition_count = ( select count(*)
from information_schema.partitions
where table_schema= p_schema_name
and table_name = p_table_name
and partition_method is not null );
if i_partition_count = 0 then
insert into ttab1(col2) values(' "partition": {},');
else
-- パーティションタイプとパーティションキー取得
set v_partition_type = ( select partition_method
from information_schema.partitions
where table_schema= p_schema_name
and table_name = p_table_name
limit 1 );
insert into ttab1(col2) values(' "partition": {');
insert into ttab1(col2) values(concat(' "partition_type": "',v_partition_type,'",') );
set v_partition_key = ( select replace(partition_expression,'`','')
from information_schema.partitions
where table_schema= p_schema_name
and table_name = p_table_name
limit 1 );
insert into ttab1(col2) values(concat(' "partition_col": "',v_partition_key,'",') );
insert into ttab1(col2) values(' "partition_defs" : [');
-- パーティション定義情報
set done = 0;
open cur3;
fetch cur3 into i_wk1
,v_wk1
,v_wk2;
while done != 1 do
if i_wk1 != i_partition_count then
set v_wk3 = ',';
else
set v_wk3 = '';
end if;
if v_partition_type = 'RANGE COLUMNS' then
insert into ttab1(col2) values(concat(' { "partition_name": "',v_wk1,'", "partition_limit": "',v_wk2,'" }',v_wk3) );
elseif v_partition_type = 'LIST COLUMNS' then
insert into ttab1(col2) values(concat(' { "partition_name": "',v_wk1,'", "partition_limit": "(',v_wk2,')" }',v_wk3) );
elseif v_partition_type = 'HASH' then
insert into ttab1(col2) values(concat(' { "partition_name": "',v_wk1,'" }',v_wk3) );
else
select 'unsupported partition type found';
end if;
fetch cur3 into i_wk1
,v_wk1
,v_wk2;
end while;
close cur3;
insert into ttab1(col2) values(' ]');
insert into ttab1(col2) values(' },');
end if;
-- 主キー情報
set i_primary_count = ( select count(*)
from information_schema.statistics
where table_schema = p_schema_name
and table_name = p_table_name
and index_name = 'PRIMARY');
if i_primary_count = 0 then
insert into ttab1(col2) values(' "primary": {},');
else
set done = 0;
open cur4;
fetch cur4 into v_wk1
, v_wk2;
while done != 1 do
insert into ttab1(col2) values(concat(' "primary": { "constraint_name": "',concat(p_table_name,'pk'),'", "index_cols": [',v_wk2,'] },') );
fetch cur4 into v_wk1
, v_wk2;
end while;
close cur4;
end if;
-- インデックス情報
set i_index_count = ( select count(distinct index_name)
from information_schema.statistics
where table_schema = p_schema_name
and table_name = p_table_name
and index_name != 'PRIMARY'
);
if i_index_count = 0 then
insert into ttab1(col2) values(' "indexes": []');
else
insert into ttab1(col2) values(' "indexes": [');
set i_wk2 = 0;
set done = 0;
open cur5;
fetch cur5 into v_wk1
, i_wk1
, v_wk2;
while done != 1 do
set i_wk2 = i_wk2+1;
if i_wk2 != i_index_count then
set v_wk3 = ',';
else
set v_wk3 = '';
end if;
if i_wk1 = 0 then
set v_wk4 = 'true';
else
set v_wk4 = 'false';
end if;
insert into ttab1(col2) values(concat(' { "index_name": "',v_wk1,'", "index_cols": [',v_wk2,'] ,"unique": "',v_wk4,'"}',v_wk3) );
fetch cur5 into v_wk1
, i_wk1
, v_wk2;
end while;
close cur5;
insert into ttab1(col2) values(' ]');
end if;
insert into ttab1(col2) values('}');
select col2 from ttab1 order by col1;
end
//
delimiter ;
call ddl2json('test','tab1');
call ddl2json('test','tab2');
call ddl2json('test','tab3');
call ddl2json('test','tab4');
MYSQL_PWD='password' mysql -N -B -uroot test <<EOF > tab1.json
call ddl2json('test','tab1');
EOF
- Oracle
(19c)
set serveroutput on format wrapped
create or replace procedure ddl2json(
p_schema_name in varchar2 default 'TEST'
,p_table_name in varchar2 default 'TAB1'
) as
v_comments all_tab_comments.comments%type;
v_partition_type all_part_tables.partitioning_type%type;
v_partition_key all_part_key_columns.column_name%type;
i_col_count integer;
i_partition_count integer;
i_primary_count integer;
i_index_count integer;
v_wk1 varchar2(4000);
v_wk2 varchar2(4000);
v_wk3 varchar2(4000);
i_wk1 integer;
begin
dbms_output.put_line('{');
dbms_output.put_line(' "schema_name": "'||p_schema_name||'",');
dbms_output.put_line(' "table_name": "'||p_table_name||'",');
-- テーブルコメント情報
v_comments:='';
for c1 in (select comments
from all_tab_comments
where owner = p_schema_name
and table_name = p_table_name ) loop
v_comments:= c1.comments;
end loop;
dbms_output.put_line(' "table_desc": "'||v_comments||'",');
-- カラム情報
select count(*) into i_col_count
from all_tab_cols
where owner = p_schema_name
and table_name = p_table_name ;
dbms_output.put_line(' "cols": [');
for c2 in ( select t1.column_id,t1.column_name,t1.data_type,t1.data_length,t1.data_precision,t1.data_scale,t1.nullable,t2.comments
from all_tab_cols t1
left outer join all_col_comments t2
on t1.owner = t2.owner
and t1.table_name = t2.table_name
and t1.column_name = t2.column_name
where t1.owner = p_schema_name
and t1.table_name = p_table_name
order by t1.column_id ) loop
if c2.column_id != i_col_count then
v_wk1:= ',';
else
v_wk1:= '';
end if;
if c2.nullable = 'N' then
v_wk2:= 'true';
else
v_wk2:= 'false';
end if;
case
when c2.data_type = 'NUMBER' AND c2.data_precision is null then
v_wk3 := c2.data_type;
when c2.data_type = 'NUMBER' AND c2.data_precision is not null and c2.data_scale is null then
v_wk3 := c2.data_type||'('||c2.data_precision||')';
when c2.data_type = 'NUMBER' AND c2.data_precision is not null and c2.data_scale is not null then
v_wk3 := c2.data_type||'('||c2.data_precision||','||c2.data_scale||')';
when c2.data_type like '%CHAR%' then
v_wk3 := c2.data_type||'('||c2.data_length||')';
when c2.data_type like '%TIMESTAMP%' then
v_wk3 := c2.data_type;
else
dbms_output.put_line('unsupported data type found');
end case;
dbms_output.put_line(' { "col_name": "'||c2.column_name||'", "col_type": "'||v_wk3||'", "not_null": "'||v_wk2||'", "col_desc": "'||c2.comments||'" }'||v_wk1);
end loop;
dbms_output.put_line(' ],');
-- パーティション情報
select count(*) into i_partition_count
from all_tab_partitions
where table_owner = p_schema_name
and table_name = p_table_name ;
if i_partition_count = 0 then
dbms_output.put_line(' "partition": {},');
else
-- パーティションタイプとパーティションキー取得
select partitioning_type into v_partition_type
from all_part_tables
where owner = p_schema_name
and table_name = p_table_name ;
dbms_output.put_line(' "partition": {');
dbms_output.put_line(' "partition_type": "'||v_partition_type||'",');
select column_name into v_partition_key
from all_part_key_columns
where owner = p_schema_name
and name = p_table_name ;
dbms_output.put_line(' "partition_col": "'||v_partition_key||'",');
dbms_output.put_line(' "partition_defs" : [');
-- パーティション定義情報
for c3 in ( select partition_position,partition_name,high_value from all_tab_partitions
where table_owner = p_schema_name
and table_name = p_table_name
order by partition_position
) loop
if c3.partition_position != i_partition_count then
v_wk1:= ',';
else
v_wk1:= '';
end if;
if v_partition_type = 'RANGE' then
dbms_output.put_line(' { "partition_name": "'||c3.partition_name||'", "partition_limit": "'||c3.high_value||'" }'||v_wk1);
elsif v_partition_type = 'LIST' then
dbms_output.put_line(' { "partition_name": "'||c3.partition_name||'", "partition_limit": "('||c3.high_value||')" }'||v_wk1);
elsif v_partition_type = 'HASH' then
dbms_output.put_line(' { "partition_name": "'||c3.partition_name||'" }'||v_wk1);
else
dbms_output.put_line('unsupported partition type found');
end if;
end loop;
dbms_output.put_line(' ]');
dbms_output.put_line(' },');
end if;
-- 主キー情報
select count(*) into i_primary_count
from all_constraints
where owner = p_schema_name
and table_name = p_table_name
and constraint_type='P' ;
if i_primary_count = 0 then
dbms_output.put_line(' "primary": {},');
else
for c4 in ( select t2.constraint_name,listagg('"'||t1.column_name||'"',',') within group ( order by t1.column_position) column_name
from all_ind_columns t1
inner join all_constraints t2
on t1.table_owner = t2.owner
and t1.table_name = t2.table_name
and t2.constraint_type = 'P'
and t1.index_name = t2.index_name
where t1.table_owner = p_schema_name
and t1.table_name = p_table_name
group by t2.constraint_name ) loop
dbms_output.put_line(' "primary": { "constraint_name": "'||c4.constraint_name||'", "index_cols": ['||c4.column_name||'] },');
end loop;
end if;
-- インデックス情報
select count(*) into i_index_count
from all_indexes t1
where t1.table_owner = p_schema_name
and t1.table_name = p_table_name
and not exists ( select 1 from all_constraints t2
where t2.owner = t1.table_owner
and t2.table_name = t1.table_name
and t2.index_name = t1.index_name
and t2.constraint_type='P'
);
if i_index_count = 0 then
dbms_output.put_line(' "indexes": []');
else
dbms_output.put_line(' "indexes": [');
i_wk1:= 0;
for c5 in ( select t1.index_name,t1.uniqueness ,listagg('"'||t2.column_name||'"',',') within group ( order by t2.column_position) column_name
from all_indexes t1
inner join all_ind_columns t2
on t1.table_owner = t2.table_owner
and t1.table_name = t2.table_name
and t1.index_name = t2.index_name
where t1.table_owner = p_schema_name
and t1.table_name = p_table_name
and not exists ( select 1 from all_constraints t3
where t3.owner = t1.table_owner
and t3.table_name = t1.table_name
and t3.index_name = t1.index_name
and t3.constraint_type='P'
)
group by t1.index_name,t1.uniqueness ) loop
i_wk1 := i_wk1+1;
if i_wk1 != i_index_count then
v_wk1:= ',';
else
v_wk1:= '';
end if;
if c5.uniqueness = 'UNIQUE' then
v_wk2:= 'true';
else
v_wk2:= 'false';
end if;
dbms_output.put_line(' { "index_name": "'||c5.index_name||'", "index_cols": ['||c5.column_name||'] ,"unique": "'||v_wk2||'"}'||v_wk1);
end loop;
dbms_output.put_line(' ]');
end if;
dbms_output.put_line('}');
end;
/
sho error;
exec ddl2json
exec ddl2json('TEST','TAB1');
exec ddl2json('TEST','TAB2');
exec ddl2json('TEST','TAB3');
exec ddl2json('TEST','TAB4');
sqlplus -s test/test@pdb1 <<EOF > tab1.json
set serveroutput on format wrapped
set feed off
exec ddl2json('TEST','TAB1');
EOF
- PostgreSQL
(15)
drop procedure ddl2json;
create or replace procedure ddl2json(
p_schema_name in varchar default 'public'
, p_table_name in varchar default 'tab1'
)
language plpgsql
as $$
declare
v_comments varchar(4000);
v_partition_type varchar(4000);
v_partition_key varchar(4000);
i_col_count integer;
i_partition_count integer;
i_primary_count integer;
i_index_count integer;
v_wk1 varchar(4000);
v_wk2 varchar(4000);
v_wk3 varchar(4000);
i_wk1 integer;
r record;
begin
raise notice '{';
raise notice ' "schema_name": "%",' ,p_schema_name;
raise notice ' "table_name": "%",' ,p_table_name;
-- テーブルコメント情報
for r in ( select obj_description ( oid ,'pg_class') comments
from pg_class
where relname = p_table_name
and relnamespace = p_schema_name::regnamespace) loop
v_comments:= r.comments;
end loop;
raise notice ' "table_desc": "%",' ,v_comments;
-- カラム情報
select count(*) strict into i_col_count
from information_schema.columns
where table_catalog = current_database()
and table_schema = p_schema_name
and table_name = p_table_name
;
raise notice ' "cols": [';
for r in ( select t2.ordinal_position
, t2.column_name
, t2.data_type
, t2.character_maximum_length
, t2.numeric_precision
, t2.numeric_scale
, t2.is_nullable
, col_description ( t1.oid ,t2.ordinal_position ) description
from pg_class t1
inner join information_schema.columns t2
on t1.relname = t2.table_name
where t1.relname = p_table_name
and t1.relnamespace = p_schema_name::regnamespace
and t2.table_catalog='test'
and t2.table_schema = p_schema_name
order by t2.ordinal_position ) loop
if r.ordinal_position != i_col_count then
v_wk1:= ',';
else
v_wk1:= '';
end if;
if r.is_nullable = 'NO' then
v_wk2:= 'true';
else
v_wk2:= 'false';
end if;
case
when r.data_type in ('integer','bigint') then
v_wk3 := r.data_type;
when r.data_type = 'numeric' then
v_wk3 := r.data_type||'('||r.numeric_precision||','||r.numeric_scale||')';
when r.data_type like '%character%' then
v_wk3 := r.data_type||'('||r.character_maximum_length||')';
when r.data_type like '%timestamp%' then
v_wk3 := r.data_type;
else
raise notice 'unsupported data type found';
end case;
raise notice ' { "col_name": "%", "col_type": "%", "not_null": "%", "col_desc": "%" }%' ,r.column_name, v_wk3, v_wk2, r.description, v_wk1;
end loop;
raise notice ' ],';
-- パーティション情報
select count(*) strict into i_partition_count
from pg_inherits
where inhparent in (select oid
from pg_class
where relname = p_table_name
and relnamespace = p_schema_name::regnamespace
)
;
if i_partition_count = 0 then
raise notice ' "partition": {},';
else
-- パーティションタイプとパーティションキー取得
select case partstrat
when 'r' then 'range'
when 'l' then 'list'
when 'h' then 'hash'
else 'unknown'
end strict into v_partition_type
from pg_partitioned_table
where partrelid in (select oid
from pg_class
where relname = p_table_name
and relnamespace = p_schema_name::regnamespace
)
;
raise notice ' "partition": {';
raise notice ' "partition_type": "%",' ,v_partition_type;
select t1.attname strict into v_partition_key
from pg_attribute t1
where t1.attrelid in (select oid
from pg_class
where relname = p_table_name
and relnamespace = p_schema_name::regnamespace
)
and t1.attnum in (
select unnest(string_to_array(partattrs::text, ' ') )::int from pg_partitioned_table
where partrelid = t1.attrelid )
;
raise notice ' "partition_col": "%",' ,v_partition_key;
raise notice ' "partition_defs" : [';
-- -- パーティション定義情報
i_wk1 := 0;
for r in ( select replace(cast(t.oid::regclass as varchar),p_table_name||'_','') partition_name
, pg_get_expr(t.relpartbound, t.oid) partition_limit
from pg_inherits i
inner join pg_class t
on t.oid = i.inhrelid
where i.inhparent in (select oid
from pg_class
where relname = p_table_name
and relnamespace = p_schema_name::regnamespace
)
order by t.oid) loop
i_wk1 = i_wk1+1;
if i_wk1 != i_partition_count then
v_wk1:= ',';
else
v_wk1:= '';
end if;
if v_partition_type = 'range' then
v_wk2 := replace(regexp_replace(r.partition_limit,'FOR.*TO \(',''),')','');
raise notice ' { "partition_name": "%", "partition_limit": "%" }%' ,r.partition_name ,v_wk2 ,v_wk1;
elsif v_partition_type = 'list' then
v_wk2 := regexp_replace(r.partition_limit,'FOR.*IN ','');
raise notice ' { "partition_name": "%", "partition_limit": "%" }%' ,r.partition_name ,v_wk2 ,v_wk1;
elsif v_partition_type = 'hash' then
raise notice ' { "partition_name": "%" }%' ,r.partition_name ,v_wk1;
else
raise notice 'unsupported partition type found';
end if;
end loop;
raise notice ' ]';
raise notice ' },';
end if;
-- 主キー情報
select count(*) strict into i_primary_count
from information_schema.table_constraints
where table_catalog = 'test'
and table_schema = p_schema_name
and table_name = p_table_name
and constraint_type = 'PRIMARY KEY'
;
if i_primary_count = 0 then
raise notice ' "primary": {},';
else
for r in ( select t2.constraint_name constraint_name
,'"'||replace(replace(substring(t1.indexdef from (position('(' in t1.indexdef) )+1 for (position(')' in t1.indexdef) - position('(' in t1.indexdef)-1) ),' ',''),',','","')||'"' column_name
from pg_indexes t1
inner join information_schema.table_constraints t2
on t1.schemaname = t2.table_schema
and t1.tablename = t2.table_name
and t2.table_catalog = 'test'
and t2.constraint_type = 'PRIMARY KEY'
and t1.indexname = t2.constraint_name
where t1.schemaname = p_schema_name
and t1.tablename = p_table_name ) loop
raise notice ' "primary": { "constraint_name": "%", "index_cols": [%] },' ,r.constraint_name, r.column_name;
end loop;
end if;
-- インデックス情報
select count(*) strict into i_index_count
from pg_indexes t1
where t1.schemaname = p_schema_name
and t1.tablename = p_table_name
and not exists (
select 1 from information_schema.table_constraints
where table_catalog = 'test'
and table_schema = t1.schemaname
and table_name = t1.tablename
and constraint_name = t1.indexname
and constraint_type = 'PRIMARY KEY'
)
;
if i_index_count = 0 then
raise notice ' "indexes": []';
else
raise notice ' "indexes": [';
i_wk1:= 0;
for r in ( select t1.indexname
,t3.indisunique
,'"'||replace(replace(substring(t1.indexdef from (position('(' in t1.indexdef) )+1 for (position(')' in t1.indexdef) - position('(' in t1.indexdef)-1) ),' ',''),',','","')||'"' column_name
from pg_indexes t1
inner join pg_class t2
on t2.relnamespace = t1.schemaname::regnamespace
and t2.relname = t1.indexname
inner join pg_index t3
on t2.oid = t3.indexrelid
where t1.schemaname = p_schema_name
and t1.tablename = p_table_name
and not exists (
select 1 from information_schema.table_constraints
where table_catalog = 'test'
and table_schema = t1.schemaname
and table_name = t1.tablename
and constraint_name = t1.indexname
and constraint_type = 'PRIMARY KEY'
)
order by t2.oid ) loop
i_wk1 := i_wk1+1;
if i_wk1 != i_index_count then
v_wk1:= ',';
else
v_wk1:= '';
end if;
if r.indisunique is true then
v_wk2:= 'true';
else
v_wk2:= 'false';
end if;
raise notice ' { "index_name": "%", "index_cols": [%] ,"unique": "%"}%' ,r.indexname, r.column_name, v_wk2, v_wk1;
end loop;
raise notice ' ]';
end if;
raise notice '}';
end;
$$;
call ddl2json();
call ddl2json('public','tab1');
call ddl2json('public','tab2');
call ddl2json('public','tab3');
call ddl2json('public','tab4');
psql test <<EOF 2> tab1.json
call ddl2json('public','tab1');
EOF
sed -i 's/^NOTICE: //' tab1.json
- SQL Server
(2022)
drop procedure ddl2json;
go
create or alter procedure ddl2json(
@p_schema_name varchar(4000) = 'dbo'
,@p_table_name varchar(4000) = 'tab1'
)
as
begin
set nocount on;
declare @v_comments varchar(4000);
declare @v_partition_type varchar(4000);
declare @v_partition_key varchar(4000);
declare @i_col_count integer;
declare @i_partition_count integer;
declare @i_primary_count integer;
declare @i_index_count integer;
declare @v_wk1 varchar(4000);
declare @v_wk2 varchar(4000);
declare @v_wk3 varchar(4000);
declare @v_wk4 varchar(4000);
declare @v_wk5 varchar(4000);
declare @v_wk6 varchar(4000);
declare @v_wk7 varchar(4000);
declare @v_wk8 varchar(4000);
declare @i_wk1 bigint;
declare @i_wk2 bigint;
declare @i_wk3 bigint;
declare @i_wk4 bigint;
declare @i_wk5 bigint;
declare @i_wk6 bigint;
declare @i_wk7 bigint;
print '{';
print ' "schema_name": "'+@p_schema_name+'",';
print ' "table_name": "'+@p_table_name+'",';
-- テーブルコメント情報
set @v_comments = ( select cast(t2.value as varchar)
from sys.tables t1
inner join sys.extended_properties t2
on t1.object_id = t2.major_id
where t1.name = @p_table_name
and schema_name(t1.schema_id) = @p_schema_name
and t2.minor_id = 0 );
print ' "table_desc": "'+@v_comments+'",';
-- カラム情報
set @i_col_count = ( select count(*)
from sys.tables t1
inner join sys.columns t2
on t1.object_id = t2.object_id
left join sys.extended_properties t3
on t2.object_id = t3.major_id
and t2.column_id = t3.minor_id
where t1.name = @p_table_name
and schema_name(t1.schema_id) = @p_schema_name );
print ' "cols": [';
declare cur2 cursor for select t2.column_id
, t2.name
, t2.system_type_id
, t2.max_length
, t2.precision
, t2.scale
, t2.is_nullable
, cast(t3.value as varchar)
from sys.tables t1
inner join sys.columns t2
on t1.object_id = t2.object_id
left join sys.extended_properties t3
on t2.object_id = t3.major_id
and t2.column_id = t3.minor_id
where t1.name = @p_table_name
and schema_name(t1.schema_id) = @p_schema_name
order by t2.column_id
;
set @i_wk1 = 0
open cur2;
fetch next from cur2 into @i_wk2, @v_wk1, @i_wk3, @i_wk4, @i_wk5, @i_wk6, @i_wk7, @v_wk2;
while @@fetch_status = 0
begin
set @i_wk1 = @i_wk1 + 1;
if @i_wk1 != @i_col_count
set @v_wk5 = ',';
else
set @v_wk5 = '';
if @i_wk7 = 0
set @v_wk6 = 'true';
else
set @v_wk6 = 'false';
-- int-> 56 bigint-> 127 numeric-> 108 decimal-> 106
-- char-> 175 varchar-> 167 nchar-> 239 nvarchar-> 231
-- date-> 40 datetime2-> 42
set @v_wk8 = ( case
when @i_wk3 = 56 then 'int'
when @i_wk3 = 127 then 'bigint'
when @i_wk3 = 108 then 'numeric('+cast(@i_wk5 as varchar)+','+cast(@i_wk6 as varchar)+')'
when @i_wk3 = 106 then 'decimal('+cast(@i_wk5 as varchar)+','+cast(@i_wk6 as varchar)+')'
when @i_wk3 = 175 then 'char('+cast(@i_wk4 as varchar)+')'
when @i_wk3 = 167 then 'varchar('+cast(@i_wk4 as varchar)+')'
when @i_wk3 = 239 then 'nchar('+cast(@i_wk4 as varchar)+')'
when @i_wk3 = 231 then 'nvarchar('+cast(@i_wk4 as varchar)+')'
when @i_wk3 = 40 then 'date'
when @i_wk3 = 42 then 'datetime2'
else 'unsupported data type found'
end );
print ' { "col_name": "'+@v_wk1+'", "col_type": "'+@v_wk8+'", "not_null": "'+@v_wk6+'", "col_desc": "'+@v_wk2+'" }'+@v_wk5;
fetch next from cur2 into @i_wk2, @v_wk1, @i_wk3, @i_wk4, @i_wk5, @i_wk6, @i_wk7, @v_wk2;
end
close cur2;
deallocate cur2;
print ' ],';
-- パーティション情報
set @i_partition_count = ( select count(*)
from sys.indexes i
inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
inner join sys.partition_functions pf on pf.function_id = ps.function_id
inner join sys.partition_range_values rv on pf.function_id = rv.function_id
where i.object_id in ( select object_id from sys.tables
where name = @p_table_name
and schema_name(schema_id) = @p_schema_name )
and i.index_id in (0,1) );
if @i_partition_count = 0
print ' "partition": {},';
else
begin
-- パーティションタイプとパーティションキー取得
print ' "partition": {';
print ' "partition_type": "range",';
set @v_partition_key = ( select c.name
from sys.tables t
inner join sys.indexes i
on t.object_id = i.object_id
and i.type <= 1
inner join sys.partition_schemes ps
on ps.data_space_id = i.data_space_id
inner join sys.index_columns ic
on ic.object_id = i.object_id
and ic.index_id = i.index_id
and ic.partition_ordinal >= 1
inner join sys.columns c
on ic.object_id = c.object_id
and ic.column_id = c.column_id
where t.name = @p_table_name
and schema_name(t.schema_id) = @p_schema_name );
print ' "partition_col": "'+@v_partition_key+'",';
print ' "partition_defs" : [';
-- パーティション定義情報
declare cur3 cursor for select "'"+cast(rv.value as varchar)+"'"
from sys.indexes i
inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
inner join sys.partition_functions pf on pf.function_id = ps.function_id
inner join sys.partition_range_values rv on pf.function_id = rv.function_id
where i.object_id in ( select object_id from sys.tables
where name = @p_table_name
and schema_name(schema_id) = @p_schema_name )
and i.index_id in (0,1)
order by rv.boundary_id;
set @i_wk1 = 0
open cur3;
fetch next from cur3 into @v_wk1;
while @@fetch_status = 0
begin
set @i_wk1 = @i_wk1 + 1;
if @i_wk1 != @i_partition_count
set @v_wk2 = ',';
else
set @v_wk2 = '';
print ' { "partition_limit": "'+@v_wk1+'" }'+@v_wk2;
fetch next from cur3 into @v_wk1;
end
close cur3;
deallocate cur3;
print ' ]';
print ' },';
end;
-- 主キー情報
set @i_primary_count = ( select count(*)
from sys.indexes
where object_id in ( select object_id from sys.tables
where name = @p_table_name
and schema_name(schema_id) = @p_schema_name )
and is_primary_key = 1
and index_id > 0 ) ;
if @i_primary_count = 0
print ' "primary": {},';
else
begin
declare cur4 cursor for select t1.name index_name
, string_agg( col_name(t2.object_id,t2.column_id) ,'","') within group ( order by t2.key_ordinal) index_column
from sys.indexes t1
inner join sys.index_columns t2
on t1.object_id = t2.object_id
and t1.index_id = t2.index_id
where t1.object_id in ( select object_id from sys.tables
where name = @p_table_name
and schema_name(schema_id) = @p_schema_name )
and t1.is_primary_key = 1
and t1.index_id > 0
group by t1.name
;
open cur4;
fetch next from cur4 into @v_wk1,@v_wk2;
while @@fetch_status = 0
begin
print ' "primary": { "constraint_name": "'+@v_wk1+'", "index_cols": ["'+@v_wk2+'"] },';
fetch next from cur4 into @v_wk1,@v_wk2;
end
close cur4;
deallocate cur4;
end;
-- インデックス情報
set @i_index_count = (select count(*)
from sys.indexes t1
where t1.object_id in ( select object_id from sys.tables
where name = @p_table_name
and schema_name(schema_id) = @p_schema_name )
and t1.is_primary_key != 1
and t1.index_id > 0
);
if @i_index_count = 0
print ' "indexes": []';
else
begin
print ' "indexes": [';
declare cur5 cursor for select t1.index_id
, t1.name
, t1.is_unique
, string_agg( col_name(t2.object_id,t2.column_id) ,'","') within group ( order by t2.key_ordinal) index_column
from sys.indexes t1
inner join sys.index_columns t2
on t1.object_id = t2.object_id
and t1.index_id = t2.index_id
where t1.object_id in ( select object_id from sys.tables
where name = @p_table_name
and schema_name(schema_id) = @p_schema_name )
and t1.is_primary_key != 1
and t1.index_id > 0
group by t1.index_id, t1.name, t1.is_unique
order by t1.index_id, t1.name, t1.is_unique
;
set @i_wk1 = 0
open cur5;
fetch next from cur5 into @i_wk2, @v_wk1, @i_wk3, @v_wk2;
while @@fetch_status = 0
begin
set @i_wk1 = @i_wk1 + 1;
if @i_wk1 != @i_index_count
set @v_wk3 = ',';
else
set @v_wk3 = '';
if @i_wk3 = 1
set @v_wk4 = 'true';
else
set @v_wk4 = 'false';
print ' { "index_name": "'+@v_wk1+'", "index_cols": ["'+@v_wk2+'"] ,"unique": "'+@v_wk4+'"}'+@v_wk3;
fetch next from cur5 into @i_wk2, @v_wk1, @i_wk3, @v_wk2;
end
close cur5;
deallocate cur5;
print ' ]';
end;
print '}';
end
go
exec ddl2json;
go
exec ddl2json 'dbo', 'tab1';
go
exec ddl2json 'dbo','tab2';
go
SQLCMDPASSWORD='password' sqlcmd -U sa -S 192.168.137.62 -d test -W <<EOF > tab1.json
exec ddl2json 'dbo', 'tab1';
go
EOF
