syscolumns

All databases

Description

syscolumns contains one row for every column in every table and view, and a row for each parameter in a procedure.

Columns

The columns for syscolumns are:

Name

Datatype

Description

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 non-procedure 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.

Bit 8 is unused.

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

sysname

Column name

printfmt

varchar(255) null

Reserved

prec

tinyint null

Number of significant digits

scale

tinyint null

Number of digits to the right of the decimal point

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(30) 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. Refer to 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. Refer to 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 System Administration Guide for more information.

status2

int

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

Indexes

Unique clustered index On id, number, colid