User's Guide Part VI. SQL Anywhere Reference Chapter 50. SQL Anywhere System TablesThis section contains descriptions of each of the system tables. The system tables are described via the CREATE TABLE commands used to create them. They serve as good examples of how tables are created in SQL. Following the CREATE TABLE command, each column is briefly described.
Several of the columns have only two possible values. Usually these values are "Y" and "N" for "yes" and "no" respectively. These columns are designated by "(Y/N)".
CREATE TABLE SYS.DUMMY (dummy_col INT NOT NULL)
The DUMMY table is provided as a table that always has exactly one row. This can be useful for extracting information from the database, as in the following example that gets the current user ID and the current date from the database.
SELECT USER, today(*) FROM SYS.DUMMY
dummy_col This column is not used. It is present because a table cannot be created with no columns.
CREATE TABLE SYS.SYSARTICLE (publication_id SMALLINT NOT NULL,table_id SMALLINT NOT NULL,where_expr LONG VARCHAR,subscribe_by_expr LONG VARCHAR,PRIMARY KEY ( publication_id, table_id ),FOREIGN KEY REFERENCES SYS.SYSPUBLICATION,FOREIGN KEY REFERENCES SYS.SYSTABLE)
Each row of SYSARTICLE describes an article in a SQL Remote publication.
publication_id The publication of which this article is a part.
table_id Each article consists of columns and rows from a single table. This column contains the table ID for this table.
where_expr For articles that contain a subset of rows defined by a WHERE clause, this column contains the search condition.
subscribe_by_expr For articles that contain a subset of rows defined by a SUBSCRIBE BY expression, this column contains the expression.
CREATE TABLE SYS.SYSARTICLECOL (publication_id SMALLINT NOT NULL,table_id SMALLINT NOT NULL,column_id SMALLINT NOT NULL,PRIMARY KEY (publication_id, table_id, column_id),FOREIGN KEY REFERENCES SYS.SYSARTICLE,FOREIGN KEY REFERENCES SYS.SYSCOLUMN)
Each row identifies a column in an article, identifying the column, the table it is in, and the publication it is part of.
publication_id A unique identifier for the publication of which the column is a part.
table_id The table to which the column belongs.
column_id The column identifier, from the SYSCOLUMN system table.
CREATE TABLE SYS.SYSCOLLATION (collation_id SMALLINT NOT NULL,collation_name CHAR(128) NOT NULL,collation_label CHAR(10) NOT NULL,collation_order BINARY(256) NOT NULL,PRIMARY KEY ( collation_id ))
This table contains the collation sequences available to SQL Anywhere. There is no way to modify the contents of this table.
collation_id A unique number identifying the collation sequence. The collation sequence with collation_id equal 2 is the sequence used in previous versions of SQL Anywhere, and is the default when a database is created with DBINIT.
collation_name The name of the collation sequence.
collation_label A string identifying each of the available collation sequences. The collation sequence to be used is selected when the database is created by specifying the collation label with the -z option.
collation_order An array of bytes defining how each of the 256 character codes are treated for comparison purposes. All string comparisons translate each character according to the collation order table before comparing the characters. For the different ASCII code pages, the only difference is how accented characters are sorted. In general, an accented character is sorted as if it were the same as the nonaccented character.
CREATE TABLE SYS.SYSCOLPERM (table_id SMALLINT NOT NULL,grantee SMALLINT NOT NULL,grantor SMALLINT NOT NULL,column_id SMALLINT NOT NULL,privilege_type SMALLINT NOT NULL,is_grantable CHAR( 1 ) NOT NULL,PRIMARY KEY ( table_id, grantee,grantor, column_id, privilege_type ),FOREIGN KEY grantee ( grantee ) REFERENCESSYS.SYSUSERPERM ( user_id ),FOREIGN KEY grantor ( grantor ) REFERENCESSYS.SYSUSERPERM ( user_id ),FOREIGN KEY REFERENCES SYS.SYSCOLUMN)
The GRANT command can give UPDATE permission to individual columns in a table. Each column with UPDATE permission is recorded in one row of SYSCOLPERM.
table_id The table number for the table containing the column.
grantee The user number of the user ID given UPDATE permission on the column. If the grantee is the user number for the special PUBLIC user ID, the UPDATE permission is given to all user IDs.
grantor The user number of the user ID granting the permission.
column_id This column number, together with the table_id, identifies the column for which UPDATE permission has been granted.
privilege_type The number in this column indicates the kind of column permission (REFERENCES, SELECT or UPDATE).
is_grantable (Y/N). Indicates if the permission on the column was granted by the grantor to the grantee WITH GRANT OPTION.
CREATE TABLE SYS.SYSCOLUMN (table_id SMALLINT NOT NULL,column_id SMALLINT NOT NULL,pkey CHAR(1) NOT NULL,domain_id SMALLINT NOT NULL,nulls CHAR(1) NOT NULL,width SMALLINT NOT NULL,scale SMALLINT NOT NULL,estimate INT NOT NULL,column_name CHAR(128) NOT NULL,remarks LONG VARCHAR,"default" LONG VARCHAR,"check" LONG VARCHAR,user_type SMALLINT,format_str CHAR(128),PRIMARY KEY ( table_id, column_id ),FOREIGN KEY REFERENCES SYS.SYSTABLE,FOREIGN KEY REFERENCES SYS.SYSDOMAIN)
Each column in every table or view is described by one row in SYSCOLUMN.
table_id The table number uniquely identifies the table or view to which this column belongs.
column_id Each table starts numbering columns at 1. The order of column numbers determines the order that columns are displayed in the command select * from table.
pkey (Y/N) Indicate whether this column is part of the primary key for the table.
domain_id Identify the data type for the column by the data type number listed in the SYSDOMAIN table.
nulls (Y/N) Indicate whether the NULL value is allowed in this column.
width This column contains the length of string columns, the precision of numeric columns, and the number of bytes of storage for all other data types.
scale The number of digits after the decimal point for numeric data type columns, and zero for all other data types.
estimate A self-tuning parameter for the optimizer. SQL Anywhere will learn from previous queries by adjusting guesses that are made by the optimizer.
column_name The name of the column.
remarks A comment string.
default The default value for the column. This value is only used when an INSERT statement does not specify a value for the column.
check. Any CHECK condition defined on the column.
user_type If the column is defined on a user-defined data type, the data type is held here.
format_str Currently unused.
CREATE TABLE SYS.SYSDOMAIN (domain_id SMALLINT NOT NULL,domain_name CHAR(128) NOT NULL,type_id SMALLINT,precision SMALLINT,PRIMARY KEY ( domain_id ))
Each of the predefined data types (sometimes called domains) in SQL Anywhere is assigned a unique number. The SYSDOMAIN table is provided for informational purposes to show the association between these numbers and the appropriate data type. This table is never changed by SQL Anywhere.
domain_id The unique number assigned to each data type. These numbers cannot be changed.
domain_name A string containing the data type normally found in the CREATE TABLE command, such as char or integer.
type_id The ODBC data type. This corresponds to "data_type" in the Transact-SQL-compatibility DBO.SYSTYPES table.
precision The number of significant digits that can be stored using this data type. The column value is NULL for non-numeric data types.
CREATE TABLE SYS.SYSFILE (file_id SMALLINT NOT NULL,file_name CHAR(80) NOT NULL,dbspace_name CHAR(128) NOT NULL,PRIMARY KEY ( file_id ))
Every database consists of one or more operating system files. Each file is recorded in SYSFILE.
file_id Each file in a database is assigned a unique number. This file identifier is the primary key for SYSFILE. All system tables are stored in file_id 0.
file_name The database name is stored when a database is created. This name is for informational purposes only.
dbspace_name Every file has a dbspace name that is unique. It is used in the CREATE TABLE command.
CREATE TABLE SYS.SYSFKCOL (foreign_table_id SMALLINT NOT NULL,foreign_key_id SMALLINT NOT NULL,foreign_column_id SMALLINT NOT NULL,primary_column_id SMALLINT NOT NULL,PRIMARY KEY ( foreign_table_id,foreign_key_id, foreign_column_id ),FOREIGN KEY REFERENCES SYS.SYSFOREIGNKEY,FOREIGN KEY ( foreign_table_id,foreign_column_id ) REFERENCESSYS.SYSCOLUMN ( table_id, column_id ))
Each row of SYSFKCOL describes the association between a foreign column in the foreign table of a relationship and the primary column in the primary table.
foreign_table_id The table number of the foreign table.
foreign_key_id The key number of the FOREIGN KEY for the foreign table. Together, foreign_table_id and foreign_key_id uniquely identify one row in SYSFOREIGNKEY, and the table number for the primary table can be obtained from that row.
foreign_column_id This column number, together with the foreign_table_id, identify the foreign column description in SYSCOLUMN.
primary_column_id This column number, together with the primary_table_id obtained from SYSFOREIGNKEY, identify the primary column description in SYSCOLUMN.
CREATE TABLE SYS.SYSFOREIGNKEY (foreign_table_id SMALLINT NOT NULL,foreign_key_id SMALLINT NOT NULL,primary_table_id SMALLINT NOT NULL,root INT NOT NULL,check_on_commit CHAR(1) NOT NULL,nulls CHAR(1) NOT NULL,role CHAR(128) NOT NULL,remarks LONG VARCHAR,PRIMARY KEY ( foreign_table_id, foreign_key_id ),UNIQUE ( role, foreign_table_id ),FOREIGN KEY foreign_table ( foreign_table_id )REFERENCES SYS.SYSTABLE ( table_id ),FOREIGN KEY primary_table ( primary_table_id )REFERENCES SYS.SYSTABLE ( table_id ))
A foreign key is a relationship between two tables---the foreign table and the primary table. Every foreign key is defined by one row in SYSFOREIGNKEY and one or more rows in SYSFKCOL. SYSFOREIGNKEY contains general information about the foreign key while SYSFKCOL identifies the columns in the foreign key and associates each column in the foreign key with a column in the primary key of the primary table.
foreign_table_id The table number of the foreign table.
foreign_key_id Each foreign key has a foreign key numberthat is unique with respect to:
primary_table_id The table number of the primary table.
root Foreign keys are stored in the database as B-trees. The root identifies the location of the root of the B-tree in the database file.
nulls (Y/N) Indicate whether the columns in the foreign key are allowed to contain the NULL value. Note that this setting is independent of the nulls setting in the columns contained in the foreign key.
check_on_commit (Y/N) Indicate whether INSERT and UPDATE commands should wait until the next COMMIT command to check if foreign keys are valid. A foreign key is valid if, for each row in the foreign table, the values in the columns of the foreign key either contain the NULL value or match the primary key values in some row of the primary table.
role The name of the relationship between the foreign table and the primary table. Unless otherwise specified, the role name will be the same as the name of the primary table. The foreign table cannot have two foreign keys with the same role name.
remarks A comment string.
CREATE TABLE SYS.SYSGROUP (group_id SMALLINT NOT NULL,group_member SMALLINT NOT NULL,PRIMARY KEY ( group_id, group_member ),FOREIGN KEY group_id ( group_id ) REFERENCESSYS.SYSUSERPERM ( user_id ),FOREIGN KEY group_member ( group_member )REFERENCES SYS.SYSUSERPERM ( user_id ))
There is one row in SYSGROUP for every member of every group. This table describes a many-to-many relationship between groups and members. A group may have many members and a user may be a member of many groups.
group_id The user number of group.
group_member The user number of a member.
CREATE TABLE SYS.SYSINDEX (table_id SMALLINT NOT NULL,index_id SMALLINT NOT NULL,root INT NOT NULL,file_id SMALLINT NOT NULL,"unique" CHAR(1) NOT NULL,creator SMALLINT NOT NULL,index_name CHAR(128) NOT NULL,remarks LONG VARCHAR,PRIMARY KEY ( table_id, index_id ),UNIQUE ( index_name, creator ),FOREIGN KEY REFERENCES SYS.SYSTABLE,FOREIGN KEY REFERENCES SYS.SYSFILE,FOREIGN KEY ( creator ) REFERENCESSYS.SYSUSERPERM ( user_id ))
Each index in the database is described by one row in SYSINDEX. Each column in the index is described by one row in SYSIXCOL.
table_id The table number uniquely identifies the table to which this index applies.
index_id Each index for one particular table is assigned a unique index number.
root Indexes are stored in the database as B-trees. The root identifies the location of the root of the B-tree in the database file.
file_id The index is completely contained in the file with this file_id (see SYSFILE). In the current implementation of SQL Anywhere, this file is always the same as the file containing the table.
unique Indicate whether the index is a unique index ("Y"), a non-unique index ("N"), or a unique constraint ("U"). A unique index prevents two rows in the indexed table from having the same values in the index columns.
creator The user number of the creator of the index. In the current implementation of SQL Anywhere, this user is always the same as the creator of the table identified by table_id.
index_name The name of the index. A user ID cannot have two indexes with the same name.
remarks A comment string.
CREATE TABLE SYS.SYSINFO (page_size SMALLINT NOT NULL,encryption CHAR(1) NOT NULL,blank_padding CHAR(1) NOT NULL,case_sensitivity CHAR(1) NOT NULL,default_collation CHAR(10) NOT NULL,database_version SMALLINT NOT NULL)
This table indicates the database characteristics as defined when the database was created using DBINIT. It always contains only one row.
page_size The page size specified to DBINIT. The default value is 1024.
encryption The value "Y" or "N" depending on whether the -e switch was used with DBINIT.
blank_padding The value "Y" or "N" depending on whether the database was created to use blank padding for string comparisons in the database ( -b switch was used with DBINIT).
case_sensitivity The value "Y" or "N" depending on whether the -c switch was used with DBINIT. Case sensitivity affects value comparisons, but not table and column name comparisons. For example, if case sensitivity is enabled, the system catalog names such as SYSCATALOG must be specified in uppercase since that is how the name was spelled when it was created.
default_collation A string corresponding to the collation_label in SYSCOLLATE corresponding to the collation sequence specified with DBINIT. The default value corresponds to the multilingual collation sequence (code page 850), which was the default prior to Watcom SQL 3.2. The collation sequence is used for all string comparisons, including searches for character strings as well as column and table name comparison.
database_version A small integer value indicating the database format. As newer versions of SQL Anywhere become available, new features may require that the format of the database file change. The version number allows SQL Anywhere software to determine if this database was created with a newer version of the software and thus cannot be understood by the software in use.
CREATE TABLE SYS.SYSLOGIN (integrated_login_id CHAR(128) NOT NULL,login_uid SMALLINT NOT NULL,remarks LONG VARCHAR,PRIMARY KEY ( integrated_login_id ))
This table contains all the User Profile names that can be used to connect to the database using an integrated logon. As a security measure, only users with DBA authority can view the contents of this table.
integrated_login_id Is a string value containing the User Profile name used to map to a user ID in the database. When a user successfully logs on using this User Profile name, and the database is enabled to accept integrated logons, the user can connect to the database without providing a user ID or password.
login_uid Is a foreign key to the system table SYSUSERPERM.
remarks Contains a comment string
CREATE TABLE SYS.SYSIXCOL (table_id SMALLINT NOT NULL,index_id SMALLINT NOT NULL,sequence SMALLINT NOT NULL,column_id SMALLINT NOT NULL,"order" CHAR(1) NOT NULL,PRIMARY KEY ( table_id, index_id, sequence )FOREIGN KEY REFERENCES SYS.SYSINDEX,FOREIGN KEY REFERENCES SYS.SYSCOLUMN)
Every index has one row in SYSIXCOL for each column in the index.
table_id Identifies the table to which the index applies.
index_id Identifies in which index this column is used. Together, table_id and index_id identify one index described in SYSINDEX.
sequence Each column in an index is assigned a unique number starting at 0. The order of these numbers determines the relative significance of the columns in the index. The most important column has sequence number 0.
column_id The column number identifies which column is indexed. Together, table_id and column_id identify one column in SYSCOLUMN.
order (A/D) Indicate whether this column in the index is kept in ascending or descending order.
CREATE TABLE SYS.SYSOPTION (user_id SMALLINT NOT NULL,"option" CHAR(128) NOT NULL,"setting" CHAR(80) NOT NULL,PRIMARY KEY ( user_id, "option" ),FOREIGN KEY REFERENCES SYS.SYSUSERPERM)
Options settings are stored in the SYSOPTION table by the SET command. Each user can have their own setting for each option. In addition, settings for the PUBLIC user ID define the default settings to be used for user IDs that do not have their own setting.
user_id The user number to whom this option setting applies.
option The name of the option.
setting The current setting for the named option.
CREATE TABLE SYS.SYSPROCEDURE (proc_id SMALLINT NOT NULL,creator SMALLINT NOT NULL,proc_name CHAR(128) NOT NULL,proc_defn LONG VARCHAR,remarks LONG VARCHAR,replicate CHAR(1) NOT NULL,PRIMARY KEY ( proc_id ),UNIQUE ( proc_name, creator ),FOREIGN KEY ( creator ) REFERENCESSYS.SYSUSERPERM ( user_id ))
Each procedure in the database is described by one row in SYSPROCEDURE.
proc_id Each procedure is assigned a unique number (the procedure number) that is the primary key for SYSPROCEDURE.
creator This user number identifies the owner of the procedure. The name of the user can be found by looking in SYSUSERPERM.
proc_name The name of the procedure. One creator cannot have two procedures with the same name.
proc_defn The command used to create the procedure.
remarks A comment string.
replicate Holds a Y if the procedure is a primary data source in a Replication Server installation, or an N if not.
CREATE TABLE SYS.SYSPROCPARM (proc_id SMALLINT NOT NULL,parm_id SMALLINT NOT NULL,parm_type SMALLINT NOT NULL,parm_mode_in CHAR(1) NOT NULL,parm_mode_out CHAR(1) NOT NULL,domain_id SMALLINT NOT NULL,width SMALLINT NOT NULL,scale SMALLINT NOT NULL,parm_name CHAR(128) NOT NULL,remarks LONG VARCHAR,"default" LONG VARCHAR,PRIMARY KEY ( proc_id, parm_id ),FOREIGN KEY REFERENCES SYS.SYSPROCEDURE,FOREIGN KEY REFERENCES SYS.SYSDOMAIN)
Each parameter to a procedure in the database is described by one row in SYSPROCEDURE.
proc_id The procedure number uniquely identifies the procedure to which this parameter belongs.
parm_id Each procedure starts numbering parameters at 1. The order of parameter numbers corresponds to the order in which they were defined.
parm_type The type of parameter will be one of the following:
parm_mode_in (Y/N) Indicate whether this parameter supplies a value to the procedure (IN or INOUT parameters).
parm_mode_out (Y/N) Indicate whether this parameter returns a value from the procedure (OUT or INOUT parameters).
domain_id Identify the data type for the parameter by the data type number listed in the SYSDOMAIN table.
width This column contains the length of string parameters, the precision of numeric parameters, and the number of bytes of storage for all other data types.
scale The number of digits after the decimal point for numeric data type parameters, and zero for all other data types.
parm_name The name of the parameter.
remarks A comment string.
default The default value for the parameter, held as a string.
CREATE TABLE SYS.SYSPROCPERM (proc_id SMALLINT NOT NULL,grantee SMALLINT NOT NULL,PRIMARY KEY ( proc_id, grantee )FOREIGN KEY ( grantee ) REFERENCESSYS.SYSUSERPERM ( user_id ),FOREIGN KEY REFERENCES SYS.SYSPROCEDURE)
Only users who have been granted permission can call a procedure. Each row of the SYSPROCPERM table corresponds to one user granted permission to call one procedure.
proc_id The procedure number uniquely identifies the procedure for which permission has been granted.
grantee The user number of the user ID receiving the permission.
CREATE TABLE SYS.SYSPUBLICATION (publication_id SMALLINT NOT NULL,creator SMALLINT NOT NULL,publication_name CHAR(128) NOT NULL,remarks LONG VARCHAR,PRIMARY KEY ( publication_id ),FOREIGN KEY ( creator )REFERENCES SYS.SYSUSERPERM (user_id ))
Each row describes a SQL Remote publication.
publication_id A unique identifying number for the publication.
creator The owner of the publication.
publication_name The name of the publication, which must be a valid identifier.
remarks Descriptive comments.
CREATE TABLE SYS.SYSREMOTEUSER (user_id SMALLINT NOT NULL,consolidate CHAR(1) NOT NULL,type_id SMALLINT NOT NULL,address LONG VARCHAR NOT NULL,frequency CHAR(!) NOT NULL,send_time TIME,log_send NUMERIC(20,0) NOT NULL,time_sent TIMESTAMP,log_sent NUMERIC(20,0) NOT NULL,confirm_sent NUMERIC(20,0) NOT NULL,send_count INTEGER NOT NULL,resend_count INTEGER NOT NULL,time_received TIMESTAMP,log_received NUMERIC(20,0) NOT NULL,confirm_received NUMERIC(20,0),receive_count INTEGER NOT NULL,rereceive_count INTEGER NOT NULL,PRIMARY KEY (user_id),FOREIGN KEY REFERENCES SYS.SYSUSERPERM)
Each row describes a userid with REMOTE permissions (a subscriber), together with the status of SQL Remote messages sent to and from that user.
user_id The user ID of the user with REMOTE permissions.
consolidate The column contains either an R to indicate a user granted REMOTE permissions, or a C to indicate a user granted CONSOLIDATE permissions.
type_id Identifies which of the of the message systems supported by SQL Remote is to be used to send messages to this user.
address The address to which SQL Remote messages are to be sent. The address must be appropriate for the address_type.
frequency How frequently SQL Remote messages are to be sent.
send_time The next time messages are to be sent to this user.
log_send Messages are sent only to subscribers for whom log_send is greater than log_sent.
time_sent The time the most recent message was sent to this subscriber.
log_sent The log offset for the most recently sent operation.
confirm_sent The log offset for the most recently confirmed operation from this subscriber.
send_count How many SQL Remote messages have been sent.
resend_count Counter to ensure messages are applied only once at the subscriber database.
time_received The time the most recent message was received from this subscriber.
log_received The log offset in the subscriber's database for the operation most recently received at the current database.
confirm_received The log offset in the subscriber's database for the most recent operation for which a confirmation message has been sent.
receive_count How many messages have been received.
rereceive_count Counter to ensure messages are applied only once at the current database.
CREATE TABLE SYS.SYSSUBSCRIPTION (publication_id SMALLINT NOT NULL,user_id SMALLINT NOT NULL,subscribe_by CHAR(128) NOT NULL,created NUMERIC(20,0) NOT NULL,started NUMERIC(20,0),PRIMARY KEY (publication_id, user_id, subscribe_by),FOREIGN KEY REFERENCES SYS.SYSPUBLICATION,FOREIGN KEY REFERENCES SYS.SYSREMOTEUSER);
Each row describes a subscription from one user ID (which must have REMOTE permissions) to one publication.
publication_id The identifier for the publication to which the user ID is subscribed.
user_id The user ID that is subscribed to the publication.
subscribe_by For publications with a SUBSCRIBE BY expression, this column holds the matching value for this subscription.
created The offset in the transaction log at which the subscription was created.
started The offset in the transaction log at which the subscription was started.
CREATE TABLE SYS.SYSTABLE (table_id SMALLINT NOT NULL,file_id SMALLINT NOT NULL,count INTEGER NOT NULL,first_page INT NOT NULL,last_page INT NOT NULL,primary_root INT NOT NULL,creator SMALLINT NOT NULL,table_name CHAR(128) NOT NULL,table_type CHAR(10) NOT NULL,view_def LONG VARCHAR,remarks LONG VARCHAR,replicate CHAR(1) NOT NULL,PRIMARY KEY ( table_id ),UNIQUE ( table_name, creator ),FOREIGN KEY ( creator ) REFERENCESSYS.SYSUSERPERM ( user_id ),FOREIGN KEY REFERENCES SYS.SYSFILE)
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) that is the primary key for SYSTABLE.
file_id The file number 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 SQL Anywhere when optimizing database access. The count is always 0 for a view.
first_page Each SQL Anywhere database is divided into a number of fixed size pages. This value identifies the first page containing 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 containing information for this table. The last_page is always 0 for a view.
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 This user number identifies the owner of the table or view. The name of the user can be found by looking in SYSUSERPERM.
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 will be "BASE" for base tables and "VIEW" for views. It will be "GBL TEMP" for global temporary tables and "LCL TEMP" for local temporary tables.
view_def For a view, this column contains the create view command used to create the view. For a table, this column will contain any CHECK constraints for the table.
remarks A comment string.
replicate Holds a Y if the table is a primary data source in a Replication Server installation, or an N if not.
CREATE TABLE SYS.SYSTABLEPERM (stable_id SMALLINT NOT NULL,grantee SMALLINT NOT NULL,grantor SMALLINT NOT NULL,ttable_id SMALLINT NOT NULL,selectauth CHAR(1) NOT NULL,insertauth CHAR(1) NOT NULL,deleteauth CHAR(1) NOT NULL,updateauth CHAR(1) NOT NULL,updatecols CHAR(1) NOT NULL,alterauth CHAR(1) NOT NULL,referenceauth CHAR(1) NOT NULL,PRIMARY KEY ( stable_id, grantee, grantor ),FOREIGN KEY ( stable_id )REFERENCES SYS.SYSTABLE ( table_id ),FOREIGN KEY future ( ttable_id )REFERENCES SYS.SYSTABLE ( table_id ),FOREIGN KEY grantee ( grantee ) REFERENCESSYS.SYSUSERPERM ( user_id ),FOREIGN KEY grantor ( grantor )REFERENCES SYS.SYSUSERPERM ( user_id ))
Permissions given by the GRANT command are stored in SYSTABLEPERM. Each row in this table corresponds to one table, one user ID granting the permission (grantor) and one user ID granted the permission (grantee).
There are several types of permission that can be granted. Each permission can have one of the following three values.
| Permissions The grantee might have been given permission for the same table by another grantor. If so, this information would be recorded in a different row of SYSTABLEPERM. |
stable_id The table number of the table or view to which the permissions apply.
grantor The user number of the user ID granting the permission.
grantee The user number of the user ID receiving the permission.
ttable_id In the current version of SQL Anywhere, this table number is always the same as stable_id.
selectauth (Y/N/G) Indicate whether SELECT permission has been granted.
insertauth (Y/N/G) Indicate whether INSERT permission has been granted.
deleteauth (Y/N/G) Indicate whether DELETE permission has been granted.
updateauth (Y/N/G) Indicate whether UPDATE permission has been granted for all columns in the table. (Only UPDATE permission can be given on individual columns. All other permissions are for all columns in a table.)
updatecols (Y/N) Indicates whether UPDATE permission has only been granted for some of the columns in the table. If updatecols has the value Y, there will be one or more rows in SYSCOLPERM granting update permission for the columns in this table.
alterauth (Y/N/G) Indicate whether ALTER permission has been granted.
referenceauth (Y/N/G) Indicate whether REFERENCE permission has been granted.
CREATE TABLE SYS.SYSTRIGGER (trigger_id SMALLINT NOT NULL,table_id SMALLINT NOT NULL,event CHAR(1) NOT NULL,trigger_time CHAR(1) NOT NULL,trigger_order SMALLINT,foreign_table_id SMALLINT,foreign_key_id SMALLINT,referential_action CHAR(1),trigger_name CHAR(128),trigger_defn LONG VARCHAR NOT NULL,remarks LONG VARCHAR,PRIMARY KEY ( trigger_id ),UNIQUE ( trigger_name ),UNIQUE ( table_id, event,trigger_time, trigger_order ),UNIQUE ( table_id, foreign_table_id,foreign_key_id, event ),FOREIGN KEY REFERENCES SYS.SYSTABLE,FOREIGN KEY REFERENCES SYS.SYSFOREIGNKEY)
Each trigger in the database is described by one row in SYSTRIGGER. The table also contains triggers automatically created by the database for foreign key definitions which have a referential triggered action (such as ON DELETE CASCADE).
trigger_id Each trigger is assigned a unique number (the trigger number) that is the primary key for SYSTRIGGER.
table_id The table number uniquely identifies the table to which this trigger belongs.
event The event or events that cause the trigger to fire. This single character value corresponds to the trigger event that was specified when the trigger was created.
trigger_time The time at which the trigger will fire. This single character value corresponds to the trigger time that was specified when the trigger was created.
trigger_order The order in which the trigger will fire. This determines the order that triggers are fired when there are triggers of the same type (insert, update, or delete) that fire at the same time (before or after).
foreign_table_id The foreign table number identifies the table containing a foreign key definition which has a referential triggered action (such as ON DELETE CASCADE).
foreign_key_id The foreign key number identifies the foreign key for the table referenced by foreign_table_id.
referential_action The action defined by a foreign key. This single character value corresponds to the action that was specified when the foreign key was created.
trigger_name The name of the trigger. One table cannot have two triggers with the same name.
trigger_defn The command used to create the trigger.
remarks A comment string.
CREATE TABLE SYS.SYSUSERMESSAGES (error INT NOT NULL,uid SMALLINT NOT NULL,description VARCHAR(255) NOT NULL,langid SMALLINT NOT NULL,UNIQUE ( error,langid ))
Each row holds a user-defined message for an error condition.
error A unique identifying number for the error condition.
uid The user ID defining the message.
description The message corresponding to the error condition.
langid Reserved.
CREATE TABLE SYS.SYSUSERPERM (user_id SMALLINT NOT NULL,user_name CHAR(128) NOT NULL UNIQUE,password BINARY(36),resourceauth CHAR(1) NOT NULL,dbaauth CHAR(1) NOT NULL,scheduleauth CHAR(1) NOT NULL,publishauth CHAR(1) NOT NULL,remotedbaauth CHAR(1) NOT NULL,user_group CHAR(1) NOT NULL,remarks LONG VARCHAR,PRIMARY KEY ( user_id ))
| DBA permissions required SYSUSERPERM contains passwords, and so DBA permissions are required to SELECT from the table. |
Each row of SYSUSERPERM describes one user ID.
user_id Each new user ID is assigned a unique number (the user number) that is the primary key for SYSUSERPERM.
user_name A string containing the name for the user ID. Each userid must have a unique name.
password The password for the user ID. The password contains the NULL value for the special user IDs SYS and PUBLIC, preventing anyone from connecting to these user IDs.
resourceauth (Y/N) Indicate whether the user has RESOURCE authority. Resource authority is required to create tables.
dbaauth (Y/N) Indicate whether the user has DBA (database administrator) authority. DBA authority is very powerful, and should be restricted to as few user IDs as possible for security purposes.
publishauth (Y/N) Indicate whether the user has the SQL Remote publisher authority.
remotedbaauth (Y/N) Indicate whether the user has the SQL Remote remote DBA authority.
scheduleauth (Y/N) Indicate whether the user has SCHEDULE authority. This is currently not used by SQL Anywhere.
user_group (Y/N) Indicate whether the user is a group.
remarks A comment string.
When a database is initialized, the following user IDs are created:
There is no way to connect to the SYS or PUBLIC user IDs.
CREATE TABLE SYS.SYSUSERTYPE (type_id SMALLINT NOT NULL,creator SMALLINT NOT NULL,domain_id SMALLINT NOT NULL,nulls CHAR(1) NOT NULL,width SMALLINT NOT NULL,scale SMALLINT NOT NULL,type_name CHAR(128) NOT NULL,"default" LONG VARCHAR NULL,"check" LONG VARCHAR NULL,format_str CHAR(128),UNIQUE ( type_name ),PRIMARY KEY ( type_id ),FOREIGN KEY ( creator )REFERENCES SYS.SYSUSERPERM ( user_id ),FOREIGN KEY REFERENCES SYS.SYSDOMAIN)
Each row holds a description of a user-defined data type.
type_id A unique identifying number for the user-defined data type.
creator The owner of the data type.
domain_id Identifies the data type for the column by the data type number listed in the SYSDOMAIN table.
nulls A Y indicates that the user-defined data type does allow nulls. A N indicates that the data type does not allow nulls.
width This column contains the length of string columns, the precision of numeric columns, and the number of bytes of storage for all other data types.
scale The number of digits after the decimal point for numeric data type columns, and zero for all other data types.
type_name The name for the data type, which must be a valid identifier.
"default" The default value for the data type.
"check" The CHECK condition for the data type.
format_str Currently unused.