syscolumns

id

int

ID of table to which this column belongs, or of procedure with which this parameter is associated.

number

smallint

Sub-procedure number when the procedure is grouped (0 for nonprocedure entries).

colid

smallint

Column ID.

status

tinyint

• Bits 0–2 (values 1, 2, and 4) – indicate bit positioning if the column uses the bit datatype. If the column uses the text/image datatype, bits 0 and 1 indicate replication status as follows:

  • 01 = always replicate

  • 10 = replicate only if changed

  • 00 = never replicate

• Bit 3 (value 8) – indicates whether NULL values are legal in this column.

• Bit 4 (value 16) – indicates whether more than one check constraint exists for the column.

• Bits 5 and 6 – are used internally.

• Bit 7 (value 128) – indicates an identity column.

type

tinyint

Physical storage type; copied from systypes.

length

int

Physical length of data; copied from systypes or supplied by user.

offset

smallint

Offset into the row where this column appears; if negative, this is a variable-length column.

usertype

smallint

User type ID; copied from systypes.

cdefault

int

ID of the procedure that generates default value for this column.

domain

int

Constraint ID of the first rule or check constraint for this column.

name

varchar(255) not null

Column name

printfmt

varchar(255) null

Reserved

prec

tinyint null

Number of significant digits, if the column uses the numeric datatype.

scale

tinyint null

Number of digits to the right of the decimal point, if the column uses the numeric datatype.

remote_type

int null

Maps local names to remote names. Required by the access methods of Component Integration Services to allow the software to pass native column datatype information in parameters to servers of class access_server.

remote_name

varchar(255) null

Maps local names to remote names. Required by the access methods of Component Integration Services to construct a query using the proper column names for a remote table.

xstatus

int null

The status of a column with extended datatypes. The values are:

• 0 = in row

• 1 = off row

xstatus is NULL for columns that do not have an extended datatype.

xtype

int null

ID of the class.

Used if a column in a table or a parameter in a procedure has a Java class as its datatype. When used, fields are not NULL, and the value of type is 0x39. See Java in Adaptive Server Enterprise for more information.

xdbid

int null

The database ID of the class. For system classes, the value is -1. Otherwise, the value is the current database ID.

Used if a column in a table or a parameter in a procedure has a Java class as its datatype. Fields are not NULL, and the value of type is 0x39. See Java in Adaptive Server Enterprise for more information.

accessrule

int null

The object ID of the access rule in sysprocedures. See “Row-level access control“ in Chapter 11, “Managing User Permissions” of the Security Administration Guide for more information.

status2

int null

Indicates the parameter mode of a SQLJ stored procedure, and the return type of a SQLJ function.

These internal bits support computed columns:

• 0x00000010, value16 – the column is a computed column.

• 0x00000020, value 32 – the column is a materialized computed column.

• 0x00000040, value 64 – the column is a computed column in a view.

• 0x00001000, value 4096 – the encrypted column has a decrypt default.

The status2 field from syscolumns uses this encoding to indicate a column’s encryption properties:

• 0x80, value 128 – the column is encrypted.

• 0x100, value 256– the column is encrypted with initialization vector.

• 0x200, value 512 – the column is encrypted with random padding.

• 0x400, value 1024 – the proxy table is encrypted.

• 0x1000, value 4096 – the encrypted column has a decrypt default.

• 0x20000, value 131072– the column is explicitly defined as not compressed.

• 0x00040000, value 262144 – the user-specified, or derived in-row length for LOB columns created as in-row.

status3

int

0x0001, value 1 – Indicates a hidden computed column for a function-based index key.

computedcol

int

Stores the object ID of the computed column definition.

encrtype

int null

Type of data in encrypted form.

lobcomp_lvl

tinyint

Compression level of the columns defined for large objects.

encrlen

int null

Length of encrypted data.

encrykeyid

int null

Object ID of key.

encrykeydb

varchar(30) null

Name of the database where the encryption key was created; NULL if it is in the same database as the encrypted column.

encrdate

datetime null

Creation date of encryption key; copied from sysobjects.crdate.

inrowlen

smallint

Stores the user-specified, or derived in-row length for LOB columns created as in-row.