Skip to main content

Schema, table and columns

All objects belong to a SQL schema, which ensures a conflict-free namespace for accessing them and a focus for user authorization. The schema contains tables and views administered.

sys.schemas
name type references description
"id" INTEGER   The unique internal identifier for the schema.
"name" VARCHAR   The unique SQL name.
"authorization" INTEGER sys.auths.id The identifier associated with the authorization.
"owner" INTEGER sys.auths.id The reference to the owner of this schema.
"system" BOOLEAN   Is it a system schema.
sys.table_types
name type references description
"table_type_id" SMALLINT   The unique internal identifier for the table type.
"table_type_name" VARCHAR   The unique SQL name of this table type.
sys.tables
name type references description
"id" INTEGER   The unique internal identifier for the table
"name" VARCHAR   The SQL name.
"schema_id" INTEGER sys.schemas.id The schema identifier.
"query" VARCHAR   The query to populate a view.
"type" SMALLINT sys.table_types. table_type_id The table type identifier.
See sys.table_types for meaning of the identifiers
"system" BOOLEAN   Is it a system table.
"commit_action" SMALLINT   Action on commit for temp tables: 0=Not a temp table, 1=Delete rows, 2=Preserve rows, 3=Drop temp table.
"access" SMALLINT   Table access code.
"temporary" TINYINT   Retention period indicator; 0=persistent, 1=session.
sys.columns
name type references description
sys._columns
"id" INTEGER   The unique internal identifier for the column
"name" VARCHAR   The SQL name.
"type" VARCHAR sys.types.sqlname The SQL type name.
"type_digits" INTEGER   The number of digits (radix 2) for numeric types or max length for character/binary strings.
"type_scale" INTEGER   The precision after decimal point. Only applicable for decimal/numeric types.
"table_id" INTEGER sys.tables.id The table this column belongs to
"default" VARCHAR   The default lexical value.
"null" BOOLEAN   Nulls are allowed constraint.
"number" INTEGER   Column position in the underlying table, starting from 0.
"storage" VARCHAR   Internal storage class identifier.