These are very brief descriptions for Adaptive Server system tables. See Reference Manual: Tables for complete information, or the System Tables Diagram poster for a visual presentation of tables, columns, and relationships between tables.
master database only – contains one row for each update of the maximum number of licenses used in Adaptive Server per 24-hour period. Columns: status, logtime, maxlicenses.
All databases – contains one row for each Adaptive Server user that is mapped or aliased to a user of the current database. Columns: suid, altsuid
Scratch database – maps page numbers in an archive database to the actual page within either the database dump and its stripes, or the modified pages section. Columns: dbid, altsuid, lstart, start, size, vstart, vdevno, segmap
All databases – defines properties of objects. Columns: class, attribute, object_type, object_cinfo, object_cinfo2, object, object_info1, object_info2, object_info3, int_value, char_value, text_value, image_value, comments
sybsecurity database – contains one row for each server-wide audit option and indicates the current setting for that option. Columns: num, val, minval, maxval, name, sval, comment
sybsecurity database – contains the audit trail. Columns: event, eventmod, spid, eventtime, sequence, suid, dbid, objid, xactid, loginname, dbname, objname, objowner, extrainfo, nodeid, instanceid
master database only – contains one row for each character set and sort order defined for use by Adaptive Server. Columns: type, id, csid, status, name, description, definition, sortfile
All databases – contains one row for every column in every table and view, and a row for each parameter in a procedure. Columns: id, number, colid, status, type, length, offset, usertype, cdefault, domain, name, printfmt, prec, scale, remote_type, remote_name, xstatus, xtype, xdbid, accessrule, status2, status3, computedcol, encrtype, encrlen, encrykeyid, encrykeydb, encrdate
All databases – contains entries for each view, rule, default, trigger, table constraint, partition, procedure, computed column, function-based index key, and other forms of compiled objects. Columns: id, number, colid, texttype, language, text, colid2, status, partitionid
master database only – contains one row for each configuration parameter that can be set by the user. Columns: config, value, comment, status, name, parent, value2, value3, value4, instanceid
All databases – whenever a user declares a new check constraint or referential constraint using create table or alter table, Adaptive Server inserts a row into the sysconstraints table. The row remains until a user executes alter table to drop the constraint. Columns: colid , constrid , tableid , error , status , spare2
sybsystemdb database only – contains information about remote Adaptive Servers participating in distributed transactions (remote participants) and their coordination states. Columns: participant, starttime, coordtype, owner, protocol, state, bootcount, dbid, logvers, spare, status, xactkey, gtrid, partdata, srvname, nodeid, instanceid
master database only – contains an entry for each of the configuration parameters, as does sysconfigures, but with the current values rather than the default values. In addition, it contains four rows that describe the configuration structure. Columns: config, value, comment, status, value2, defvalue, minimum_value, maximum_value, memory_used, display_level, datatype, message_num, apf_percent, nodeid, instanceid, type
master database only – contains one row for each database in Adaptive Server. When Adaptive Server is installed, sysdatabases contains entries for the master database, the model database, the sybsystemprocs database, and the tempdb database. If you have installed auditing, it also contains an entry for the sybsecurity database. Columns: name, dbid, suid, status, version, logptr, crdate, dumptrdate, status2, audflags, deftabaud, defvwaud, defpraud, def_remote_type, def_remote_loc, status3, status4, audflags2, instanceid, durability
All databases – contains one row for each procedure, view, or table that is referenced by a procedure, view, or trigger. Columns: id, number, depid, depnumber, status, selall, resultobj, readobj, columns
master database only – contains one row for each tape dump device, disk dump device, disk for databases, and disk partition for databases. Columns: low , high , status , cntrltype , name , phyname , mirrorname, vdevno, crdate, resizedate, status2, instanceid, uuid
Each key created in a database, including the default key, has an entry in the database-specific system catalog sysencryptkeys. Columns: id, ekalgorithm, type, status, eklen, value, uid, eksalt, ekpairid, pwdate, expdate, ekpwdwarn
master database only – contains one row for each Adaptive Server engine currently online. Columns: engine, osprocid, osprocname, status, affinitied, cur_kpid, last_kpid, idle_1, idle_2, idle_3, idle_4, starttime, nodeid, instanceid
All databases – sysgams stores the global allocation map for the database.
All databases – contains one row for each clustered index, one row for each nonclustered index, one row for each table that has no clustered index, and one row for each table that contains text or image columns. Columns: name, id, indid, doampg, ioampg, oampgtrips, status3, status2, ipgtrips, first, root, distribution, usagecnt, segment, status, maxrowsperpage, minlen , maxlen , maxirow, keycnt, keys1, keys2, soid, csid, base_partition, fill_factor, res_page_gap, exp_rowsize, keys3, identitygap, crdate, partitiontype, conditionid
A fake table that reports on the state of the instances. sysinstances includes a row for each instance defined in the cluster configuration. Columns: id, name, state, hostname, starttime, connections_active, engines_online
All databases – contains one row for each Java archive file that is retained in the database. Columns: jid, jstatus, jname, jbinary
All databases – contains one row for each primary, foreign, or common key. Columns: id, type, depid, keycnt, size, key1 ... key8, depkey1 ... depkey8, spare1
master database only – contains one row for each language known to Adaptive Server. Columns: langid, dateformat, datefirst, upgrade, name, alias, months, shortmonths, days
master database only – contains a row for each network protocol available for connecting with the current Adaptive Server. Columns: net_type, address_info, spare, nodeid, instanceid
master database only – contains information about active locks, and built dynamically when queried by a user. Columns: id, dbid, page, type, spid, class, fid, context, row, loid, partitionid, nodeid, instanceid
master database only – contains a row for each instance of a server login possessing a system role. Columns: suid, srid, status
master database only – contains one row for each valid Adaptive Server user account. Columns: suid, status, accdate, totcpu, totio, spacelimit, timelimit, resultlimit, dbname, name, password, language, pwdate, audflags, fullname, srvname, logincount, procid, lastlogindate, crdate, locksuid, lockreason, lockdate
All databases – contains the transaction log. It is used by Adaptive Server for recovery and roll forward. It is not useful to users. Column: xactid, op
master database only – contains information about each database’s oldest active transaction (if any) and the Replication Server truncation point (if any) for the transaction log, but it is not a normal table. Rather, it is built dynamically when queried by a user. Columns: dbid, reserved, spid, page, xactid, masterxactid, starttime, name, xloid
master database only – contains one row for each system error or warning that can be returned by Adaptive Server. Columns: error, severity, dlevel, description, langid, sqlstate
master database only – contains one row for each monitor counter. Columns: field_name, group_name, field_id, value, description, nodeid, instanceid
All databases – contains one row for each table, view, stored procedure, extended stored procedure, log, rule, default, trigger, check constraint, referential constraint, computed column, function-based index key, and (in tempdb only) temporary object, and other forms of compiled objects. It also contains one row for each partition condition ID when object type is N. Columns: name, id, uid, type, userstat, sysstat, indexdel, schemacnt, sysstat2, systat3, crdate, expdate, deltrig, instrig, updtrig, seltrig, ckfirst, cache, audflags, objspare, versionts, loginame, identburnmax, spacestate, erlchgts
All databases – the fake table queried by sp_options. Columns: spid, name, category, currentsetting, defaultsetting, scope
All databases – contains one row for each partition key for hash, range, and list partitioning of a table. All columns are not null. Columns: indid, id, colid, position
All databases – contains one row for each data partition and one row for each index partition. Columns: name, indid, id, partitionid, segment, status, datoampage, indoampage, firstpage, rootpage, data_partitionid, crdate, cdataptnname
All databases – contains entries for each view, default, rule, trigger, procedure, declarative default, partition condition, check constraint, computed column, function-based index key, and other forms of compiled objects. Columns: type, qp_setting, id , sequence, status, number, version
master database only – contains information about Adaptive Server processes, but it is not a normal table. Columns: spid, kpid, enginenum, status, suid, hostname, program_name, hostprocess, clientport, cmd, cpu, physical_io, memusage, blocked, dbid, uid, gid, tran_name, time_blocked, network_pktsz, fid, execlass, priority, affinity, id, stmtnum, linenum, origsuid, block_xloid, clientname, clienthostname, clientapplname, sys_id, ses_id, loggedindatetime, ipaddr, nodeid, instanceid, pad, lcid
All databases – contains information on permissions that have been granted to, or revoked from, users, groups, and roles. Columns: id, uid, action, protecttype, columns, grantor
All databases – presents aggregated historical query processing metrics for individual queries from persistent data. Columns: uid, gid, hashkey, id, sequence, exec_min, exec_max, exec_avg, elap_min, elap_max, elap_avg, lio_min, lio_max, lio_avg, pio_min, pio_max, pio_avg, cnt, abort_cnt, qtext
All databases – contains two or more rows for each abstract query plan. Uses datarow locking. Columns: uid, dbid, qpdate, sprocid, hashkey2, key1, key2, key3, gid, hashkey, id, type, sequence, status, text
All databases – contains one row for each referential integrity constraint declared on a table or column. Columns: indexid, constrid, tableid, reftabid, keycnt, status, frgndbid, pmrydbid, spare2, fokey1 ... fokey16, refkey1 ... refkey16, frgndbname, pmrydbname
master database only – contains one row for each remote user that is allowed to execute remote procedure calls on this Adaptive Server. Columns: remoteserverid, remoteusername, suid, status
master database only – contains a row for each resource limit defined by Adaptive Server. Columns: name, appname, rangeid, limitid, enforced, action, limitvalue, scope, spare
All databases – maps server role IDs to local role IDs. Columns: id, lrid, type, status
master database only – contains information about the security services supported by each security mechanism that is available to Adaptive Server. Columns: sec_mech_name, available_service
All databases – contains one row for each segment (named collection of disk pieces). Columns: segment, name, status
master database only – contains one row for each remote Adaptive Server, Backup Server, or Open Server on which this Adaptive Server can execute remote procedure calls. Columns: srvid, srvstatus, srvstatus2, srvstat2, srvname, srvnetname, srvclass, srvsecmech, srvcost
master database only – contains one row for each client that connects to Adaptive Server with the failover property. Columns: sys_id, ses_id, state, spare, status, dbid, name, nodeid, instanceid, ses_data
All databases – contains one row for each slice (page chain) of a sliced table. sysslices is used only during the Adaptive Server upgrade process. Columns: state, id, partitionid, firstpage, controlpage, spare
master database only – contains a row for each system or user-defined role. Columns: srid, name, password, pwdate, status, logincount
All databases – contains one or more rows for each indexed column on a user table and for each partition. May also contain rows for unindexed column. Columns: statid, id, sequence, moddate, formatid, usedcount, colidarray, c0...c79, indid, ststatus, partitionid, spare2, spare3
All databases – contains one row for each clustered index, one row for each nonclustered index, one row for each table that has no clustered index, and one row for each partition. Columns: indid, id, activestatid, indexheight, leafcnt, pagecnt, rowcnt, forwrowcnt, delrowcnt, dpagecrcnt, ipagecrcnt, drowcrcnt, oamapgcnt, extent0pgcnt, datarowsize, leafrowsize, status, plljoindegree, spare2, rslastoam, rslastpage, frlastoam, frlastpage, conopt_thld, plldegree, emptypgcnt, spare4, partitionid, spare5, statmoddate, unusedpgcnt, oampagecnt
All databases – contains one row for each threshold defined for the database. Columns: segment, free_space, status, proc_name, suid, currauth
master database only – stores named time ranges, which are used by Adaptive Server to control when a resource limit is active. Columns: name, id, startday, endday, starttime, endtime
master database only – contains information about Adaptive Server transactions, but it is not a normal table. Columns: xactkey, starttime, failover, type, coordinator, state, connection, status, status2, spid, masterdbid, loid, namelen, xactname, srvname, nodeid, instanceid
All databases – contains one row for each system-supplied and user-defined datatype. Domains (defined by rules) and defaults are given, if they exist. Columns: uid, usertype, variable, allownulls, type, length, tdefault, domain, name, printfmt, prec, scale, ident, hierarchy, xtypeid, xdbid, accessrule
master database only – contains one row for each disk allocation piece assigned to a database. Each database contains a specified number of database (logical) page numbers. Columns: dbid, segmap, lstart, size, vstart, pad, unreservedpgs, crdate, vdevno
All databases – contains one row for each user-defined message that can be returned by Adaptive Server. Columns: error, uid, description, langid, dlevel
All databases – contains one row for each user allowed in the database, and one row for each group or role. Columns: suid, uid, gid, name, environ
All databases – contains one row for each extended, Java-SQL datatype. Columns: xtid, xtstatus, xtmetatype, xtcontainer, xtname, xtsource, xtbinaryinrow, xtbinaryoffrow
