[PostgreSQL] Table / Row 정보조회 SQL

DB의 TABLE 목록(Table의 이름)을 출력한다.

 

 

Table의 열을 검색한다.

SELECT * 
  FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_CATALOG = '데이터베이스명' 
       AND TABLE_NAME = '테이블명' 
 ORDER BY ORDINAL_POSITION;

 

 

Table의 기본키 값 조회

SELECT CC.COLUMN_NAME AS COLUMN_NAME 
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC 
 WHERE TC.TABLE_CATALOG = '데이터베이스명' 
   AND TC.TABLE_NAME = '테이블명' 
   AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
   AND TC.TABLE_CATALOG = CC.TABLE_CATALOG 
   AND TC.TABLE_SCHEMA = CC.TABLE_SCHEMA 
   AND TC.TABLE_NAME = CC.TABLE_NAME 
   AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME;

 

 

 

테이블의 COMMENT(설명) 조회

SELECT PS.RELNAME    AS TABLE_NAME,
       PD.DESCRIPTION AS TABLE_COMMENT
  FROM PG_STAT_USER_TABLES PS,
       PG_DESCRIPTION      PD
 WHERE PS.RELID   = PD.OBJOID
   AND PD.OBJSUBID  = 0;

 

 

 

 

 

테이블 열(ROW/항복) COMMENT(설명) 조회

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