Databases metadata

Quite often when the database already exists some time on the server administrator needs to read somehow information regarding that database. The most important and the most basic information can be found in sysdatabases table:

SELECT * FROM sysdatabases

There is a lot of columns here, the most important are described below, the others save some more complicated stuff that is presented in quite descriptive way by for example sp_helpdb stored procedure.

• name - determines the name of the database
• dbid - is probably more important than the name as Sybase performs all the operations on databases using rather their ids than names. Names are for humans...
• suid - identifies id of login, who is the owner of the database
• status, status2, ... - store information regarding the status of the database
• crdate - date when the database was created
• dumptrdate - date of last incremental database dump

Another important table is sysdevices. It is described in sysdevices system table on this blog. Let's remind here only, that information from that table allows to get information regarding disk space consumed by the database, and also read how many available space still exists for the database.

Database may have a long live. During that live it can be grown multiple times by the administrator. Each time when a new fragment of the device is added to the database, such information is saved in the sysusages system table. The most important columns here are:

• dbid - allows to identify the database a fragment of device belongs to
• lstart - the number (logical start) of the fragment in the database structure (sequence number). Numeration starts from 0 and enumerates all the pages of a database. In one database this number remains unique, but of course another database has it's own numeration and from the server perspective those numbers are not unique
• size - the size of the fragment (in logical pages = 2K)
• vdevno - number of device where the fragment is located
• vstart - number of page on a device where the fragment starts (vstart+size = end page on device)
• segmap - integer value that determines what kind of data can be stored on the fragment (examples are system tables, data, log or value determining the location as a default). Detailed specification of currently used by a database segments can be found in syssegments system table. This table is again database specific and it is not stored in master database.

Having in mind that picture (from http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc70204.1570/pdf/ase_sys_poster.pdf?noframes=true)

following query can be used to display basic information about databases:

SELECT DISTINCT
      db.dbid, db.name, dvc.vdevno, dvc.name
FROM sysdatabases db
JOIN sysusages    usg ON db.dbid    = usg.dbid
JOIN sysdevices   dvc ON dvc.vdevno = usg.vdevno

but may be just using sp_helpdb would be easier...