ASA SQL Reference
System Tables
| Column name | Column type | Column constraint | Table constraints |
|---|---|---|---|
| table_id | UNSIGNED INT | NOT NULL | Primary key |
| file_id | SMALLINT | NOT NULL | Foreign key references SYSFILE |
| count | UNSIGNED BIGINT | NOT NULL | |
| first_page | INTEGER | NOT NULL | |
| last_page | INTEGER | NOT NULL | |
| primary_root | INTEGER | NOT NULL | |
| creator | UNSIGNED INT | NOT NULL | Unique index. Foreign key references SYSUSERPERM.user_id |
| first_ext_page | INTEGER | NOT NULL | |
| last_ext_page | INTEGER | NOT NULL | |
| table_page_count | INTEGER | NOT NULL | |
| ext_page_count | INTEGER | NOT NULL | |
| table_name | CHAR(128) | NOT NULL | Unique index |
| table_type | CHAR(10) | NOT NULL | |
| view_def | LONG VARCHAR | ||
| remarks | LONG VARCHAR | ||
| replicate | CHAR(1) | NOT NULL | |
| existing_obj | CHAR(1) | ||
| remote_location | LONG VARCHAR | ||
| remote_objtype | CHAR(1) | ||
| srvid | INTEGER | Foreign key references SYSSERVERS | |
| server_type | CHAR(4) | NOT NULL | |
| primary_hash_limit | SMALL INT | NOT NULL | |
| page_map_start | INTEGER | NOT NULL | |
| source | LONG VARCHAR |
Each row of SYSTABLE describes one table or view in the database.
table_id Each table or view is assigned a unique number (the table number) which is the primary key for SYSTABLE.
file_id Indicates which database file contains the table. The file_id is a FOREIGN KEY for SYSFILE.
count The number of rows in the table is updated during each successful CHECKPOINT. This number is used by Adaptive Server Anywhere when optimizing database access. The count is always 0 for a view.
first_page Each database is divided into a number of fixed-size pages. This value identifies the first page that contains information for this table, and is used internally to find the start of this table. The first_page is always 0 for a view.
last_page The last page that contains information for this table. The last_page is always 0 for a view. For global temporary tables, 0 indicates that the table was created using ON COMMIT PRESERVE ROWS while 1 indicates that the table was created using ON COMMIT DELETE ROWS.
primary_root Primary keys are stored in the database as B-trees. The primary_root locates the root of the B-tree for the primary key for the table. It will be 0 for a view and for a table with no primary key.
creator The user number of the owner of the table or view. The name of the user can be found by looking in SYSUSERPERM.
first_ext_page The first page used for storing row extensions and blobs.
last_ext_page The last page used for storing row extensions and blobs. The pages are maintained as a doubly-linked list.
table_page_count The total number of main pages used by this table.
ext_page_count The total number of extension (blob) pages used by this table.
table_name The name of the table or view. One creator cannot have two tables or views with the same name.
table_type This column is BASE for base tables, VIEW for views, and be GBL TEMP for global temporary tables. No entry is created for local temporary tables.
view_def For a view, this column contains the CREATE VIEW command that was used to create the view. For a table, this column contains any CHECK constraints for the table.
remarks A comment string.
replicate (Y/N) Indicates whether the table is a primary data source in a Replication Server installation.
existing_obj (Y/N) Indicates whether the table previously existed or not.
remote_location Indicates the storage location of the remote object.
remote_objtype Indicates the type of remote object: 'T' if table; 'V' if view; 'R' if rpc; 'B' if JavaBean.
srvid The unique ID for the server.
server_type The location of the data for the table. It is either SA or OMNI.
primary_hash_limit The hash size for the primary key index for this table.
page_map_start The start of the page map maintained for this table. Page maps are used to facilitate blocked I/O during sequential scans.
source This column contains the original source for the procedure if the preserve_source_format option is ON. It is used to maintain the appearance of the original text. For more information, see PRESERVE_SOURCE_FORMAT option [database].