-- Title : [PGS9.2] TABLE & COLUMN Management - ver.dBRang
-- Reference : dbrang.tistory.com
-- Key word : 테이블 컬럼 관리 이름 변경
****************************************************************************************************************/
-- Table Management View
select n.nspname "sch_nm"
, c.relname "rel_nm"
, 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"
, 'table space' "tbs_tp"
, NULL "lob_tp"
, (
SELECT n_tup_ins - n_tup_del
FROM pg_stat_all_tables
where relname = c.relname
AND schemaname = n.nspname
) "row_cnt"
, CASE WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'es'
THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' bytes','') :: int / 1024
WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'kb'
THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' kb','') :: int
WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'mb'
THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' mb','') :: int * 1024
WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'gb'
THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' gb','') :: int * 1024 * 1024
ELSE 999999
END "rsvt_kb"
, CASE WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'es'
THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' bytes','') :: int / 1024
WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'kb'
THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' kb','') :: int
WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'mb'
THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' mb','') :: int * 1024
WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'gb'
THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' gb','') :: int * 1024 * 1024
ELSE 999999
END "data_kb"
, CASE WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'es'
THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' bytes','') :: int / 1024
WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'kb'
THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' kb','') :: int
WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'mb'
THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' mb','') :: int * 1024
WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'gb'
THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' gb','') :: int * 1024 * 1024
ELSE 999999
END
- CASE WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'es'
THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' bytes','') :: int / 1024
WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'kb'
THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' kb','') :: int
WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'mb'
THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' mb','') :: int * 1024
WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'gb'
THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' gb','') :: int * 1024 * 1024
ELSE 999999
END "odx_kb"
, NULL "nuse_kb"
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';
-- Column Management View
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_PK"
, case when u.FK_nm is not null then 1 else 0 end "IS_FK"
, case when c.column_default like '%nextval%' then 1 else 0 end "IS_IDENT"
, case when c.column_default not like '%nextval%' and c.column_default is not null
then c.column_default
else null
end "DFLT"
, NULL "COMP"
, c.ordinal_position "COL_ORD"
FROM
(
select table_catalog, table_schema, table_name, column_name, ordinal_position, column_default, data_type
, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable
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
inner 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.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;