시스템 카탈로그를 참조하여 칼럼 검증, 참조키 검증, 칼럼목록 등 확인하는 SQL 입니다.
-- 스키마를 설정하세요
-- @set schema_name = schema name of cdm
@set schema_name = myDB
-- 설정 스키마 확인
select '${schema_name}';
-- 1. 테이블 칼럼수 1초과 테이블 :: obj_description(oid) 테이블 코멘트 추가 :: 주석은 타이틀과 설명을 |으로 분리 적용
select t.schemaname, t.relname, split_part(obj_description(c.oid), '|', 1) as title, split_part(obj_description(c.oid), '|', 2) as description, t.n_live_tup
from pg_catalog.pg_stat_user_tables t join pg_catalog.pg_class c on (c.relnamespace::regnamespace::text = t.schemaname and c.relname = t.relname and c.relkind = 'r')
where t.schemaname = '${schema_name}'
and t.n_live_tup > 1
order by 3 ;
-- 2. 테이블별 칼럼형식 목록 - table column data type
select i.table_name, i.ordinal_position, i.column_name,
split_part((SELECT col_description(a.attrelid, a.attnum)), '|', 1) AS title,
split_part((SELECT col_description(a.attrelid, a.attnum)), '|', 2) AS description, i.is_nullable, i.data_type, i.udt_name
from information_schema."columns" i join pg_catalog.pg_attribute a on (a.attrelid = i.table_name::regclass::oid and a.attnum = i.ordinal_position)
where i.table_schema = '${schema_name}'
and exists (select 1 from pg_catalog.pg_stat_user_tables u where i.table_name = u.relname and u.schemaname = i.table_schema
and u.n_live_tup > 1 )
order by 1, 2;
-- 3. 테이블별 칼럼형식 - Not Null 칼럼 목록
select i.table_name, i.ordinal_position, i.column_name,
split_part((SELECT col_description(a.attrelid, a.attnum)), '|', 1) AS title,
split_part((SELECT col_description(a.attrelid, a.attnum)), '|', 2) AS description, i.is_nullable, i.data_type, i.udt_name
from information_schema."columns" i join pg_catalog.pg_attribute a on (a.attrelid = i.table_name::regclass::oid and a.attnum = i.ordinal_position)
where i.table_schema = '${schema_name}'
and i.is_nullable = 'NO'
and exists (select 1 from pg_catalog.pg_stat_user_tables a where i.table_name = a.relname and a.schemaname = i.table_schema
and a.n_live_tup > 1 )
order by 1, 2;
-- 4. 테이블별 칼럼형식 - time or date 칼럼 목록
select i.table_name, i.ordinal_position, i.column_name,
split_part((SELECT col_description(a.attrelid, a.attnum)), '|', 1) AS title,
split_part((SELECT col_description(a.attrelid, a.attnum)), '|', 2) AS description, i.is_nullable, i.data_type, i.udt_name
from information_schema."columns" i join pg_catalog.pg_attribute a on (a.attrelid = i.table_name::regclass::oid and a.attnum = i.ordinal_position)
where i.table_schema = '${schema_name}'
and (i.data_type like 'time%' or i.data_type like 'date%')
and exists (select 1 from pg_catalog.pg_stat_user_tables a where i.table_name = a.relname and a.schemaname = i.table_schema
and a.n_live_tup > 1 )
order by 1, 2;
-- 5. 데이터 타입별 칼럼 갯수 1
select i.data_type, i.udt_name, count(1) as totalcnt
from information_schema."columns" i
where i.table_schema = '${schema_name}'
and exists (select 1 from pg_catalog.pg_stat_user_tables a where i.table_name = a.relname and a.schemaname = i.table_schema
and a.n_live_tup > 1 )
group by 1, 2
order by 3;
-- 6. 데이터 타입별 칼럼 갯수 2
select i.data_type, count(1) as total
from information_schema."columns" i
where i.table_schema = '${schema_name}'
and exists (select 1 from pg_catalog.pg_stat_user_tables a where i.table_name = a.relname and a.schemaname = i.table_schema
and a.n_live_tup > 1 )
group by 1
;
-- 7. 총칼럼갯수 239
with col as (
select i.table_name, count(1) as column_count
from information_schema."columns" i where i.table_schema = '${schema_name}'
and exists (select 1 from pg_catalog.pg_stat_user_tables a where i.table_name = a.relname and a.schemaname = i.table_schema
and a.n_live_tup > 1 )
GROUP by i.table_name order by column_count desc
)
select sum(column_count) from col;
-- 8. 테이블별 칼럼갯수 데이터 1개 초과
select i.table_name, count(1) as column_count
from information_schema."columns" i
where i.table_schema = '${schema_name}'
and exists (select 1 from pg_catalog.pg_stat_user_tables a where i.table_name = a.relname and a.schemaname = i.table_schema
and a.n_live_tup > 1 )
GROUP by i.table_name order by column_count desc;
-- 6. 스키마에 사용된 데이터 형식별 칼럼 건수
select i.data_type, count(1) as total
from information_schema."columns" i
where i.table_schema = '${schema_name}'
and exists (select 1 from pg_catalog.pg_stat_user_tables a where i.table_name = a.relname and a.schemaname = i.table_schema
and a.n_live_tup > 1 )
group by 1
order by 2;
-- 9. 키, 참조키 리스트
with pop as (
SELECT
(conrelid::regclass)::varchar AS table_from,
conname,
pg_get_constraintdef(oid) as condef,
r.contype
FROM pg_catalog.pg_constraint r
)
select * from pop where table_from not like '%.%' and table_from != '-' and table_from != 'admin' and table_from not like 'bsct_%'
order by 1
;
-- 10. 테이블별 건수 산정
with tbl as (
select distinct i.table_schema, i.table_name
from information_schema."columns" i
where i.table_schema = '${schema_name}'
and exists (select 1 from pg_catalog.pg_stat_user_tables a where i.table_name = a.relname and a.schemaname = i.table_schema
and a.n_live_tup > 1 )
)
SELECT
table_schema,
table_name,
(xpath('/row/c/text()',
query_to_xml(format('select count(*) AS c from %I.%I', table_schema, table_name),
false,
true,
'')))[1]::text::int AS rows_n
FROM tbl ORDER BY 2
;
-- 11. 테이블별 칼럼형식이 Null이 아닌 데이터 건수 보기 - Not Null 칼럼
with table_n as (
select i.table_schema, i.table_name, i.ordinal_position, i.column_name, i.is_nullable, i.data_type, i.udt_name
from information_schema."columns" i
where i.table_schema = '${schema_name}'
and i.is_nullable = 'NO'
and exists (select 1 from pg_catalog.pg_stat_user_tables a where i.table_name = a.relname and a.schemaname = i.table_schema
and a.n_live_tup > 1 )
order by 1, 2
)
select table_name, column_name,
(xpath('/row/nullcount/text()',
query_to_xml(format('SELECT count(1) as nullcount from %I.%I where %I is null', table_schema, table_name, column_name),false,
true,
'')))[1]::text::int AS rows_null
from table_n
;
-- 12 날짜형식 특정기간 벗어난 건수 구하기
with table_n as (
select i.table_schema, i.table_name, i.ordinal_position, i.column_name, i.is_nullable, i.data_type, i.udt_name
from information_schema."columns" i
where i.table_schema = '${schema_name}'
and (i.data_type like 'time%' or i.data_type like 'date%')
and exists (select 1 from pg_catalog.pg_stat_user_tables a where i.table_name = a.relname and a.schemaname = i.table_schema
and a.n_live_tup > 1 )
order by 1, 2
)
select table_name, column_name,
(xpath('/row/total/text()',
query_to_xml(format('SELECT count(1) as total from %I.%I where %I <= ''1900.01.01'' or %I >= ''2100.01.01''' , table_schema, table_name, column_name, column_name), false,
true,
'')))[1]::text::int AS rows_outrange
from table_n
;
-- 13. 테이블별 칼럼형식 - time or date 칼럼 min max 이상치 날짜 조회
with table_n as (
select i.table_schema, i.table_name, i.ordinal_position, i.column_name, i.is_nullable, i.data_type, i.udt_name
from information_schema."columns" i
where i.table_schema = '${schema_name}'
and (i.data_type like 'time%' or i.data_type like 'date%')
and exists (select 1 from pg_catalog.pg_stat_user_tables a where i.table_name = a.relname and a.schemaname = i.table_schema
and a.n_live_tup > 1 )
order by 1, 2
)
select table_name, column_name,
(xpath('/row/min/text()',
query_to_xml(format('SELECT min(%I) from %I.%I' , column_name, table_schema, table_name), false,
true,
'')))[1]::text::timestamp AS rows_min,
(xpath('/row/max/text()',
query_to_xml(format('SELECT max(%I) from %I.%I' , column_name, table_schema, table_name), false,
true,
'')))[1]::text::timestamp AS rows_max,
(xpath('/row/total/text()',
query_to_xml(format('SELECT count(1) as total from %I.%I where %I <= ''1900.01.01'' or %I >= ''2100.01.01''' , table_schema, table_name, column_name, column_name), false,
true,
'')))[1]::text::int AS rows_outrange
from table_n
;
-- 14. 테이블별 숫자 형식 자료 min max
with table_n as (
select i.table_schema, i.table_name, i.ordinal_position, i.column_name, i.is_nullable, i.data_type, i.udt_name
from information_schema."columns" i
where i.table_schema = '${schema_name}'
and (i.data_type like 'bigint%' or i.data_type like 'int%' or i.data_type like 'numeric%')
and exists (select 1 from pg_catalog.pg_stat_user_tables a where i.table_name = a.relname and a.schemaname = i.table_schema
and a.n_live_tup > 1 )
order by 1, 2
)
select table_name, column_name, is_nullable,
format('SELECT min(%I) from %I.%I' , column_name, table_schema, table_name) as minSQLsyntax,
format('SELECT max(%I) from %I.%I' , column_name, table_schema, table_name) as maxSQLsyntax,
(xpath('/row/min/text()',
query_to_xml(format('SELECT min(%I) from %I.%I' , column_name, table_schema, table_name), false,
true,
'')))[1]::text::numeric AS rows_min,
(xpath('/row/max/text()',
query_to_xml(format('SELECT max(%I) from %I.%I' , column_name, table_schema, table_name), false,
true,
'')))[1]::text::numeric AS rows_max
from table_n
;
-- 15. 테이블별 참조키 검증
with pop as (
SELECT
(conrelid::regclass)::varchar AS table_from,
conname,
pg_get_constraintdef(oid) as condef,
r.contype
FROM pg_catalog.pg_constraint r
where r.contype = 'f'
)
select
table_from, conname,
condef,
(xpath('/row/fcount/text()',
query_to_xml(format('select count(1) as fcount from %I t where not exists (select 1 from %I a where a.%I = t.%I) and t.%I is not null', table_from,
split_part(split_part(condef, 'REFERENCES ', 2), '(', 1),
left(split_part(condef, '(', 3), -1),
split_part(split_part(condef, 'FOREIGN KEY (', 2), ')', 1),
split_part(split_part(condef, 'FOREIGN KEY (', 2), ')', 1)), false,
true,
'')))[1]::text::int AS fcount
from pop
where table_from not like '%.%' and table_from != '-' and table_from != 'admin' and table_from not like 'bsct_%'
order by 1
;