Page History

-- USER_NAME : Owner name 
-- TABLE_NAME : Table name
-- COLUMN_NAME : Column name
-- DATA_TYPE : Data type
-- COLUMN_SIZE : Column size
-- CONST_TYPE : Constraint type
-- CHECK_CONDITION : Condition of CHECK constraint
-- COLUMN_ORDER : Column order
  
SELECT U.USER_NAME 'USER_NAME' 
     , T.TABLE_NAME 'TABLE_NAME'
     , C.COLUMN_NAME 'COLUMN_NAME'
     , DECODE(C.DATA_TYPE, 1, 'CHAR', 12, 'VARCHAR', -8, 'NCHAR', -9, 'NVARCHAR', 2, 'NUMERIC/DECIMAL', 6, 'FLOAT/NUMBER', 8, 'DOUBLE', 7, 'REAL', -5, 'BIGINT', 4, 'INTEGER', 5, 'SMALLINT', 9, 'DATE', 30, 'BLOB', 40, 'CLOB', 20001, 'BYTE', 20002, 'NIBBLE', -7, 'BIT', -100, 'VARBIT', 10003, 'GEOMETRY') 'DATA_TYPE'
     , DECODE(C.DATA_TYPE, 2, C.PRECISION||'.'||C.SCALE, 6, C.PRECISION||'.'||C.SCALE, C.PRECISION) COLUMN_SIZE
     , DECODE(CONST.CONSTRAINT_TYPE, 0, 'FK', 1, 'NOT NULL', 2, 'UNIQUE', 3, 'PK', 4, 'NULL', 5, 'TIMESTAMP', 6, 'LOCAL UNIQUE', 7, 'CHECK') CONST_TYPE
     , CONST.CHECK_CONDITION     
     , C.COLUMN_ORDER 'COLUMN_ORDER'
  FROM SYSTEM_.SYS_USERS_ U
     , SYSTEM_.SYS_TABLES_ T
     , SYSTEM_.SYS_COLUMNS_ C
       LEFT OUTER JOIN system_.SYS_CONSTRAINT_COLUMNS_ CONST_COL ON CONST_COL.COLUMN_ID = C.COLUMN_ID 
       LEFT OUTER JOIN SYSTEM_.SYS_CONSTRAINTS_ CONST ON CONST.CONSTRAINT_ID = CONST_COL.CONSTRAINT_ID
 WHERE U.USER_NAME NOT IN ('PUBLIC', 'SYSTEM_')
   AND T.TABLE_TYPE = 'T'
   AND U.USER_ID = T.USER_ID
   AND T.TABLE_ID = C.TABLE_ID  
 ORDER BY U.USER_NAME, T.TABLE_NAME, C.COLUMN_ORDER   
;