Contents Index SYSSYNC system table SYSTABLEPERM system table

ASA SQL Reference
  System Tables

SYSTABLE system table


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].


Contents Index SYSSYNC system table SYSTABLEPERM system table