Oracle テーブル定義を取得するSQL

Oracleのテーブル定義を取得するSQLメモ。

テーブル一覧

SELECT T.TABLE_NAME
     , C.COMMENTS
     , O.CREATED
     , O.LAST_DDL_TIME
  FROM ALL_TABLES T
  LEFT JOIN ALL_TAB_COMMENTS C
    ON T.OWNER=C.OWNER AND T.TABLE_NAME=C.TABLE_NAME
  LEFT JOIN ALL_OBJECTS O
    ON T.OWNER=O.OWNER AND T.TABLE_NAME=O.OBJECT_NAME
 WHERE T.OWNER=user 
 ORDER BY T.TABLE_NAME

テーブル定義1

SELECT T.COLUMN_ID
     , P.CONSTRAINT_TYPE PK
     , T.COLUMN_NAME
     , T.DATA_TYPE
     , CASE 
         WHEN T.DATA_TYPE IN ('VARCHAR2','NVARCHAR2','CHAR','NCHAR') THEN TO_CHAR(T.CHAR_LENGTH) || DECODE(T.CHAR_USED,'C',' CHAR',' BYTE')
         WHEN T.DATA_TYPE='NUMBER' THEN TO_CHAR(T.DATA_PRECISION) || ',' ||TO_CHAR(T.DATA_SCALE)
         WHEN T.DATA_TYPE='RAW' THEN TO_CHAR(T.DATA_LENGTH)
         ELSE NULL
       END  DATALENGTH 
     , T.DATA_DEFAULT
     , T.NULLABLE 
     , M.COMMENTS 
  FROM ALL_TAB_COLUMNS T
  LEFT JOIN ALL_COL_COMMENTS M
    ON T.OWNER=M.OWNER 
   AND T.TABLE_NAME=M.TABLE_NAME 
   AND T.COLUMN_NAME=M.COLUMN_NAME
  LEFT JOIN (SELECT S.OWNER
                  , S.TABLE_NAME
                  , COL.COLUMN_NAME
                  , S.CONSTRAINT_TYPE  
              FROM ALL_CONSTRAINTS S
             INNER JOIN ALL_CONS_COLUMNS COL
                ON S.OWNER=COL.OWNER 
               AND S.TABLE_NAME=COL.TABLE_NAME  
               AND S.CONSTRAINT_NAME=COL.CONSTRAINT_NAME 
               AND S.CONSTRAINT_TYPE='P') P
    ON T.OWNER=P.OWNER 
   AND T.TABLE_NAME=P.TABLE_NAME 
   AND T.COLUMN_NAME=P.COLUMN_NAME
 WHERE  T.OWNER= USER --current userの情報
   AND  T.TABLE_NAME=:table_name
 ORDER BY T.COLUMN_ID

結果例：

テーブル定義2 (UNIQUE制約 4つまでの情報取得)

WITH UK_lIST AS (
SELECT  * 
  FROM (
       SELECT S.OWNER
            , S.TABLE_NAME
            , COL.COLUMN_NAME
            , S.CONSTRAINT_TYPE  
            , S.CONSTRAINT_NAME
            , DENSE_RANK() OVER(ORDER BY S.CONSTRAINT_NAME) R
        FROM ALL_CONSTRAINTS S
       INNER JOIN ALL_CONS_COLUMNS COL
          ON S.OWNER = USER
         AND S.TABLE_NAME = :table_name 
         AND S.OWNER=COL.OWNER 
         AND S.TABLE_NAME=COL.TABLE_NAME  
         AND S.CONSTRAINT_NAME=COL.CONSTRAINT_NAME 
         AND S.CONSTRAINT_TYPE='U'
        )
       PIVOT (MAX(CONSTRAINT_NAME) FOR R IN (1 as UK1, 2 AS UK2,3 AS UK3,4 AS UK4))
       )
SELECT T.COLUMN_ID
     , P.CONSTRAINT_TYPE PK
     , T.COLUMN_NAME
     , T.DATA_TYPE
     , CASE 
         WHEN T.DATA_TYPE IN ('VARCHAR2','NVARCHAR2','CHAR','NCHAR') THEN TO_CHAR(T.CHAR_LENGTH) || DECODE(T.CHAR_USED,'C',' CHAR',' BYTE')
         WHEN T.DATA_TYPE='NUMBER' THEN TO_CHAR(T.DATA_PRECISION) || ',' ||TO_CHAR(T.DATA_SCALE)
         WHEN T.DATA_TYPE='RAW' THEN TO_CHAR(T.DATA_LENGTH)
         ELSE NULL
       END  DATALENGTH 
     , T.DATA_DEFAULT
     , T.NULLABLE 
     , M.COMMENTS
     , U.UK1
     , U.UK2
     , U.UK3
     , U.UK4
  FROM ALL_TAB_COLUMNS T
  LEFT JOIN ALL_COL_COMMENTS M
    ON T.OWNER=M.OWNER 
   AND T.TABLE_NAME=M.TABLE_NAME 
   AND T.COLUMN_NAME=M.COLUMN_NAME
  LEFT JOIN (SELECT S.OWNER
                  , S.TABLE_NAME
                  , COL.COLUMN_NAME
                  , S.CONSTRAINT_TYPE  
              FROM ALL_CONSTRAINTS S
             INNER JOIN ALL_CONS_COLUMNS COL
                ON S.OWNER=COL.OWNER 
               AND S.TABLE_NAME=COL.TABLE_NAME  
               AND S.CONSTRAINT_NAME=COL.CONSTRAINT_NAME 
               AND S.CONSTRAINT_TYPE='P') P
    ON T.OWNER=P.OWNER 
   AND T.TABLE_NAME=P.TABLE_NAME 
   AND T.COLUMN_NAME=P.COLUMN_NAME
  LEFT JOIN UK_lIST U
    ON T.OWNER=U.OWNER 
   AND T.TABLE_NAME=U.TABLE_NAME 
   AND T.COLUMN_NAME=U.COLUMN_NAME
 WHERE  T.OWNER= USER --current user
   AND  T.TABLE_NAME=:table_name
 ORDER BY T.COLUMN_ID

結果例：