[개발관련] DBeaver 테이블 명세 추출 쿼리

select t1.table_name, t2.column_name,t2.column_key, 
		case 
			when data_type='varchar' then concat('varchar(',character_maximum_length,')')
			when data_type='int' or data_type='bigint' then concat(data_type,'(',NUMERIC_PRECISION+1,')') 
		else data_type
		end as type,
t2.column_default,t2.column_comment,t2.is_nullable
from (select * from information_schema.tables where table_schema = 'db명') t1,
     (select * from information_Schema.columns where table_schema = 'db명') t2
where t1.table_name = t2.table_name;

 

 

 

테이블 명세 및 테이블 목록 View를 생성해 놓고 테이블 명세서를 추출

-- itpk_ims.v_table_def_list source

create or replace
algorithm = UNDEFINED view `v_table_def_list` as
select
    `a`.`TABLE_SCHEMA` as `DB명칭`,
    `a`.`TABLE_NAME` as `테이블명(영문)`,
    `a`.`TABLE_COMMENT` as `테이블명(국문)`,
    `b`.`ORDINAL_POSITION` as `컬럼순서`,
    `b`.`COLUMN_NAME` as `컬럼명(영문)`,
    `b`.`COLUMN_COMMENT` as `컬럼명(국문)`,
    `b`.`COLUMN_TYPE` as `컬럼형`,
    `b`.`COLUMN_KEY` as `KEY여부`,
    `b`.`IS_NULLABLE` as `NULL허용여부`,
    `b`.`COLUMN_DEFAULT` as `DEFAULT값`
from
    (`information_schema`.`tables` `a`
join `information_schema`.`columns` `b` on
    (`a`.`TABLE_SCHEMA` = `b`.`TABLE_SCHEMA`
        and `a`.`TABLE_NAME` = `b`.`TABLE_NAME`))
where
    `a`.`TABLE_SCHEMA` = 'DB명'
    and `a`.`TABLE_TYPE` = 'BASE TABLE'
order by
    `a`.`TABLE_NAME`,
    `b`.`ORDINAL_POSITION`;
    
   
   
   -- itpark.v_table_list source

create or replace
algorithm = UNDEFINED view `v_table_list` as
select
    `information_schema`.`tables`.`TABLE_SCHEMA` as `DB명칭`,
    `information_schema`.`tables`.`TABLE_NAME` as `테이블명(영문)`,
    `information_schema`.`tables`.`TABLE_COMMENT` as `테이블명(국문)`
from
    `information_schema`.`tables`
where
    `information_schema`.`tables`.`TABLE_SCHEMA` = 'DB명'
    and `information_schema`.`tables`.`TABLE_TYPE` = 'BASE TABLE'
order by
    `information_schema`.`tables`.`TABLE_NAME`;