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 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 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: 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 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	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, 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	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. 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.

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

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

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