Catalog changes

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. However, most compatibility views are provided.

The following table provides a complete mapping of the catalog from SQL 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 of 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.

Note

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	SYSARTICLE system view For pre-10.0.0 compatibility: SYSARTICLES consolidated view.
SYSARTICLECOL / SYSARTICLECOL	ISYSARTICLECOL	SYSARTICLECOL system view For pre-10.0.0 compatibility, see SYSARTICLECOLS consolidated view.
SYSATTRIBUTE / -	ISYSATTRIBUTE	-
SYSATTRIBUTENAME / -	ISYSATTRIBUTENAME	-
SYSCAPABILITY / SYSCAPABILITIES	ISYSCAPABILITY	SYSCAPABILITY system view SYSCAPABILITIES consolidated view
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	SYSEXTERNLOGIN system view
SYSFILE / -	ISYSFILE	SYSFILE compatibility view (deprecated)
SYSFKCOL / -	ISYSIDXCOL	SYSIDXCOL system view 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	SYSGROUP system view and SYSGROUPS consolidated view
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	SYSIDXCOL system view 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	SYSPROCEDURE system view The SYSPROCEDURES view has been renamed 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 SYSREMOTETYPES 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	SYSSERVER system view
- / -	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, SYSSYNCS 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	SYSTAB system view 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

Summary of new views

System view name	Link to more information
SYSDEPENDENCY	Each row in the SYSDEPENDENCY system view describes a dependency between two database objects.
SYSFKEY	Each row in the SYSFKEY system view describes a foreign key constraint in the system.
SYSIDX	Each row in the SYSIDX system table defines a logical index in the database.
SYSIDXCOL	Each row in the SYSIDXCOL system view describes one column of an index described in the SYSIDX 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.
SYSMVOPTION	Each row in the SYSMVOPTION system view describes the setting of one option value for a materialized view.
SYSMVOPTIONNAME	Each row in the SYSMVOPTIONNAME system view contains the name of an option defined in the SYSMVOPTION system view.
SYSOBJECT	Each row in the SYSOBJECT system view describes an object. Examples of database objects include tables, views, columns, indexes, and procedures.
SYSPHYSIDX	Each row in the SYSPHYSIDX system view defines a physical index in the database.
SYSPROCS	The SYSPROCS system view replaces the former SYSPROCEDURES view.
SYSPROXYTAB	Each row of the SYSPROXYTAB system view describes the remote parameters of one proxy table.
SYSREMARK	Each row in the SYSREMARK system view describes a remark (or comment) for an object.
SYSSOURCE	Each row in the SYSSOURCE system view contains the source for an object listed in the ISYSOBJECT system table.
SYSSYNCSCRIPT	Each row in the SYSSYNCSCRIPT system view identifies a stored procedure for MobiLink scripted upload.
SYSTABCOL	The SYSTABCOL system view contains one row for each column of each table and view in the database.
SYSUSER	Each row in the SYSUSER system view describes a user in the database.
SYSUSERAUTHORITY	Each row of SYSUSERAUTHORITY system view describes an authority granted to one user ID.

Summary of deprecated tables or views

Following is a list of catalog objects that are deprecated. Usually, the object in a table in previous versions 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.
SYSCOLLATIONMAPPINGS system table	Collation mapping information is now stored as database properties.
SYSCOLUMN system table	Use the SYSTABCOL system view instead.
SYSFKCOL system table	Use the SYSFKEY system view instead.
SYSFOREIGNKEY system table	Use the SYSFKEY system view instead.
SYSINDEX system table	Use the SYSIDX system view instead.
SYSIXCOL system table	Use the SYSIDXCOL system view instead.
SYSTABLE system table	Use the SYSTAB system view instead.
SYSUSERAUTH system view	Use the SYSUSERAUTHORITY system view instead.
SYSUSERPERM system table	Use the SYSUSERAUTHORITY system view instead.
SYSUSERLIST system view	Use the SYSUSERAUTHORITY system view instead.
SYSUSERPERMS system view	Use the SYSUSERAUTHORITY system view instead.

Summary of removed or renamed tables or views

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.
SYSATTRIBUTENAME system table	Use the SYSIDX and SYSPHYSIDX system views instead.
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.
SYSLOGIN system table	The SYSLOGIN table has been replaced by the SYSLOGINMAP system view, with some changes.
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.
SYSSERVERS	Renamed to SYSSERVER.
SYSUSERMESSAGES	Renamed to SYSUSERMESSAGE.

Change to columns in system tables and system views

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.
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.
SYSREMOTEOPTION view You can no longer select from SYSREMOTEOPTION. Use SYSREMOTEOPTIONS or SYSREMOTEOPTION2 instead.
SYSJAR, SYSJARCOMPONENT, and SYSJAVACLASS views The create_time column has been removed. However the creation time information is available in SYSOBJECT.create_time.
SYSFILE system view The store_type column is now an INTEGER.
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.
SYSPROCPARMS view SYSPROCPARM.width has changed from a SMALLINT to an UNSIGNED INT.
SYSREMOTEUSER view The log_send, log_sent, confirm_sent, log_received, and confirm_received columns are now UNSIGNED BIGINT.
SYSSUBSCRIPTION view The created and started columns are now UNSIGNED BIGINT.
SYSSYNC view The progress, created, and log_sent columns are now UNSIGNED BIGINT.

Discuss this page in DocCommentXchange.