SYS_COLUMNS

Information on columns in base tables and views of the database.

Column name

Data type

Contents

TABLE_SCHEMA

CHAR (31)

Name of the schema to which the table
belongs

TABLE_NAME

CHAR (31)

Name of the base table or view

COLUMN_NAME

CHAR (31)

Column name

ORDINAL_POSITION

SMALLINT

Sequence number of the column in the
table

COLUMN_DEFAULT

VARCHAR (256)

For base tables only:

Default value,

as specified in the column definition
(e.g. CHAR literal in single quotes) if
the current authorization identifier
owns the schema

TRUNCATED

if representation of the default value
comprises more than 256 characters
and the current authorization
identifier owns the schema. The
default value cannot be displayed.

NULL value in all other cases

IS_NULLABLE

VARCHAR (3)

NO	Column cannot accept NULL values under any circumstances
YES	else

DATA_TYPE

VARCHAR (24)

Data type of the column:
CHARACTER
CHARACTER VARYING
NATIONAL CHAR
NATIONAL CHAR VARYING
REAL
DOUBLE PRECISION
FLOAT
INTEGER
SMALLINT
NUMERIC
DECIMAL
DATE
TIME
TIMESTAMP
OLDEST

CHARACTER
_MAXIMUM_LENGTH

SMALLINT

Max. length of the column in code units

if the data type is CHARACTER,
CHARACTER VARYING, NATIONAL
CHAR, NATIONAL CHAR VARYING
or OLDEST

NULL value in all other cases

NUMERIC_PRECISION

SMALLINT

Total number of significant digits

for numeric data types

NULL value in all other cases

NUMERIC_PRECISION
_RADIX

SMALLINT

Radix

for numeric data types

NULL value in all other cases

NUMERIC_SCALE

SMALLINT

Number of digits right of the decimal point

for exact numeric data types

NULL value in all other cases

DATETIME_PRECISION

SMALLINT

Number of digits right of the decimal point

for the data types TIME and
TIMESTAMP

NULL value in all other cases

The columns OLDEST_DESCRIPTOR* are assigned a value if DATA_TYPE is OLDEST:

OLDEST_DESCRIPTOR1

CHAR (1)

Y left-aligned

N not left-aligned

NULL value if DATATYPE is not OLDEST

OLDEST_DESCRIPTOR2

CHAR (1)

Y Fill character

N No fill character

NULL value if DATATYPE is not OLDEST

OLDEST_DESCRIPTOR3

CHAR (1)

Y Null (0) permitted as value

N Null (0) not permitted

NULL value if DATATYPE is not OLDEST

OLDEST_DESCRIPTOR4

CHAR (1)

Y Value has arithmetic result

N Value does not have arithmetic result

NULL value if DATATYPE is not OLDEST

COLUMN_DESCRIPTOR1

CHAR (1)

Y	Base table column has exactly one single-column index and is not included in a compound index
N	else

COLUMN_DESCRIPTOR2

CHAR (1)

Y	Base table column has exactly one compound index and no single column index
N	else

COLUMN_DESCRIPTOR3

CHAR (1)

Y Base table column has more than one index

N else

COLUMN_DESCRIPTOR4

CHAR (1)

Y Base table column has a CALL DML default value

N else

COLUMN_DESCRIPTOR5

CHAR (1)

Y Base table column is a multiple column

N else

PK_DISTANCE

SMALLINT

Distance of the column to the start of the
primary key

NULL value if column is not in the primary
key or is not a base table column

SESAM_SAN

CHAR (3)

Symbolic attribute name of the column

NULL value if the column is not defined in
the base table or SQL table

SESAM_BAN

CHAR (2)

Binary attribute name of the column

NULL value if the column is not defined in
the base table

SESAM_DEFAULT

CHAR (2)

CALL DML default (with sign if numeric
data type)

NULL value if the column is not defined in
the base table or SQL table

FIRST_OCCURRENCE

SMALLINT

First possible occurrence of a multiple
column (for base table = 1)

NULL value if the column is not multiple

LAST_OCCURRENCE

SMALLINT

Last possible occurrence of a multiple
column

NULL value if the column is not multiple

Table 111: SYS_COLUMNS view of the SYS_INFO_SCHEMA