Cloudscape System Tables
Page 4 of 17

SYSCOLUMNS

Describes the columns within:

  • all tables in the current database
  • parameters in the current database's publications
  • parameters in the current database's stored prepared statements

    Column Name

    Type

    Length

    Nullable

    Contents

    REFERENCEID

    CHAR

    36

    false

    Identifier for table, publication, or stored prepared statement (join with SYSTABLES.TABLEID, SYSSTATEMENTS. STMTID, or SYSPUBS.PUBLICATIONID)

    COLUMNNAME

    CHAR

    128

    false

    column or parameter name; null for stored prepared statements

    COLUMNNUMBER

    INT

    4

    false

    the position of the column within the table or the position of the parameter within the publication or stored prepared statement

    COLUMNDATATYPE

    SERIALIZE
    (COM.cloudscape.
    types.TypeDescriptor
    )


    false

    system type that describes precision, length, scale, nullability, type name, and storage type of data

    COLUMNDEFAULT

    SERIALIZE
    (java.io.Serializable)


    true

    for tables, describes default value of the column. The toString() method on the object stored in the table returns the text of the default value as specified in the CREATE TABLE or ALTER TABLE statement.

    for publication parameters, defines the default value of the parameter at the source, and the actual value of the parameter at the target

    for stored prepared statements, defines a sample value for the parameter used in a CREATE STATEMENT or ALTER STATEMENT RECOMPILE statement for optimization purposes

    COLUMNDEFAULTID

    CHAR

    36

    false

    unique identifier for the default value

    AUTOINCREMENTVALUE

    LONGINT


    true

    what the next value for column will be, if it has a default value of AUTOINCREMENT

    AUTOINCREMENTSTART

    LONGINT


    true

    initial value of column (if specified), if it has a default value of AUTOINCREMENT

    AUTOINCREMENTINC

    LONGINT


    true

    amount column value is automatically incremented (if specified), if the column has a default value of AUTOINCREMENT

Indexes

  • SYSCOLUMNS_INDEX1 unique BTREE index on (REFERENCEID, COLUMNNAME)
  • SYSCOLUMNS_INDEX2 unique BTREE index on COLUMNDEFAULTID

Example Query

The following query returns some information about all the columns in the Countries table:

SELECT c.COLUMNNAME, c.COLUMNNUMBER,
    c.COLUMNDATATYPE.getSQLstring(),
    c.COLUMNDATATYPE.isNullable()
FROM SYS.SYSCOLUMNS c, SYS.SYSTABLES t
WHERE c.REFERENCEID = t.TABLEID
AND t.TABLENAME = 'COUNTRIES'

-- see all columns that have default values
SELECT c.COLUMNNAME, COLUMNDEFAULT.toString(), TABLENAME
FROM SYS.SYSCOLUMNS c, SYS.SYSTABLES s
WHERE c.REFERENCEID=s.TABLEID
AND COLUMNDEFAULT IS NOT NULL

-- see the default values for a stored prepared
-- statement
SELECT STMTNAME, COLUMNDEFAULT.toString()
FROM SYS.SYSCOLUMNS c, SYS.SYSSTATEMENTS s
WHERE c.REFERENCEID = s.STMTID
AND STMTNAME = 'GETFLIGHTINFO'

-- find out information about columns that have
-- autoincrement defaults
SELECT columnname, autoincrementvalue,
    autoincrementstart, autoincrementinc
FROM SYS.SYSCOLUMNS
WHERE autoincrementstart IS NOT NULL