Altibase Table 정보 조회 Query

가끔 유용하게 쓰일 때가 있다. ^^

 

SELECT A.USER_NAME AS 'USER_NAME',
       B.TABLE_NAME AS 'TABLE_NAME',
       C.COLUMN_ORDER + 1 AS 'NO',
       C.COLUMN_NAME AS 'COLUMN_NAME',
       CASE C.DATA_TYPE
         WHEN '1' THEN 'CHAR(' || C.PRECISION || ')'
         WHEN '2' THEN 'NUMERIC(' || C.PRECISION || NVL2(C.SCALE, ',' || C.SCALE, NULL) || ')'
         WHEN '4' THEN 'INTEGER(' || C.PRECISION || ')'
         WHEN '8' THEN 'DOUBLE'
         WHEN '9' THEN 'DATETIME'
         WHEN '12' THEN 'VARCHAR(' || C.PRECISION || ')'
       END AS 'TYPE',
       DECODE(C.IS_NULLABLE, 'T', NULL, 'F', 'Y') AS 'IS NOT NULL',
       C.DEFAULT_VAL AS 'DEFAULT',
       DECODE(E.IS_UNIQUE, 'T', 'Y', 'F', 'N') AS 'IS_UNIQUE',
       CASE
         WHEN D.USER_ID IS NOT NULL THEN E.INDEX_NAME
         ELSE NULL
       END AS 'REMK'
  FROM SYSTEM_.SYS_USERS_ A INNER JOIN SYSTEM_.SYS_TABLES_ B ON A.USER_ID = B.USER_ID
   AND A.USER_NAME = UPPER('SKTELINK') /*스키마 이름 입력 또는 계정 이름 입력.*/
  INNER JOIN SYSTEM_.SYS_COLUMNS_ C ON B.USER_ID = C.USER_ID
   AND B.TABLE_ID = C.TABLE_ID
   AND B.TABLE_TYPE = 'T'
   AND UPPER(B.TABLE_NAME) = UPPER('tableName') /*테이블 명을 입력합니다. */
  LEFT OUTER JOIN SYSTEM_.SYS_INDEX_COLUMNS_ D ON C.USER_ID = D.USER_ID
   AND C.TABLE_ID = D.TABLE_ID
   AND C.COLUMN_ID = D.COLUMN_ID LEFT OUTER JOIN SYSTEM_.SYS_INDICES_ E ON D.USER_ID = E.USER_ID
   AND D.TABLE_ID = E.TABLE_ID
   AND D.INDEX_ID = E.INDEX_ID
 WHERE 0 =
       CASE
         WHEN INSTR(B.TABLE_NAME, 'BACK') != 0 THEN 1
         WHEN INSTR(B.TABLE_NAME, '_BK') != 0 THEN 1
         WHEN INSTR(B.TABLE_NAME, '08') != 0 THEN 1
         WHEN INSTR(B.TABLE_NAME, '09') != 0 THEN 1
         WHEN INSTR(B.TABLE_NAME, 'TEMP') != 0 THEN 1
         WHEN INSTR(B.TABLE_NAME, 'TEST') != 0 THEN 1
         ELSE 0
       END
 ORDER BY A.USER_ID, B.TABLE_NAME, C.COLUMN_ORDER