Skip to content

Table/Column Definition#

Overview#

This is a query required when creating user table and column definitions.

Table definition#

The table owner, table name, number of records, table creation date, and last DDL execution date information can be checked.

The number of records can be checked from Altibase 6.3.1, which added the performance view related to statistical information.

There are differences in the query statement for each version, so refer to the statement for each version.

Altibase 6.3.1 or later

-- USER_NAME  : Table owner
-- TABLE_NAME : Table name
-- RECORD_CNT : Number of records. Statistical information must be collected from this information in order to see accurate information. '-' means a table that has never collected statistical information.
-- CREATED_DATE : Date of table creation
-- LAST_DDL_DATE : Date of last DDL execution

SELECT U.USER_NAME 'USER_NAME'
     , T.TABLE_NAME 'TABLE_NAME'
     , DECODE(D.NUM_ROW, NULL, '-', D.NUM_ROW) 'RECORD_CNT'              -- Number of records,'-' is a table that has never collected statistical information
     , TO_CHAR(T.CREATED, 'YYYY-MM-DD HH:MI:SS') 'CREATED_DATE'          -- Date of table creation
     , TO_CHAR(T.LAST_DDL_TIME, 'YYYY-MM-DD HH:MI:SS') 'LAST_DDL_DATE'   -- Date of last DDL execution
  FROM SYSTEM_.SYS_USERS_ U
     , SYSTEM_.SYS_TABLES_ T
       LEFT OUTER JOIN V$DBMS_STATS D ON D.TARGET_ID = T.TABLE_OID AND D.TYPE = 'T'
 WHERE U.USER_ID NOT IN (0, 1)  -- System user PUBLIC, except SYSTEM_
   AND T.TABLE_TYPE = 'T'  -- Table only
   AND U.USER_ID = T.USER_ID
;

Altibase 5.3.3 / 5.5.1 / 6.1.1

-- USER_NAME  : Table owner
-- TABLE_NAME : Table name
-- CREATED_DATE : Date of table creation
-- LAST_DDL_DATE : Date of last DDL execution

SELECT U.USER_NAME 'USER_NAME'
     , T.TABLE_NAME 'TABLE_NAME'
     , TO_CHAR(T.CREATED, 'YYYY-MM-DD HH:MI:SS') 'CREATED_DATE'          -- Date of table creation
     , TO_CHAR(T.LAST_DDL_TIME, 'YYYY-MM-DD HH:MI:SS') 'LAST_DDL_DATE'   -- Date of last DDL execution
  FROM SYSTEM_.SYS_USERS_ U
     , SYSTEM_.SYS_TABLES_ T
 WHERE U.USER_ID NOT IN (0, 1)  -- System user PUBLIC, except SYSTEM_
   AND T.TABLE_TYPE = 'T'  -- Table only
   AND U.USER_ID = T.USER_ID
;

Altibase 4.3.9

-- USER_NAME  : Table owner
-- TABLE_NAME : Table name

SELECT U.USER_NAME 'USER_NAME'
     , T.TABLE_NAME 'TABLE_NAME'
  FROM SYSTEM_.SYS_USERS_ U
     , SYSTEM_.SYS_TABLES_ T
 WHERE U.USER_ID NOT IN (0, 1)  -- System user PUBLIC, except SYSTEM_
   AND T.TABLE_TYPE = 'T'  -- Table only
   AND U.USER_ID = T.USER_ID
;

Column definition#

The column name, data type, size, NOT NULL, constraint information, and column order information can be checked.

There are differences in the query statement for each version, so refer to the statement for each version.

Altibase 6.3.1 or later

-- 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   
;

Altibase 4.3.9 / 5.3.3 / 5.5.1 / 6.1.1

-- USER_NAME : Name owner 
-- TABLE_NAME : Table name
-- COLUMN_NAME : Column name
-- DATA_TYPE : Data type
-- COLUMN_SIZE : Column size
-- CONST_TYPE : Constraint type
-- 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    
     , 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   
;