/****************************************************************************************************************
-- Title : [9.2] 시스템 카탈로그(system catalog) 조회 쿼리
-- Reference : dbrang.tistory.com
-- Key word : system catalog 시스템 카탈로그 관리스크립트 sp_tablespace0 sp_table0 script 관리쿼리
management script 관리 쿼리
****************************************************************************************************************/
-- 파라미터 설정값 조회
=# select name, setting from pg_settings
where name = 'wal_level';-- db 조회
=# select d.datdba "ownr_id", pg_get_userbyid(d.datdba) "ownr_nm"
, d.oid "db_id", d.datname "db_nm"
, pg_size_pretty(pg_database_size(d.datname)) "db_sz"
, dattablespace "bas_tbs_id"
, t.spcname "bas_tbs_nm"
from pg_database d
inner join pg_tablespace t
on d.dattablespace = t.oid;
-- 테이블스페이스 조회
=# select pg_get_userbyid(t.spcowner) "tbs_ownr",t.oid "tbs_id", t.spcname "tbs_nm"
, pg_size_pretty(pg_tablespace_size(t.oid)) "tbs_sz"
, case when pg_tablespace_location(t.oid) = ''
then u.tbs_location
else pg_tablespace_location(t.oid)
end "ts_location"
from pg_tablespace t
left join (select a.setting || '/' || case when b.tbs_nm = 'default'
then 'base' else b.tbs_nm
end "tbs_location"
, 'pg_' || b.tbs_nm "tbs_nm"
from pg_settings a
cross join (select 'default' "tbs_nm" union all
select 'global'
) b
where name = 'data_directory'
) u
on t.spcname = u.tbs_nm
order by t.spcname;
-- 테이블 조회
=# select relowner "ownr_id", pg_get_userbyid(c.relowner) "ownr_nm"
, case c.relkind when 'r' then 'table'
when 'v' then 'view'
when 'i' then 'index'
when 'S' then 'sequence' -- 대문자 S
when 's' then 'special' -- 소문자 s
end "rel_tp"
, n.oid "sch_id", n.nspname "sch_nm"
, c.oid "rel_id", c.relname "rel_nm"
, c.reltablespace
, case when c.relkind in ('r','v','i') and c.reltablespace = 0
then u.bas_tbs_id
when c.relkind in ('r','v','i') and c.reltablespace <> 0
then c.reltablespace
else null
end "tbs_id"
, case when c.relkind in ('r','v','i') and c.reltablespace = 0
then u.bas_tbs_nm
when c.relkind in ('r','v','i') and c.reltablespace <> 0
then t.spcname
else null
end "tbs_nm"
, c.relhasindex "idx_has_yn"
, pg_size_pretty(pg_table_size(c.oid)) "tbl_sz"
, pg_size_pretty(pg_relation_size(c.oid)) "rel_sz"
, ( SELECT n_tup_ins - n_tup_del
FROM pg_stat_all_tables
where relname = c.relname
) "row_cnt"
from pg_class c
left join pg_namespace n
on c.relnamespace = n.oid
left join pg_tablespace t
on c.reltablespace = t.oid
cross join (select a.oid "bas_tbs_id", a.spcname "bas_tbs_nm"
from pg_tablespace a
inner join (select dattablespace
from pg_database
where datname = current_database()
) b
on a.oid = b.dattablespace
) as u /* basis tablespace join */
where n.nspname <> 'pg_catalog'
and n.nspname <> 'information_schema'
and n.nspname !~ '^pg_toast'
and c.relkind = 'r';
-- 컬럼 조회
SELECT c.table_schema || '.' || c.table_name "TABLE"
, c.column_name "COLUMN"
, case when c.data_type = 'character' then 'CHAR(' || c.character_maximum_length || ')'
when c.data_type = 'character varying' then 'VARCHAR(' || c.character_maximum_length || ')'
when c.data_type = 'numeric' then 'NUMERIC(' || c.numeric_precision || case when c.numeric_scale = 0 then ')'
else ',' || c.numeric_scale || ')'
end
else upper(c.data_type)
end "COLUMN_TYPE"
, coalesce(c.character_octet_length, c.numeric_precision/8) "LENGTH_BT"
, case when c.is_nullable = 'YES' then 1 else 0 end "IS_NULL"
, case when u.PK_nm is not null then 1 else 0 end "IS_PRIMARYKEY"
, case when u.FK_nm is not null then 1 else 0 end "IS_FOREIGNYKEY"
, case when c.column_default like '%nextval%' then 1 else 0 end "IS_IDENTITY"
, case when c.column_default not like '%nextval%' and c.column_default is not null
then c.column_default
else null
end "DEFAULT", column_default
, NULL "COMPUTED"
, c.ordinal_position "COLUMN_ORDER"
--, u.*
FROM information_schema.columns c
left join
(
select a.table_schema, a.table_name, a.column_name
, max(case when constraint_type = 'PRIMARY KEY' then constraint_name end) as PK_nm
, max(case when constraint_type = 'FOREIGN KEY' then constraint_name end) as FK_nm
from
(
select tc.table_catalog, tc.table_schema, tc.table_name "table_name", tc.constraint_name, kc.column_name, tc.constraint_type
from information_schema.table_constraints tc
join information_schema.key_column_usage kc
on kc.table_name = tc.table_name and kc.table_schema = tc.table_schema
and tc.constraint_name = kc.constraint_name
where tc.table_catalog = 'tttdb'
and tc.constraint_type in ('PRIMARY KEY', 'FOREIGN KEY')
) a
group by a.table_schema, a.table_name, a.column_name
) u
on c.table_schema = u.table_schema
and c.table_name = u.table_name
and c.column_name = u.column_name
where c.table_schema not in ('pg_catalog', 'information_schema')
order by c.table_schema, c.table_name, c.ordinal_position;