Database: What is a database in SAP Sybase? System tables

A database is the basic component, where user data is stored, but apart from user data, databases store also some other elements:

• indexes that speedup data retrieval
• views, functions and procedures that simplify working with data
• transaction log that is required to guarantee transactions to be consistent even if database server would be unexpectedly stopped
• system tables describing objects that bildup the database

Let's take closer look to system tables. Their names usually begin with 'sys' prefix and generally should never be modified directly (however after changing the 'allow updates' parameter it is possible):

• sysobjects - each record represents a object in the database like function or table;
• sysindexes - describes tables and indexes;
• systabstats, sysstatistics - stores histogram of data from each table and index;
• syscolumns - contains description of all tables, vilews columns as well as procedures parameters;
• sysprocedures - each record represents a procedure;
• syscomments - a very interesting table containing script text that was executed to create an object, so this is something like a documentory table describing all more advanced objects;
• sysusers - lists users having access to the database containing that table;
• sysprotects - lists permissions granted to database users;
• syslogs - describes database logs
• syspartitions - stores data about partitioned tables
• syssegments - allows name device's fragments and next putting a table or index on such a segment. This solution is used for tuning disk access http://sybaseblog.com/sybasewiki/index.php?title=What_is_segment_and_how_to_create_segment%3F

There is a dedicated stored procedure displaying detailed information regarding passed by parameter database:

sp_helpdb 'pubs2'

If no parameter would be passed to the procedure than information regarding all databases will be displayed. As shown on the picture above, also information regarding database options is printed. Command works similar as sp_helpdevice for displaying device information. Also here an equivalent query could be written to display the same information, but using a procedure is just easier...