|
Each row of the SYSTAB system view describes one table or view in the database. Additional information for views can be found in the SYSVIEW system view. The underlying system table for this view is ISYSTAB.
| Column name | Column type | Column constraint |
|---|---|---|
| table_id | UNSIGNED INT | NOT NULL |
| file_id | SMALLINT | NOT NULL |
| count | UNSIGNED BIGINT | NOT NULL |
| creator | UNSIGNED INT | NOT NULL |
| table_page_count | INTEGER | NOT NULL |
| ext_page_count | INTEGER | NOT NULL |
| commit_action | INTEGER | NOT NULL |
| share_type | INTEGER | NOT NULL |
| object_id | UNSIGNED BIGINT | NOT NULL |
| last_modified_at | TIMESTAMP | NOT NULL |
| table_name | CHAR(128) | NOT NULL |
| table_type | TINYINT | NOT NULL |
| replicate | CHAR(1) | NOT NULL |
| server_type | TINYINT | NOT NULL |
| tab_page_list | LONG VARBIT | NULL |
| ext_page_list | LONG VARBIT | NULL |
| pct_free | UNSIGNED INT | NULL |
| clustered_index_id | UNSIGNED INT | NULL |
| encrypted | CHAR(1) | NOT NULL |
| table_type_str | CHAR(8) | NOT NULL |
table_id Each table is assigned a unique number (the table number).
file_id A value indicating which dbspace contains the table.
count The number of rows in the table or materialized view. This value is updated during each successful checkpoint. This number is used by SQL Anywhere when optimizing database access. The count is always 0 for a non-materialized view or remote table.
creator The user number of the owner of the table or view.
table_page_count The total number of main pages used by the underlying table.
ext_page_count The total number of extension pages used by the underlying table.
commit_action For global temporary tables, 0 indicates that the ON COMMIT PRESERVE ROWS clause was specified when the table was created, 1 indicates that the ON COMMIT DELETE ROWS clause was specified when the table was created (the default behavior for temporary tables), and 3 indicates that the NOT TRANSACTIONAL clause was specified when the table was created. For non-temporary tables, commit_action is always 0.
share_type For global temporary tables, 4 indicates that the SHARE BY ALL clause was specified when the table was created, and 5 indicates that the SHARE BY ALL clause was not specified when the table was created. For non-temporary tables, share_type is always 5 because the SHARE BY ALL clause cannot be specified when creating non-temporary tables.
object_id The object ID of the table.
last_modified_at The time at which the data in the table was last modified. This column is only updated at checkpoint time.
table_name The name of the table or view. One creator cannot have two tables or views with the same name.
table_type The type of table or view. Values include:
| Value | Table type |
|---|---|
| 1 | Base table |
| 2 | Materialized view |
| 3 | Global temporary table |
| 21 | View |
replicate A value indicating whether the underlying table is a primary data source in a Replication Server installation.
server_type The location of the data for the underlying table. Values include:
| Value | Location |
|---|---|
| 1 | Local server (SQL Anywhere) |
| 3 | Remote server |
tab_page_list The set of pages that contain information for the table, expressed as a bitmap. This is for internal use only.
ext_page_list The set of pages that contain row extensions and large object (LOB) pages for the table, expressed as a bitmap. This is for internal use only.
pct_free The PCT_FREE specification for the table, if one has been specified; otherwise, NULL.
clustered_index_id The ID of the clustered index for the table. If none of the indexes are clustered, then this field is NULL.
encrypted A value (Y or N) indicating whether the table or materialized view is encrypted, one of Y or N.
table_type_str Readable value for table_type. Values include:
| Value | Table type |
|---|---|
| BASE | Base table |
| MAT VIEW | Materialized view |
| GBL TEMP | Global temporary table |
| VIEW | View |
PRIMARY KEY (table_id)
FOREIGN KEY (file_id) references SYS.ISYSFILE (file_id)
FOREIGN KEY (creator) references SYS.ISYSUSER (user_id)
FOREIGN KEY (object_id) references SYS.ISYSOBJECT (object_id) MATCH UNIQUE FULL
UNIQUE (table_name, creator)
UNIQUE (table_name)