http://goo.gl/EmKxy0

Home > ASE > Sybase frequently used system tables

Sybase frequently used system tables

System tables in master
=======================
syscharsets –> One row for each character set or sort order
sysconfigures –> One row for each configuration parameter that can be set by users
syscurconfigs –> Information about configuration parameters currently being used by Adaptive Server
sysdatabases –> One row for each database on Adaptive Server
sysdevices –> One row for each tape dump device, disk dump device, disk for databases, and disk partition
for databases
sysengines –> One row for each Adaptive Server engine currently online
syslanguages –> One row for each language (except U.S. English) known to the server
syslisteners –> One row for each type of network connection used by current Adaptive Server
syslocks –> Information about active locks
sysloginroles –> One row for each server login that possesses a system role
syslogins –> One row for each valid Adaptive Server user account
syslogshold –> Information about the oldest active transaction and the Replication ServerĀ® truncation point for each database
sysmessages –> One row for each system error or warning
sysmonitors –> One row for each monitor counter
sysprocesses –>Information about server processes
sysremotelogins –>One row for each remote user
sysresourcelimits –>One row for each resource limit
syssecmechs –> Information about the security services available for each security mechanism that is
available to Adaptive Server
sysservers –> One row for each remote Adaptive Server
syssessions –> Only used when Adaptive Server is configured for Sybase’s Failover in a high availability
system. syssessions contains one row for each client that connects to Adaptive Server with
the failover property (for example, isql -Q)
syssrvroles –> One row for each server-wide role
systimeranges –> One row for each named time range
systransactions –>One row for each transaction
sysusages –>One row for each disk piece allocated to a database

System tables in all databases
==============================
The following system tables occur in all databases:
syscoordinations –>One row for each remote participant of a distributed transaction

sysalternates –>One row for each Adaptive Server user mapped to a database user
sysattributes –>One row for each object attribute definition
syscolumns –>One row for each column in a table or view, and for each parameter in a procedure
syscomments –>One or more rows for each view, rule, default, trigger, and procedure, giving SQL definition
statement
sysconstraints –>One row for each referential and check constraint associated with a table or column
sysdepends –>One row for each procedure, view, or table that is referenced by a procedure, view, or trigger
sysgams –>Allocation bitmaps for an entire database
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
sysjars –>One row for each Java archive (JAR) file that is retained in the database. Uses row-level
locking
syskeys –>One row for each primary, foreign, or common key; set by user (not maintained by Adaptive
Server)
syslogs –>Transaction log
sysobjects –>One row for each table, view, procedure, rule, trigger default, log, and (in tempdb only)
temporary object
syspartitions –>One row for each partition (page chain) of a partitioned table
sysprocedures –>One row for each view, rule, default, trigger, and procedure, giving internal definition
sysprotects –>User permissions information
sysqueryplans –>Abstract query plans and SQL text
sysreferences –>One row for each referential integrity constraint declared on a table or column
sysroles Maps –>server-wide roles to local database groups
syssegments –>One row for each segment (named collection of disk pieces)
sysstatistics –>One or more rows for each indexed column on a user table. May also contain rows for
unindexed column systabstats –>One row for each table, plus one row for each nonclustered index
systhresholds –>One row for each threshold defined for the database
systypes –>One row for each system-supplied and user-defined datatype
sysusermessages –>One row for each user-defined message
sysusers –>One row for each user allowed in the database

All direct updates on system tables are by default not allowed -even for the
database owner. Instead, Adaptive Server supplies system procedures to make
any normally needed updates and additions to system tables.
You can allow direct updates to the system tables if it becomes necessary to
modify them in a way that cannot be accomplished with a system procedure.
To accomplish this, a System Security Officer must reset the configuration
parameter called allow updates to system tables with the system procedure
sp_configure.

Rules for using system tables
=============================
Unless noted otherwise, system tables use allpages locking.
Updating system tables
———————-
All direct updates on system tables are by default not allowed -even for the
database owner. Instead, Adaptive Server supplies system procedures to make
any normally needed updates and additions to system tables.
You can allow direct updates to the system tables if it becomes necessary to
modify them in a way that cannot be accomplished with a system procedure.
To accomplish this, a System Security Officer must reset the configuration
parameter called allow updates to system tables with the system procedure
sp_configure.

There are entries in some of the master database tables that should not be
altered by any user under any circumstances. For example, do not attempt to
modify syslogs with a delete, update, or insert command. In addition, an
attempt to delete all rows from syslogs will put Adaptive Server into an infinite
loop that eventually fills up the entire database.

Triggers on system tables — You cannot create triggers on system tables. If you try to create a trigger on a
system table, Adaptive Server returns an error message and cancels the trigger.

Aggregate functions and virtual tables — Aggregate functions cannot be used on virtual tables such as syslocks and
sysprocesses
========================================

Auditing Involves –
——————
List the roles which are active.

Subcommand – for alter table command, the options add column or drop column might be used

Previous value – The value prior to the update if the event resulted in the update of a value.

Current – value The new value if the event resulted in the update of a value.

Proxy information – The original login name, if the event occurred while a set proxy was in effect.

Principal information – The principal name from the underlying security mechanism, if the user’s login is the
secure default login, and the user logged into Adaptive Server via unified login. The value
of this field is NULL, if the secure default login is not being used.

Check the Sybase Wiki @ sybasewiki.com
Categories: ASE Tags:
  1. No comments yet.
  1. No trackbacks yet.