[PostgreSQL] 스키마에 따른 테이블(컬럼) 정보확인

1. 스키마의 테이블/컬럼 정보를 한눈에 확인하기 위한 목적
2. 테이블설명 및 컬럼설명의 누락 정보를 체크하여 채우기 위한 용도로 사용

[쿼리]

SELECT tb.schemaname AS 스키마명 ,
tb.relname AS 테이블명 ,
tb_dc.description AS 테이블설명 ,
col.attname AS 컬럼명 ,
col.attnum AS 컬럼순서 ,
col_dc.description AS 컬럼설명 ,
col_type.data_type AS 컬럼데이터타입 ,
col_type.character_maximum_length AS LEN
FROM ( SELECT *
FROM PG_STAT_ALL_TABLES
WHERE 1=1
-- and RELNAME = 'tbs_ext_sale_ctgr_info'
AND schemaname='g1ecl'
)
tb --테이블정보
LEFT JOIN PG_DESCRIPTION tb_dc--테이블설명
ON tb_dc.OBJSUBID = 0
AND tb.RELID = tb_dc.OBJOID
LEFT JOIN PG_ATTRIBUTE col--컬럼정보
ON tb.relid=col.ATTRELID
LEFT JOIN PG_DESCRIPTION col_dc--컬럼설명
ON col_dc.OBJSUBID <> 0
AND tb.RELID = col_dc.OBJOID
AND col_dc.OBJOID = col.ATTRELID
AND col_dc.OBJSUBID = col.ATTNUM
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col_type--컬럼데이터타입
ON col_type.table_schema =tb.schemaname
AND col_type.table_name =tb.relname
AND col_type.column_name =col.attname
AND col_type.ordinal_position=col.attnum
WHERE 1 =1
AND col.attstattarget ='-1'
AND col_dc.description IS NULL
ORDER BY tb.relname