PostgreSQL DB 다양한 스크립트 모음

DDL SQL을 활용해 테이블과 관련된 정보들을 추출하기
(데이터베이스 목록, 테이블 목록, 스키마 목록, 컬럼 목록, 테이블 코멘트, 컬럼 코멘트 조회, 인덱스 등)

1. 데이터 베이스 조회
SELECT datname, * FROM pg_database; -- 전체 데이터베이스 목록 조회
SELECT datname, * FROM pg_database WHERE datistemplate = false; -- 자신이 생성한 데이터베이스 목록만 조회


2. 데이터 베이스의 스키마 조회
select nspname, * from pg_catalog.pg_namespace; -- 전체 스키마 목록 조회



3. 데이터 베이스의 테이블 조회
select * from pg_tables; -- 모든 테이블 조회
select tablename, * from pg_tables where SCHEMANAME = '내가 생성한 스키마'; -- 자신이 생성한 스키마의 테이블만 조회



4. 테이블의 컬럼 목록 조회
select column_name,
   data_type,
   character_maximum_length 
       , *
from information_schema.columns
where table_catalog = '데이터베이스명'
and table_name = '테이블명';



5. 테이블 및 컬럼의 Description 정보 조회
select * from PG_DESCRIPTION 




6. 컬럼의 속성정보 조회
select * from PG_ATTRIBUTE 




7. 테이블 코멘트 목록 조회
SELECT PS.RELNAME    AS TABLE_NAME
      ,PD.DESCRIPTION AS TABLE_COMMENT
FROM PG_STAT_USER_TABLES PS
      ,PG_DESCRIPTION      PD
WHERE PS.RELNAME  = '테이블명'
AND PS.RELID   = PD.OBJOID
AND PD.OBJSUBID  = 0;
8. 컬럼 코멘트 목록 조회
SELECT PS.RELNAME    AS TABLE_NAME
      ,PA.ATTNAME     AS COLUMN_NAME
      ,PD.DESCRIPTION AS COLUMN_COMMENT
FROM PG_STAT_ALL_TABLES PS
    ,PG_DESCRIPTION     PD
    ,PG_ATTRIBUTE       PA
WHERE PS.SCHEMANAME = (SELECT SCHEMANAME
                            FROM PG_STAT_USER_TABLES
                           WHERE RELNAME = '테이블명')
   AND PS.RELNAME  = '테이블명'
   AND PS.RELID   = PD.OBJOID
   AND PD.OBJSUBID <> 0
   AND PD.OBJOID    = PA.ATTRELID
   AND PD.OBJSUBID  = PA.ATTNUM
 ORDER BY PS.RELNAME, PD.OBJSUBID



9. 스키마, 테이블, 테이블 코멘트 한번에 조회
SELECT N.NSPNAME, C.RELNAME, OBJ_DESCRIPTION(C.OID) 
FROM PG_CATALOG.PG_CLASS C INNER JOIN PG_CATALOG.PG_NAMESPACE N ON C.RELNAMESPACE=N.OID WHERE C.RELKIND = 'r'
and a.nspname = '스키마명'

 

10. 스키마, 테이블, 컬럼, 컬럼속성, 컬럼 null여부, 컬럼의 코멘트 조회
select a.table_schema , a.table_name , a.column_name , 
      case when data_type = 'character' then data_type 
            when data_type = 'character varying' then 'varchar('||coalesce  (character_maximum_length,0)||')' 
            when data_type = 'integer' then data_type||'('||numeric_precision||')'
            when data_type = 'bigint' then data_type||'('||numeric_precision||')'
            when data_type = 'numeric' then data_type||'('||coalesce(numeric_precision,0)||','||coalesce(numeric_scale,0)||')'
            when data_type = 'timestamp without time zone' then 'timestamp'
            when data_type = 'text' then 'text'
       else data_type end as data_type_2
      , a.is_nullable 
      , col_description(b.relid, a.ordinal_position) 
from information_schema.columns a  
join pg_stat_user_tables b 
on a.table_schema  = b.schemaname 
and a.table_name = b.relname 
where a.table_schema = '스키마명'
order by a.table_schema , a.table_name , a.ordinal_position ;