Table: SYS . DUMMY

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

This column is not used. It is present because a table cannot be created with no columns.

Table: dbo . RowGenerator

An undocumented table containing the numbers 1 to 255.

The numbers 1 to 255 are pre-loaded into this column.

Table: SYS . SYSARTICLE

Each row of SYSARTICLE describes an article in a SQL Remote publication.

The publication of which this article is a part.

Each article consists of columns and rows from a single table. This column contains the table ID for this table.

For articles that contain a subset of rows defined by a WHERE clause, this column contains the search condition.

For articles that contain a subset of rows defined by a SUBSCRIBE BY expression, this column contains the expression.

(undocumented) (Y/N) Indicates if the subscribe_by_expr contains a subquery.

Table: SYS . SYSARTICLECOL

Each row identifies a column in an article, identifying the column, the table it is in, and the publication it is part of.

A unique identifier for the publication of which the column is a part.

The table to which the column belongs.

The column identifier, from the SYSCOLUMN system table.

Table: SYS . SYSCOLLATION

This table contains the collation sequences available to SQL Anywhere. There is no way to modify the contents of this t

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.

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.

The name of the collation sequence.

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.

Table: SYS . SYSCOLPERM

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.

The table number for the table containing the column.

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.

The user number of the user ID granting the permission.

This column number, together with the table_id, identifies the column for which UPDATE permission has been granted.

The number in this column indicates the kind of column permission (REFERENCES, SELECT or UPDATE).

(Y/N). Indicates if the permission on the column was granted by the grantor to the grantee WITH GRANT OPTION.

Table: SYS . SYSCOLUMN

Each column in every table or view is described by one row in SYSCOLUMN.

The table number uniquely identifies the table or view to which this column belongs.

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.

(Y/N) Indicates whether this column is part of the primary key for the table.

Identify the data type for the column by the data type number listed in the SYSDOMAIN table.

(Y/N) Indicate whether the NULL value is allowed in this column.

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.

The number of digits after the decimal point for numeric data type columns, and zero for all other data types.

A self-tuning parameter for the optimizer. SQL Anywhere will learn from previous queries by adjusting guesses that are made by the optimizer.

The name of the column.

A comment string.

The default value for the column. This value is only used when an INSERT statement does not specify a value for the column.

Any CHECK condition defined on the column.

If the column is defined on a user-defined data type, the data type is held here.

Currently unused.

Table: SYS . SYSDOMAIN

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.

The unique number assigned to each data type. These numbers cannot be changed.

A string containing the data type normally found in the CREATE TABLE command, such as char or integer.

The ODBC data type. This corresponds to "data_type" in the Transact-SQL-compatibility DBO.SYSTYPES table.-

The number of significant digits that can be stored using this data type. The column value is NULL for non-numeric data types.

Table: SYS . SYSEXTENT

Undocumented, unused by SQL Anywhere and Adaptive Server Anywhere.

Undocumented, unused.

Undocumented, unused.

Undocumented, unused.

Undocumented, unused.

Undocumented, unused.

Table: SYS . SYSFILE

Every database consists of one or more operating system files. Each file is recorded in SYSFILE.

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.

The database name is stored when a database is created. This name is for informational purposes only.

Every file has a dbspace name that is unique. It is used in the CREATE TABLE command.

Table: SYS . SYSFKCOL

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.

The table number of the foreign table.

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.

This column number, together with the foreign_table_id, identify the foreign column description in SYSCOLUMN.

This column number, together with the primary_table_id obtained from SYSFOREIGNKEY, identify the primary column description in SYSCOLUMN.

Table: SYS . SYSFOREIGNKEY

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.

The table number of the foreign table.

Each foreign key has a foreign key number that is unique with respect to:

• The key number of all other foreign keys for the foreign table
• The key number of all foreign keys for the primary table
• The index number of all indexes for the foreign table

The table number of the primary table.

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.

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

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

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.

A comment string.

Table: SYS . SYSGROUP

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.

The user number of group.

The user number of a member.

Table: SYS . SYSINDEX

Each index in the database is described by one row in SYSINDEX. Each column in the index is described by one row in SYSIXCOL.

The table number uniquely identifies the table to which this index applies.

Each index for one particular table is assigned a unique index number.

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.

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.

(Y/N) 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.

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.

The name of the index. A user ID cannot have two indexes with the same name.

A comment string.

Table: SYS . SYSINFO

This table indicates the database characteristics as defined when the database was created using DBINIT. It always contains only one row.

The page size specified to DBINIT. The default value is 1024.

(Y/N) The value "Y" or "N" depending on whether the -e switch was used with DBINIT.

(Y/N) 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).

(Y/N) 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.

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.

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.

Table: SYS . SYSIXCOL

Every index has one row in SYSIXCOL for each column in the index.

Identifies the table to which the index applies.

Identifies in which index this column is used. Together, table_id and index_id identify one index described in SYSINDEX.

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.

The column number identifies which column is indexed. Together, table_id and column_id identify one column in SYSCOLUMN.

(A/D) Indicates whether this column in the index is kept in ascending or descending order.

Table: SYS . SYSLOGIN

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.

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.

s a foreign key to the system table SYSUSERPERM.

Contains a comment string

Table: SYS . SYSOPTION

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.

The user number to whom this option setting applies.

The name of the option.

The current setting for the named option.

Table: SYS . SYSPROCEDURE

Each procedure in the database is described by one row in SYSPROCEDURE.

Each procedure is assigned a unique number (the procedure number) that is the primary key for SYSPROCEDURE.

This user number identifies the owner of the procedure. The name of the user can be found by looking in SYSUSERPERM.

The name of the procedure. One creator cannot have two procedures with the same name.

The command used to create the procedure.

A comment string.

(Y/N) Holds a Y if the procedure is a primary data source in a Replication Server installation, or an N if not.

Table: SYS . SYSPROCPARM

Each parameter to a procedure in the database is described by one row in SYSPROCEDURE.

The procedure number uniquely identifies the procedure to which this parameter belongs.

Each procedure starts numbering parameters at 1. The order of parameter numbers corresponds to the order in which they were defined.

The type of parameter will be one of the following:

• 0 - variable - normal parameter
• 1 - result - result variable - used with procedure that return result sets
• 2 - SQLSTATE - SQLSTATE error value
• 3 - SQLCODE - SQLCODE error value

(Y/N) Indicate whether this parameter supplies a value to the procedure (IN or INOUT parameters).

(Y/N) Indicate whether this parameter returns a value from the procedure (OUT or INOUT parameters).

Identify the data type for the parameter by the data type number listed in the SYSDOMAIN table.

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.

The number of digits after the decimal point for numeric data type parameters, and zero for all other data types.

The name of the parameter.

A comment string.

The default value for the parameter, held as a string.

Table: SYS . SYSPROCPERM

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.

The procedure number uniquely identifies the procedure for which permission has been granted.

The user number of the user ID receiving the permission.

Table: SYS . SYSPUBLICATION

Each row describes a SQL Remote publication.

A unique identifying number for the publication.

The owner of the publication.

The name of the publication, which must be a valid identifier.

Descriptive comments.

Table: SYS . SYSREMOTETYPE

Each row describes one of the SQL Remote message types, including the publisher address.

An identification number for the message type.

The message type. There is a separate row for each of the following:

• FILE
• FTP
• MAPI
• VIM
• SMTP

The publisher's address for the message type type_name.

A comment string.

Table: SYS . SYSREMOTEUSER

Each row describes a userid with REMOTE permissions (a subscriber), together with the status of SQL Remote messages sent to and from that user.

The user ID of the user with REMOTE permissions.

(R/C) The column contains either an R to indicate a user granted REMOTE permissions, or a C to indicate a user granted CONSOLIDATE permissions.

Identifies which of the of the message systems supported by SQL Remote is to be used to send messages to this user.

The address to which SQL Remote messages are to be sent. The address must be appropriate for the address_type.

How frequently SQL Remote messages are to be sent.

The next time messages are to be sent to this user.

Messages are sent only to subscribers for whom log_send is greater than log_sent.

The time the most recent message was sent to this subscriber.

The log offset for the most recently sent operation.

The log offset for the most recently confirmed operation from this subscriber.

How many SQL Remote messages have been sent.

Counter to ensure messages are applied only once at the subscriber database.

The time the most recent message was received from this subscriber.

The log offset in the subscriber's database for the operation most recently received at the current database.

The log offset in the subscriber's database for the most recent operation for which a confirmation message has been sent.

How many messages have been received.

Counter to ensure messages are applied only once at the current database.

Table: SYS . SYSSQLSERVERTYPE

This table contains information relating to compatibility with Adaptive Server Enterprise.

A smallint field describing the Adaptive Server Enterprise user type

A smallint field describing the Adaptive Server Enterprise domain id.

Contains the Adaptive Server Enterprise type name.

A smallint field containing the Adaptive Server Anywhere primary domain id.

A smallint field containing the Adaptive Server Anywhere primary user type.

Table: SYS . SYSSUBSCRIPTION

Each row describes a subscription from one user ID (which must have REMOTE permissions) to one publication.

The identifier for the publication to which the user ID is subscribed.

The user ID that is subscribed to the publication.

For publications with a SUBSCRIBE BY expression, this column holds the matching value for this subscription.

The offset in the transaction log at which the subscription was created.

The offset in the transaction log at which the subscription was started.

Table: SYS . SYSTABLE

Each row of SYSTABLE describes one table or view in the database.

Each table or view is assigned a unique number (the table number) that is the primary key for SYSTABLE.

The file number indicates which database file contains the table. The file_id is a FOREIGN KEY for SYSFILE.

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

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.

The last page containing information for this table. The last_page is always 0 for a view.

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.

This user number identifies the owner of the table or view. The name of the user can be found by looking in SYSUSERPERM.

The name of the table or view. One creator cannot have two tables or views with the same name.

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.

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.

A comment string.

(Y/N) Holds a Y if the table is a primary data source in a Replication Server installation, or an N if not.

Table: SYS . SYSTABLEPERM

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.

• N - No, the grantee has not been granted this permission by the grantor.
• Y - Yes, the grantee has been given this permission by the grantor.
• G - The grantee has been given this permission. In addition, the grantee can grant the same permission to another user.

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.

The table number of the table or view to which the permissions apply.

The user number of the user ID receiving the permission.

The user number of the user ID granting the permission.

In the current version of SQL Anywhere, this table number is always the same as stable_id.

(Y/N/G) Indicate whether SELECT permission has been granted.

(Y/N/G) Indicate whether INSERT permission has been granted.

(Y/N/G) Indicate whether DELETE permission has been granted.

(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.)

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

(Y/N/G) Indicate whether ALTER permission has been granted.

(Y/N/G) Indicate whether REFERENCE permission has been granted.

Table: SYS . SYSTRIGGER

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

Each trigger is assigned a unique number (the trigger number) that is the primary key for SYSTRIGGER.

The table number uniquely identifies the table to which this trigger belongs.

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.

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.

• A - AFTER
• B - BEFORE

he 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).

The foreign table number identifies the table containing a foreign key definition which has a referential triggered action (such as ON DELETE CASCADE).

The foreign key number identifies the foreign key for the table referenced by foreign_table_id.

The action defined by a foreign key. This single character value corresponds to the action that was specified when the foreign key was created.

• C - CASCADE
• D - SET DEFAULT
• N - SET NULL
• R - RESTRICT

The name of the trigger. One table cannot have two triggers with the same name.

The command used to create the trigger.

A comment string.

Table: SYS . SYSTYPEMAP

The SYSTYPEMAP system table contains the compatibility mapping values for the SYSSQLSERVERTYPE system table.

Contains the Adaptive Server Enterprise user type.

Contains the Adaptive Server Anywhere 6.0 domain_id.

Contains the Adaptive Server Anywhere 6.0 user type.

This field describes whether or not the type can or cannot be null.

Table: SYS . SYSUSERMESSAGES

Each row holds a user-defined message for an error condition.

A unique identifying number for the error condition.

The user ID defining the message.

The message corresponding to the error condition.

Reserved.

Table: SYS . SYSUSERPERM

Each row of SYSUSERPERM describes one user ID.

DBA permissions required - SYSUSERPERM contains passwords, and so DBA permissions are required to SELECT from the table.

When a database is initialized, the following user IDs are created:

• SYS - The creator of all the system tables.
• PUBLIC - A special user ID used to record PUBLIC permissions.
• DBA - The database administrator user ID is the only usable user ID in an initialized system. The initial password is SQL.

There is no way to connect to the SYS or PUBLIC user IDs.

Each new user ID is assigned a unique number (the user number) that is the primary key for SYSUSERPERM.

A string containing the name for the user ID. Each userid must have a unique name.

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.

(Y/N) Indicate whether the user has RESOURCE authority. Resource authority is required to create tables.

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

(Y/N) Indicate whether the user has SCHEDULE authority. This is currently not used by SQL Anywhere.

(Y/N) Indicate whether the user has the SQL Remote publisher authority.

(Y/N) Indicate whether the user has the SQL Remote remote DBA authority.

(Y/N) Indicate whether the user is a group.

A comment string.

Table: SYS . SYSUSERTYPE

Each row holds a description of a user-defined data type.

A unique identifying number for the user-defined data type.

The owner of the data type.

Identifies the data type for the column by the data type number listed in the SYSDOMAIN table.

(Y/N) A Y indicates that the user-defined data type does allow nulls. A N indicates that the data type does not allow nulls.

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.

The number of digits after the decimal point for numeric data type columns, and zero for all other data types.

The name for the data type, which must be a valid identifier.

The default value for the data type.

The CHECK condition for the data type.

Currently unused.