Contents Index SYSVIEWS system view System Procedures and Functions

ASA SQL Reference
  System Views

Views for Transact-SQL compatibility


Adaptive Server Enterprise and Adaptive Server Anywhere have different system catalogs, reflecting the different uses for the two products.

In Adaptive Server Enterprise, a single master database contains a set of system tables, which information that applies to all databases on the server. Many databases may exist within the master database, and each has additional system tables associated with it.

In Adaptive Server Anywhere, each database exists independently, and contains its own system tables. There is no master database that contains system information on a collection of databases. Each server may run several databases at a time, dynamically loading and unloading each database as needed.

The Adaptive Server Enterprise and Adaptive Server Anywhere system catalogs are different. The Adaptive Server Enterprise system tables and views are owned by the special user dbo, and exist partly in the master database, partly in the sybsecurity database, and partly in each individual database; the Adaptive Server Anywhere system tables and views are owned by the special user SYS and exist separately in each database.

To assist in preparing compatible applications, Adaptive Server Anywhere provides a set of views owned by the special user dbo, which correspond to the Adaptive Server Enterprise system tables and views. Where architectural differences make the contents of a particular Adaptive Server Enterprise table or view meaningless in a Adaptive Server Anywhere context, the view is empty, containing just the column names and data types.

The following tables list the Adaptive Server Enterprise system tables and their implementation in the Adaptive Server Anywhere system catalog. The owner of all tables is dbo in each DBMS.

Tables existing in each Adaptive Server Enterprise database 
Table name Description Data?
sysalternates One row for each user mapped to a database user No
syscolumns One row for each column in a table or view, and for each parameter in a procedure Yes
syscomments One or more rows for each view, rule, default, trigger, and procedure, giving the SQL definition statement Yes
sysconstraints One row for each referential or check constraint associated with a table or column No
sysdepends One row for each procedure, view, or table that is referenced by a procedure, view, or trigger No
sysindexes One row for each clustered or nonclustered index, one row for each table with no indexes, and an additional row for each table containing text or image data. Yes
syskeys One row for each primary, foreign, or common key; set by the user (not maintained by Adaptive Server Enterprise) No
syslogs Transaction log No
sysobjects One row for each table, view, procedure, rule, trigger default, log, or (in tempdb only) temporary object Contains compatible data only
sysprocedures One row for each view, rule, default, trigger, or procedure, giving the internal definition No
sysprotects User permissions information No
sysreferences One row for each referential integrity constraint declared on a table or column No
sysroles Maps server-wide roles to local database groups No
syssegments One row for each segment (named collection of disk pieces) No
systhresholds One row for each threshold defined for the database No
systypes One row for each system-supplied or user-defined data type Yes
sysusermessages One row for each user-defined message Yes (this is an Adaptive Server Anywhere system table)
sysusers One row for each user allowed in the database Yes
Tables existing in the Adaptive Server Enterprise master database 
Table name Description Data?
syscharsets One row for each character set or sort order No
sysconfigures One row for each user-settable configuration parameter No
syscurconfigs Information about configuration parameters currently being used by the server No
sysdatabases One row for each database on the server No
sysdevices One row for each tape dump device, disk dump device, disk for databases, or disk partition for databases No
sysengines One row for each server currently online No
syslanguages One row for each language (except U.S. English) known to the server No
syslocks Information about active locks No
sysloginroles One row for each server login that possesses a system-defined role No
syslogins One row for each valid user account Yes
sysmessages One row for each system error or warning No
sysprocesses Information about server processes No
sysremotelogins One row for each remote user No
sysservers One row for each remote server No
syssrvroles One row for each server-wide role No
sysusages One row for each disk piece allocated to a database No
Tables existing in the Adaptive Server Enterprise sybsecurity database 
Table name Description Data?
sysaudits One row for each audit record No
sysauditoptions One row for each global audit option No

Contents Index SYSVIEWS system view System Procedures and Functions