반응형
/****************************************************************************************************************
-- Title : [PGS9.2] Information View of Table & Columns
-- Title : [PGS9.2] Information View of Table & Columns
-- Reference : dbrang.tisotry.com
-- Key word : table information column information
****************************************************************************************************************/
-- Key word : table information column information
****************************************************************************************************************/
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;
반응형