PostgreSQL 테이블 상세 정보 조회 스크립트

PostgreSQL 테이블 상세 정보 조회 스크립트

---

SELECT col.table_schema AS schema_name, col.table_name, col.column_name, 

col.character_maximum_length, col.is_nullable, col.numeric_precision, 

col.numeric_scale, col.datptime_precision, col.ordinal_position, b.atttypmod, b.attndims, col.data_type AS col_type,

pt.typelem, pt.typlen, pt.typtype, nbt.nspname AS elem_schema, bt.typname AS elem_name, b.atttypid, col.udt_schema, col.udt_name, 

col.domain_catalog, col.domain_schema, col.domain_name, col_description(c.oid, col.ordinal_position) AS comment,

col.column_default AS col_default, b.attacl, colnsp.nspname AS collation_schema_name, 

coll.collname, c.relkind, b.attfdwoptions AS foreign_options 

FROM information_schema.columns AS col 

LEFT JOIN pg_namespace ns ON ns.nspname = col.table_schema 

LEFT JOIN pg_class c ON col.table_name = c.relname 

AND c.relnamespace = ns.oid 

LEFT JOIN pg_attrdef a 

ON c.oid = a.adrelid 

AND col.ordinal_position = a.adnum 

LEFT JOIN pg_attribute b 

ON b.attrelid = c.oid 

AND b.attname = col.column_name 

LEFT JOIN pg_type pt 

ON pt.oid = b.atttypid 

LEFT JOIN pg_collation coll 

ON coll.oid = b.attcollation 

LEFT JOIN pg_namespace colnsp 

ON coll.collnamespace = colnsp.oid 

LEFT JOIN pg_type bt 

ON pt.typelem = bt.oid 

LEFT JOIN pg_namespace nbt 

ON bt.typnamespace = nbt.oid 

 WHERE col.table_schema = 'cbsadm' 

 ORDER BY col.table_schema, col.table_name, col.ordinal_position