Transact-SQL compatibility views

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

In Adaptive Server Enterprise, there is a single master database containing a set of system tables holding 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 Sybase IQ, 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 Sybase IQ 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 Sybase IQ system tables and views are owned by the special user SYS and exist separately in each database.

To assist in preparing compatible applications, Sybase IQ 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 Sybase IQ context, the view is empty, containing only the column names and data types.

Table 8-1, Table 8-2, and Table 8-3 list the Adaptive Server Enterprise system tables and their implementation in the Sybase IQ system catalog. The owner of all tables is dbo in each DBMS.

Tables in each Adaptive Server Enterprise database

Table 8-1: Tables in each ASE database

Table name

Description

Data?

Supported by IQ?

sysalternates

One row for each user mapped to a database user

No

No

syscolumns

One row for each column in a table or view, and for each parameter in a procedure. In Sybase IQ, use the owner name dbo when querying, i.e. dbo.syscolumns.

Yes

Yes

syscomments

One or more rows for each view, rule, default, and procedure, giving SQL definition statement.

Yes

Yes

sysconstraints

One row for each referential and check constraint associated with a table or column.

No

No

sysdepends

One row for each procedure, view, or table that is referenced by a procedure, view.

No

No

sysindexes

One row for each clustered or nonclustered index, and one row for each table with no indexes, and an additional row for each table containing text or image data. In Sybase IQ, use the owner name dbo when querying, i.e. dbo.sysindexes.

Yes

Yes

sysiqobjects

One row for each system table, user table, view, procedure, trigger, event, join index, constraint, domain (sysdomain), domain (sysusertype), column, and index.

Yes

Yes

sysiqvindex

One row for each non-fp iq index.

Yes

Yes

syskeys

One row for each primary, foreign, or common key; set by user (not maintained by Adaptive Server Enterprise).

No

No

syslogs

Transaction log.

No

No

sysobjects

One row for each table, view, procedure, rule, default, log, and (in tempdb only) temporary object.

Contains compatible data only

Yes

sysprocedures

One row for each view, rule, default, and procedure, giving internal definition.

No

No

sysprotects

User permissions information.

No

No

sysreferences

One row for each referential integrity constraint declared on a table or column.

No

No

sysroles

Maps server-wide roles to local database groups.

No

No

syssegments

One row for each segment (named collection of disk pieces).

No

No

systhresholds

One row for each threshold defined for the database.

No

No

systypes

One row for each system-supplied and user-defined data type.

Yes

Yes

sysusers

One row for each user allowed in the database.

Yes

Yes

Tables in the Adaptive Server Enterprise master database

Table 8-2: ASE master database tables

Table name

Description

Data?

Supported by IQ?

syscharsets

One row for each character set or sort order

No

No

sysconfigures

One row for each configuration parameter that can be set by a user

No

No

syscurconfigs

Information about configuration parameters currently being used by the server

No

No

sysdatabases

One row for each database on the server

No

No

sysdevices

One row for each tape dump device, disk dump device, disk for databases, and disk partition for databases

No

No

sysengines

One row for each server currently online

No

No

syslanguages

One row for each language (except U.S. English) known to the server

No

No

syslocks

Information about active locks

No

No

sysloginroles

One row for each server login that possesses a system-defined role

No

No

syslogins

One row for each valid user account

Yes

Yes

sysmessages

One row for each system error or warning

No

No

sysprocesses

Information about server processes

No

No

sysremotelogins

One row for each remote user

No

No

syssrvroles

One row for each server-wide role

No

No

sysservers

One row for each remote server

No

No

sysusages

One row for each disk piece allocated to a database

No

No

Tables in the Adaptive Server Enterprise sybsecurity database

Table 8-3: ASE sybsecurity database tables

Table name

Description

Data?

Supported by IQ?

sysaudits

One row for each audit record

No

No

sysauditoptions

One row for each global audit option

No

No