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 schema name. Names are case sensitive. | |
| "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 MonetDB 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 or view. | |
| "name" | VARCHAR | The SQL table or view name. Names are case sensitive. | |
| "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. Query sys.table_types for meaning of the identifiers. |
| "system" | BOOLEAN | Is it a MonetDB system table or view. | |
| "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: 0 = READWRITE, 1 = READ ONLY, 2 = INSERT ONLY. | |
| "temporary" | TINYINT | Retention period indicator; 0 = persistent, 1 = session. |
sys.columns
| name | type | references | description |
|---|---|---|---|
| "id" | INTEGER | The unique internal identifier for the column. | |
| "name" | VARCHAR | The SQL column name. Names are case sensitive. | |
| "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 or view this column belongs to. |
| "default" | VARCHAR | The default literal value or expression. | |
| "null" | BOOLEAN | Whether NULLs are allowed. When false it denotes the NOT NULL constraint. | |
| "number" | INTEGER | Column position in the underlying table, starting from 0. | |
| "storage" | VARCHAR | Internal storage class identifier. |
sys.statistics
| name | type | references | description |
|---|---|---|---|
| "column_id" | INTEGER | sys.columns.id | The unique internal identifier for the column. |
| "schema" | VARCHAR | sys.schemas.name | The SQL column's schema name. Names are case sensitive. |
| "table" | VARCHAR | sys.tables.name | The SQL column's table name. Names are case sensitive. |
| "column" | VARCHAR | sys.columns.name | The SQL column name. Names are case sensitive. |
| "type" | VARCHAR | sys.types.sqlname | The SQL type name. |
| "width" | INTEGER | The number of bytes for fixed size data types or number of encoding bytes (1, 2 or 4) needed for character/binary strings. | |
| "count" | BIGINT | The approximate number of items/rows in this column. | |
| "unique" | BOOLEAN | Whether the data in this column contains only unique values. | |
| "nils" | BOOLEAN | Whether the data in this column contains any NULLs (so missing values). | |
| "minval" | CLOB | The minimum value found for this column or NULL. | |
| "maxval" | CLOB | The maximum value found for this column or NULL. | |
| "sorted" | BOOLEAN | Whether the data in this column is sorted in ascending order. | |
| "revsorted" | BOOLEAN | Whether the data in this column is sorted in descending order. |
Statistics information can be updated via the ANALYZE statement.