syscolumns

Provides information about table and view columns, including column name, data type, length, default values, and computed column definitions.

Name Datatype Description
id int The table ID of the column, or the procedure ID of the associated parameter.
number smallint A subprocedure number if a stored procedure is grouped.
0 – For non-procedure entries.
colid smallint The column ID.
status tinyint A bitmask describing column properties such as bit-column position, replication behavior for text/image columns, nullability, presence of multiple check constraints, and whether the column is an identity column.
The possible values are:
  • Bits 0–2 (values 1, 2, and 4) – Specifies bit positioning if the column uses the bit datatype. If the column uses the text or image datatype, bits 0 and 1 specify replication status as follows:
    • 01 – Always replicates.
    • 10 – Replicates only if changed.
    • 00 – Never replicates.
  • Bit 3 (value 8) – Specifies whether NULL values are allowed.
  • Bit 4 (value 16) – Specifies whether more than one check constraint exists.
  • Bits 5 and 6 – Used internally.
  • Bit 7 (value 128) – Specifies an identity column.
type tinyint The physical storage type of the column. The value is derived from systypes.
length int The physical storage length of the column, in bytes. The value is derived from systypes or specified by the user.
offset smallint The byte offset within the row where the column appears.
A negative value specifies a variable-length column.
usertype smallint The user type ID derived from systypes. For nullable datatypes, the value of usertype is derived from usertype of the corresponding base type specified in DDL statements.
cdefault int The object ID of the procedure that defines the default value for the column.
domain int The object ID of the first rule or check constraint associated with the column.
name varchar(255) not null The column name.
printfmt varchar(255) null Reserved for internal use.
prec tinyint null The total number of digits for numeric datatypes.
scale tinyint null The number of digits to the right of the decimal point for numeric datatypes.
remote_type int null The mapping ID used by Component Integration Services to translate local datatypes to corresponding datatypes on remote servers.
remote_name varchar(255) null An alternative column name used when generating queries against remote tables.
xstatus int null The status of a column with extended datatypes.
The possible values are:
  • 0 – In row.
  • 1 – Off row.
  • NULL – Non-extended datatypes.
xtype int null The ID of the Java class used as a datatype if a column or procedure parameter is defined using a Java class.
xdbid int null The database ID where the Java class datatype is defined.
The possible values are:
  • -1 – System classes.
  • Other values – A stored database ID.
accessrule int null The object ID of the row-level access control rule stored in sysprocedures.
status2 int null Extended status flags indicating properties such as SQLJ parameter mode, computed column attributes, encryption settings, and compression options.
The parameter mode of a SQLJ stored procedure, and the return type of a SQLJ function:
  • 0x00000001, value 1 – The IN parameter.
  • 0x00000002, value 2 – The OUT parameter.

Internal bits supporting computed columns:
  • 0x00000010, value 16 – A computed column.
  • 0x00000020, value 32 – A materialized computed column.
  • 0x00000040, value 64 – A computed column in a view.
  • 0x00001000, value 4096 – A decrypt default for an encrypted column.

Encoding indicating a column’s encryption properties:
  • 0x80, value 128 – An encrypted column.
  • 0x100, value 256 – A column encrypted using an initialization vector.
  • 0x200, value 512 – A column encrypted with random padding.
  • 0x400, value 1024 – An encrypted proxy table.
  • 0x1000, value 4096 – A column encrypted with a default decryption value.
  • 0x20000, value 131072 – A column explicitly set as not compressed.
  • 0x00040000, value 262144 – User-specified or derived in-row length for LOB columns created as in-row.
status3 smallint Specifies that the column stores bitmask values.
0x0001 (value 1) – A hidden computed column used as a key in a function-based index.
computedcol int An object ID referencing the definition of the computed column.
encrtype int null An ID specifying the encryption format used for the column data.
lobcomp_lvl tinyint A compression level applied to large object (LOB) columns.
encrlen int null The length of encrypted column data.
encrkeyid int null The object ID of the encryption key.
encrkeydb varchar(30) null The name of the database where the encryption key was created.
NULL – The key is stored in the same database as the encrypted column.
encrdate datetime null The date and time when the encryption key was created. The value is derived from sysobjects.crdate.
inrowlen smallint User-defined or system-derived in-row storage length for LOB columns configured for in-row storage.