The catalog has undergone major changes in version 10.0.0. The most significant change is that system tables have been renamed to include an I at the beginning of their name. If you attempt to access the system tables, you will receive a permission denied error. Information in the system tables is made available through system views. There is one system view per system table, and, for backward compatibility, the system view names coincide with the table names from previous versions of SQL Anywhere. For example, in 9.0.2, there was a system table called SYS.SYSARTICLE. In version 10.0.0 that system table is now called SYS.ISYSARTICLE, and a corresponding system view, SYS.SYSARTICLE.
The catalog now also contains consolidated views. These are views which provide commonly needed joins from two or more tables or views. Most of the consolidated views were present as system views in previous releases.
Some system tables and views have been deprecated or removed from the catalog. In most cases, however, compatibility views are provided.
The following table provides a complete mapping of the catalog from Adaptive Server Anywhere 9.0.2 to SQL Anywhere 10.0.0. The first column, 9.0.2 system table/view, shows names of the 9.0.2 system tables, followed by a forward slash ('/'), and then the name the 9.0.2 associated view(s). The middle column, 10.0.0 system table, contains the 10.0.0 table name. The final column, 10.0.0 system view, contains the associated 10.0.0 view name(s), as well as compatibility notes.
A dash (-) in any of the columns indicates that there is no equivalent object. For example, a new table in the catalog for the 10.0.0 release results in a dash for the table in the 9.0.2 column.
| 9.0.2 system table/view | 10.0.0 system table | 10.0.0 system view |
|---|---|---|
| DUMMY / - | DUMMY | - |
| RowGenerator / - | RowGenerator | - |
| SYSARTICLE / SYSARTICLES | ISYSARTICLE |
For pre-10.0.0 compatibility: SYSARTICLES consolidated view. |
| SYSARTICLECOL / SYSARTICLECOL | ISYSARTICLECOL |
For pre-10.0.0 compatibility: SYSARTICLECOLS consolidated view. |
| SYSATTRIBUTE / - | ISYSATTRIBUTE | - |
| SYSATTRIBUTENAME / - | ISYSATTRIBUTENAME | - |
| SYSCAPABILITY / SYSCAPABILITIES | ISYSCAPABILITY | |
| SYSCAPABILITYNAME / - | ISYSCAPABILITYNAME | SYSCAPABILITYNAME system view |
| - / SYSCATALOG | SYSCATALOG consolidated view | |
| SYSCHECK / - | ISYSCHECK | SYSCHECK system view |
| - / SYSCOLAUTH | - | SYSCOLAUTH consolidated view |
| SYSCOLLATION / - | - | SYSCOLLATION compatibility view (deprecated) |
| SYSCOLLATIONMAPPINGS / - | - | SYSCOLLATIONMAPPINGS compatibility view (deprecated) |
| SYSCOLPERM / - | ISYSCOLPERM | SYSCOLPERM system view |
| SYSCOLSTAT / SYSCOLSTATS | ISYSCOLSTAT | SYSCOLSTAT system view and SYSCOLSTATS consolidated view |
| SYSCOLUMN / SYSCOLUMNS | ISYSTABCOL |
SYSTABCOL system view and SYSCOLUMNS consolidated view For pre-10.0.0 compatibility: SYSCOLUMN compatibility view (deprecated) |
| SYSCONSTRAINT / - | ISYSCONSTRAINT | SYSCONSTRAINT system view |
| - / - | ISYSDEPENDENCY | SYSDEPENDENCY system view |
| SYSDOMAIN / - | ISYSDOMAIN | SYSDOMAIN system view |
| SYSEVENT / - | ISYSEVENT | SYSEVENT system view |
| SYSEVENTTYPE / - | ISYSEVENTTYPE | SYSEVENTTYPE system view |
| SYSEXTENT / - | - | - |
| SYSEXTERNLOGINS / - | ISYSEXTERNLOGIN | |
| SYSFILE / - | ISYSFILE | SYSFILE compatibility view (deprecated) |
| SYSFKCOL / - | ISYSIDXCOL |
For pre-10.0.0 compatibility: SYSFKCOL compatibility view (deprecated) |
| SYSFOREIGNKEY / SYSFOREIGNKEYS | ISYSFKEY |
SYSFKEY system view and SYSFOREIGNKEYS consolidated view. For pre-10.0.0 compatibility: SYSFOREIGNKEY compatibility view (deprecated) |
| - / SYSGROUPS | ISYSGROUP | |
| SYSHISTORY / - | ISYSHISTORY | SYSHISTORY system view |
| SYSINDEX / SYSINDEXES | ISYSIDX |
SYSIDX system view and SYSINDEXES consolidated view For pre-10.0.0 compatibility: SYSINDEX compatibility view (deprecated) |
| SYSINFO / - | - | SYSINFO compatibility view (deprecated) |
| SYSIXCOL / - | ISYSIDXCOL |
For pre-10.0.0 compatibility: SYSIXCOL compatibility view (deprecated) |
| SYSJAR / - | ISYSJAR | SYSJAR system view |
| SYSJARCOMPONENT / - | ISYSJARCOMPONENT | SYSJARCOMPONENT system view |
| SYSJAVACLASS / - | ISYSJAVACLASS | SYSJAVACLASS system view |
| SYSLOGIN / - | ISYSLOGINMAP | SYSLOGINMAP system view |
| SYSOPTBLOCK / - | - | system use only |
| - / - | ISYSMVOPTION | SYSMVOPTION system view |
| - / - | ISYSMVOPTIONNAME | SYSMVOPTIONNAME system view |
| - / - | ISYSOBJECT | SYSOBJECT system view |
| SYSOPTION / SYSOPTIONS | ISYSOPTION | SYSOPTION system view and SYSOPTIONS consolidated view |
| SYSOPTJOINSTRATEGY / SYSOPTJOINSTRATEGIES | - | system use only |
| SYSOPTORDER / SYSOPTORDERS | - | system use only |
| SYSOPTQUANTIFIER / - | - | system use only |
| SYSOPTREQUEST / - | - | system use only |
| SYSOPTREWRITE / - | - | system use only |
| SYSOPTSTAT / - | ISYSOPTSTAT | SYSOPTSTAT system view |
| - | ISYSPHYSIDX | SYSPHYSIDX system view |
| - / SYSPROCAUTH | - | SYSPROCAUTH consolidated view |
| SYSPROCEDURE / SYSPROCEDURES | ISYSPROCEDURE |
The SYSPROCEDURES view has been renamed to SYSPROCS. See SYSPROCS consolidated view. |
| SYSPROCPARM / SYSPROCPARMS | ISYSPROCPARM | SYSPROCPARM system view and SYSPROCPARMS consolidated view |
| SYSPROCPERM / - | ISYSPROCPERM | SYSPROCPERM system view |
| - | ISYSPROXYTAB | SYSPROXYTAB system view |
| SYSPUBLICATION / SYSPUBLICATIONS | ISYSPUBLICATION | SYSPUBLICATION system view and SYSPUBLICATIONS consolidated view |
| - / - | ISYSREMARK | SYSREMARK system view |
| SYSREMOTEOPTION / SYSREMOTEOPTIONS, SYSREMOTEOPTION2 | ISYSREMOTEOPTION | SYSREMOTEOPTION system view, SYSREMOTEOPTION2 consolidated view, and SYSREMOTEOPTIONS consolidated view |
| SYSREMOTEOPTIONTYPE / - | ISYSREMOTEOPTIONTYPE | SYSREMOTEOPTIONTYPE system view |
| SYSREMOTETYPE / SYSREMOTETYPES | ISYSREMOTETYPE | SYSREMOTETYPE system view and SYSREMOTETYPES consolidated view |
| SYSREMOTEUSER / SYSREMOTEUSERS | ISYSREMOTEUSER | SYSREMOTEUSER system view and SYSREMOTEUSERS consolidated view |
| SYSSCHEDULE / - | ISYSSCHEDULE | SYSSCHEDULE system view |
| SYSSERVERS / - | ISYSSERVER | |
| - / - | ISYSSOURCE | SYSSOURCE system view |
| SYSSQLSERVERTYPE / - | ISYSSQLSERVERTYPE | SYSSQLSERVERTYPE system view |
| SYSSUBSCRIPTION / SYSSUBSCRIPTIONS | ISYSSUBSCRIPTION | SYSSUBSCRIPTION system view and SYSSUBSCRIPTIONS consolidated view |
| SYSSYNC / SYSSYNCS, SYSSYNC2 | ISYSSYNC | SYSSYNC system view, SYSSYNC2 consolidated view, and SYSSYNC2 consolidated view |
| - | ISYSSYNCSCRIPT | SYSSYNCSCRIPT system view and SYSSYNCSCRIPTS consolidated view |
| - / SYSSYNCSUBSCRIPTIONS | - | SYSSYNCSUBSCRIPTIONS consolidated view |
| - / SYSSYNCUSERS | - | SYSSYNCUSERS consolidated view |
| - / SYSTABAUTH | - | SYSTABAUTH consolidated view |
| SYSTABLE / - | ISYSTAB |
For pre-10.0.0 compatibility: SYSTABLE compatibility view (deprecated) |
| - | ISYSTABCOL | SYSTABCOL system view |
| SYSTABLEPERM / - | ISYSTABLEPERM | SYSTABLEPERM system view |
| SYSTRIGGER / SYSTRIGGERS | ISYSTRIGGER | SYSTRIGGER system view and SYSTRIGGERS consolidated view |
| SYSTYPEMAP / - | ISYSTYPEMAP | SYSTYPEMAP system view |
| - | ISYSUSER | SYSUSER system view |
| - / SYSUSERAUTH | ISYSUSERAUTHORITY | SYSUSERAUTHORITY system view and SYSUSERAUTH compatibility view (deprecated) |
| - / SYSUSERLIST | SYSUSERAUTHORITY system view and SYSUSERLIST compatibility view (deprecated) | |
| SYSUSERMESSAGES / - | ISYSUSERMESSAGE | SYSUSERMESSAGE system view |
| - / SYSUSEROPTIONS | - | SYSUSEROPTIONS consolidated view |
| SYSUSERPERM / SYSUSERPERMS | - |
Data now located in the ISYSUSER and ISYSUSERAUTHORITY system tables. See: SYSUSER system view and SYSUSERAUTHORITY system view For pre-10.0.0 compatibility: SYSUSERPERM compatibility view (deprecated) and SYSUSERPERMS compatibility view (deprecated) |
| SYSUSERTYPE / - | ISYSUSERTYPE | SYSUSERTYPE system view |
| - / SYSVIEWS | ISYSVIEW | SYSVIEW system view and SYSVIEWS consolidated view |
| SYSWEBSERVICE / - | ISYSWEBSERVICE | SYSWEBSERVICE system view |
| System view name | Link to more information |
|---|---|
| SYSDEPENDENCY | Each row in the SYSDEPENDENCY system view describes a dependency between two database objects. See SYSDEPENDENCY system view |
| SYSFKEY | Each row in the SYSFKEY system view describes a foreign key constraint in the system. See SYSFKEY system view. |
| SYSIDX | Each row in the SYSIDX system table defines a logical index in the database. See SYSIDX system view. |
| SYSIDXCOL | Each row in the SYSIDXCOL system view describes one column of an index described in the SYSIDX system view. See SYSIDXCOL system view. |
| SYSLOGINMAP | The SYSLOGINMAP system view contains all the user names that can be used to connect to the database using either an integrated login, or Kerberos login. See SYSLOGINMAP system view. |
| SYSMVOPTION | Each row in the SYSMVOPTION system view describes the setting of one option value for a materialized view. See SYSMVOPTION system view. |
| SYSMVOPTIONNAME | Each row in the SYSMVOPTIONNAME system view contains the name of an option defined in the SYSMVOPTION system view. See SYSMVOPTIONNAME system view. |
| SYSOBJECT | Each row in the SYSOBJECT system view describes an object. Examples of database objects include tables, views, columns, indexes, and procedures. See SYSOBJECT system view. |
| SYSPHYSIDX | Each row in the SYSPHYSIDX system view defines a physical index in the database. See SYSPHYSIDX system view. |
| SYSPROCS | The SYSPROCS system view replaces the former SYSPROCEDURES view. See SYSPROCS consolidated view. |
| SYSPROXYTAB | Each row of the SYSPROXYTAB system view describes the remote parameters of one proxy table. See SYSPROXYTAB system view. |
| SYSREMARK | Each row in the SYSREMARK system view describes a remark (or comment) for an object. See SYSREMARK system view. |
| SYSSOURCE | Each row in the SYSSOURCE system view contains the source for an object listed in the ISYSOBJECT system table. See SYSSOURCE system view. |
| SYSSYNCSCRIPT | Each row in the SYSSYNCSCRIPT system view identifies a stored procedure for MobiLink scripted upload. See SYSSYNCSCRIPT system view. |
| SYSTABCOL | The SYSTABCOL system view contains one row for each column of each table and view in the database. See SYSTABCOL system view. |
| SYSUSER | Each row in the SYSUSER system view describes a user in the database. See SYSUSER system view. |
| SYSUSERAUTHORITY | Each row of SYSUSERAUTHORITY system view describes an authority granted to one user ID. See SYSUSERAUTHORITY system view. |
Following is a list of catalog objects that are deprecated. In most cases, the object was a table in previous versions but is now a compatibility view. Referencing these objects does not result in an error; however, for future compatibility, you are encouraged to change your applications to point to the suggested object(s) instead.
| Deprecated table or view | Transition information |
|---|---|
| SYSCOLLATION system table | Collation mapping information is now stored as database properties. See SYSCOLLATION compatibility view (deprecated). |
| SYSCOLLATIONMAPPINGS system table | Collation mapping information is now stored as database properties. See SYSCOLLATIONMAPPINGS compatibility view (deprecated). |
| SYSCOLUMN system table | Use the SYSTABCOL system view instead. See SYSTABCOL system view and SYSCOLUMN compatibility view (deprecated). |
| SYSFKCOL system table | Use the SYSFKEY system view instead. See SYSFKEY system view and SYSFKCOL compatibility view (deprecated). |
| SYSFOREIGNKEY system table | Use the SYSFKEY system view instead. See SYSFKEY system view and SYSFOREIGNKEY compatibility view (deprecated). |
| SYSINDEX system table | Use the SYSIDX system view instead. See SYSIDX system view and SYSINDEX compatibility view (deprecated). |
| SYSIXCOL system table | Use the SYSIDXCOL system view instead. See SYSIDXCOL system view and SYSIXCOL compatibility view (deprecated). |
| SYSTABLE system table | Use the SYSTAB system view instead. See SYSTAB system view and SYSTABLE compatibility view (deprecated). |
| SYSUSERAUTH system view | Use the SYSUSERAUTHORITY system view instead. See SYSUSERAUTHORITY system view and SYSUSERAUTH compatibility view (deprecated). |
| SYSUSERPERM system table | Use the SYSUSERAUTHORITY system view instead. See SYSUSERAUTHORITY system view and SYSUSERPERM compatibility view (deprecated). |
| SYSUSERLIST system view | Use the SYSUSERAUTHORITY system view instead. See SYSUSERAUTHORITY system view and SYSUSERLIST compatibility view (deprecated). |
| SYSUSERPERMS system view | Use the SYSUSERAUTHORITY system view instead. See SYSUSERAUTHORITY system view and SYSUSERPERMS compatibility view (deprecated). |
Following is a list of catalog objects that are no longer present in the catalog. Referencing these objects results in an error.
| Removed table or view | Transition information |
|---|---|
| SYSATTRIBUTE system table | Use the SYSTAB and SYSPHYSIDX system views instead. Information about percent free and clustered index is now maintained in the ISYSTAB system table. Information about key values, key distance, leaf pages, and depth is now stored in the ISYSPHYSIDX system table. See SYSTAB system view and SYSPHYSIDX system view. |
| SYSATTRIBUTENAME system table | Use the SYSIDX and SYSPHYSIDX system views instead. See SYSIDX system view and SYSPHYSIDX system view. |
| SYSEXTENT system table | The SYSEXTENT table is no longer available in the catalog in SQL Anywhere version 10.0.0 and later. This table was previously unused. |
| SYSEXTERNLOGINS | Renamed to SYSEXTERNLOGIN. See SYSEXTERNLOGIN system view. |
| SYSLOGIN system table | The SYSLOGIN table has been replaced by the SYSLOGINMAP system view, with some changes. See SYSLOGINMAP system view. |
| SYSOPTBLOCK | This table was for internal use only. |
| SYSOPTJOINSTRATEGY | This table was for internal use only. |
| SYSOPTJOINSTRATEGIES | This view was for internal use only. |
| SYSOPTORDER | This table was for internal use only. |
| SYSOPTORDERS | This view was for internal use only. |
| SYSOPTQUANTIFIER | This table was for internal use only. |
| SYSOPTREQUEST | This table was for internal use only. |
| SYSOPTREWRITE | This table was for internal use only. |
| SYSPROCEDURES view | Use the SYSPROCS consolidated view instead. See SYSPROCS consolidated view. |
| SYSSERVERS | Renamed to SYSSERVER. See SYSSERVER system view. |
| SYSUSERMESSAGES | Renamed to SYSUSERMESSAGE. See SYSUSERMESSAGE system view. |
There have been numerous changes to columns in system tables and views. With the exception of the information below, all of the changes consist of adding new columns, or removing unused columns, neither of which impact your applications.
SYSCOLUMN and SYSCOLUMNS views The width column for both of these views has changed from a SMALLINT to an UNSIGNED INT. See SYSCOLUMN compatibility view (deprecated) and SYSCOLUMNS consolidated view.
SYSCONSTRAINT view The previous SYSCONSTRAINT system table has been replaced by a new system table, ISYSCONSTRAINT, with a corresponding SYSCONSTRAINT system view. References to SYSCONSTRAINT now use the new system view, which is significantly different in this release. To see the contents of the SYSCONSTRAINT system view, see SYSCONSTRAINT system view.
SYSREMOTEOPTION view You can no longer select from SYSREMOTEOPTION. Use SYSREMOTEOPTIONS or SYSREMOTEOPTION2 instead. See SYSREMOTEOPTIONS consolidated view or SYSREMOTEOPTION2 consolidated view.
SYSJAR, SYSJARCOMPONENT, and SYSJAVACLASS views The create_time column has been removed. However the creation time information is available in SYSOBJECT.create_time. See SYSOBJECT system view.
SYSFILE system view The store_type column is now an INTEGER. See SYSFILE compatibility view (deprecated).
SYSPROCPARM and SYSLOGINMAP views The remarks column has been removed from these views. Also, the width column in SYSPROCPARM has changed from a SMALLINT to an UNSIGNED INT. See SYSPROCPARM system view and SYSLOGINMAP system view.
SYSPROCPARMS view SYSPROCPARM.width has changed from a SMALLINT to an UNSIGNED INT. See SYSPROCPARMS consolidated view.
SYSREMOTEUSER view The log_send, log_sent, confirm_sent, log_received, and confirm_received columns are now UNSIGNED BIGINT. See SYSREMOTEUSER system view.
SYSSUBSCRIPTION view The created and started columns are now UNSIGNED BIGINT. See SYSSUBSCRIPTION system view.
SYSSYNC view The progress, created, and log_sent columns are now UNSIGNED BIGINT. See SYSSYNC system view.
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |