Appendix F
System Tables



System tables are the tables installed with SQL Server that are used by SQL Server to manage the users, devices, and all the other SQL Server objects. System tables are found in every database. The following sections give brief descriptions of the system tables and their physical layouts. This appendix is divided into two parts: system tables found only in the master database and system tables found in all databases.

System Tables in the master Database

The following system tables (in alphabetical order) are found in the master database and are used by SQL Server to manage and maintain the server.

syscharsets

The syscharsets table contains a single row for each valid character set and sort order available.
Table Structure
Column Datatype
type smallint
id tinyint
csid tinyint
status smallint
name varchar(30)
description varchar(255)
definition image


Index Unique clustered index on id. Unique nonclustered index on name.

sysconfigures

The sysconfigures table contains one row for each user-configurable configuration parameter.
Table Structure
Column Datatype
config smallint
value int
comment varchar(255)
status smallint


Index Unique clustered index on config.

syscurconfigs

The syscurconfigs table contains the current system configuration values: one for each user-configurable parameter and an additional four entries that describe the configuration structure. The syscurconfigs table is built dynamically when queried by a user.
Table Structure
Column Datatype
config smallint
value int
comment varchar(255)
status smallint


Index Unique clustered index on config.

sysdatabases

The sysdatabases table contains an entry for each database on SQL Server.
Table Structure
Column Datatype
name varchar(30)
dbid smallint
suid smallint
mode smallint
status smallint
version smallint
logptr int
crdate datetime
dumptrdate datetime
category int


NOTE: The column category is new for SQL Server version 6.x and is used for publication and constraints.

Index Unique clustered index on name. Unique nonclustered index on dbid.

sysdevices

The sysdevices table contains a row for every device on the SQL Server.
Table Structure
Column Datatype
low
int
high int
status smallint
cntrltype smallint
name varchar(30)
phyname varchar(127)
mirrorname varchar(127)
stripeset varchar(30)


Index Unique clustered index on name.

syslanguages

The syslanguages table contains a single row for each language installed on the SQL Server.


NOTE: U.S. English is not in syslanguages but is always available to SQL Server.

Table Structure
Column Datatype
angid smallint
dateformat char(3)
datefirst tinyint
upgrade int
name var
char(30)
alias var
char(30)
months var
char(251
shortmonths varchar(119)
days var
char(216)


Index Unique clustered index on langid. Unique nonclustered index on name. Unique nonclustered index on alias.

syslocks

The syslocks table is dynamically built when queried by a user; it contains information about active locks.
Table Structure
Column Datatype
id int
dbid smallint
page int
type smallint
spid smallint


Index None

syslogins

The syslogins table contains a single row for each valid SQL Server login account.
Table Structure
Column Datatype
suid smallint
status smallint
accdate datetime
totcpu int
totio int
spacelimit int
timelimit int
resultlimit int
dbname var
char(30)
name var
char(30)
password varchar(30)
language varchar(30)


Index Unique clustered index on suid. Unique nonclustered index on name.

sysmessages

The sysmessages table contains the system errors and warning messages returned by SQL Server.
Table Structure
Column Datatype
error int
severity smallint
dlevel smallint
description varchar(255)
langid smallint


Index Clustered index on error, dlevel. Unique nonclustered index on error, dlevel, langid.

sysprocesses

The sysprocesses table is built dynamically when queried by a user; it contains information about SQL Server processes.
Table Structure
Column Datatype
pid smallint
kpid smallint
status char(10)
suid smallint
hostname char(10)
program_name char(16)
hostprocess char(8)
cmd char(16)
cpu int
physical_io int
memusage int
blocked smallint
waittype binary
dbid smallint
uid smallint
gid smallint
last_batch char(8)
login_time char(8)
net_address char(12)
nt_domain char(30)
nt_username char(30)
net_library char(12)


NOTE: The columns last_batch, login_time, net_address, nt_domain, nt_username, and net_library are new for SQL Server 6.5.

Index None.

sysremotelogins

The sysremotelogins table contains a single row for each remote user who is allowed to execute remote procedure calls.
Table Structure
Column Datatype
remoteserverid smallint
remoteusername varchar(30)
suidsmall int
status smallint


Index Unique clustered index on remoteserverid, remoteusername.

sysservers

The sysservers table contains a single row for each remote SQL Server. These SQL Servers can execute remote procedures.
Table Structure
Column Datatype
srvid smallint
srvstatus smallint
srvname varchar(30)
srvnetname varchar(32)
topologyx int
topologyy int


Index Unique clustered index on srvid. Unique nonclustered index on srvname.

sysusages

The sysusages table contains a single row for each disk allocation piece assigned to a database.
Table Structure
Column Datatype
dbid smallint
segmap int
lstart int
size int
vstart int


Index Unique clustered index on dbid, lstart. Unique nonclustered index on vstart.

Database System Tables

The following tables are the system tables found in each database, including the master database, on SQL Server.

sysalternates

The sysalternates table contains a single row for each aliased user in the database.
Table Structure
Column Datatype
suid smallint
altsuid smallint


Index Unique clustered index on suid.

sysarticles

The sysarticles table contains a single row for each article posted by the publishing server. The sysarticles table is new for SQL Server 6.0.
Table Structure
Column Datatype
artid int
columns varbinary(32)
creation_script varchar(127)
del_cmd varchar(255)
description varchar(255)
dest_table varchar(30)
filter int
filter_clause text
ins_cmd varchar(255)
name varchar(30)
objid int
pubid int
pre_creation_cmd tinyint
status tinyint
sync_objid int
type tinyint
upd_cmd varchar(255)


Index Unique nonclustered index on artid, pubid.

syscolumns

The syscolumns table contains a single row for each parameter in a stored procedure and each column in a table and view.
Table Structure
Column Datatype
id int
number smallint
colid tinyint
status tinyint
type tinyint
length tinyint
offset smallint
usertype smallint
cdefault int
domain int
name varchar(30)
printfmt varchar(255)
prec tinyint
scale tinyint


Index Unique clustered index on id, number, colid.

syscomments

The syscomments table contains entries for database objects such as views, rules, defaults, triggers, and procedures.
Table Structure
Column Datatype
id int
number smallint
colid tinyint
language smallint
text varchar(255)


Index Unique clustered index on id, number, colid, texttype.

sysconstraints

The sysconstraints table contains constraint mappings to owned objects. The sysconstraints table is new for SQL Server 6.0.
Table Structure
Column Datatype
constid int
id int
colid tinyint
spare1 tinyint
status int
actions int
error int


Index Clustered index on id, colid. Unique nonclustered index on constid.

sysdepends

The sysdepends table contains rows for object dependencies. Table Structure
Table Structure
Column Datatype
id int
number smallint
depid int
depnumber smallint
depdbid smallint
depsiteid smallint
status smallint
selall bit
resultobj bit
readobj bit


Index Unique clustered index (ignore duplicate key) on id, number, depid, depnumber, depdbid, depsiteid.

sysindexes

The sysindexes table contains a single row for each of the following:

Table Structure
Column Datatype
name varchar(30)
id int
indid smallint
dpages int
reserved int
used int
rows int
first int
root int
distribution int
OrigFillFactor tinyint
segment smallint
status smallint
rowpage smallint
minlen smallint
maxlen smallint
maxirow smallint
keycnt smallint
keys1 varbinary(255)
keys2 varbinary(255)
soid tinyint
csid tinyint
UpdateStamp varbinary


NOTE: The column UpdateStamp is new for SQL Server 6.5.

Index Unique clustered index on id, indid.

syslogs

The syslogs table is used for the transaction log and is used by SQL Server for roll forward and recovery.


CAUTION: Do not try to modify the syslogs table. Doing so results in an infinite loop until the database fills up.

Table Structure
Column Datatype
xactid binary(6)
op tinyint


Index None.

sysobjects

The sysobjects table contains a single entry for each database object, such as tables, views, stored procedures, constraints, rules, and so on. Table Structure
Table Structure
Column Datatype
name varchar(30)
id int
uid smallint
type char(2)
userstat smallint
sysstat smallint
indexdel smallint
schema smallint
refdate datetime
crdate datetime
version datetime
deltrig int
instrig int
updtrig int
seltrig int
category int
cache smallint


Index Unique clustered index on id. Unique nonclustered index on name, uid.


NOTE: Following are the valid values for the type column; they can be used to query for specific database objects (these values sometimes appear on the certification test): C Check constraint D Default F Foreign key reference constraint K Primary or unique constraint L Log P Stored procedure R Rule S System table TR Trigger User table V View X Extended stored procedure.

sysprocedures

The sysprocedures table contains entries for defaults, rules, view, triggers, and stored procedures.
Table Structure
Column Datatype
type smallint
id int
sequence smallint
status smallint
number smallint


Index Unique clustered index on id, number, type, and sequence.

sysprotects

The sysprotects table contains user GRANT and REVOKE permission information.
Table Structure
Column Datatype
id int
uid smallint
action tinyint
protecttype tinyint
columns varbinary(32)
grantor smallint


Index Clustered index on id, uid, and action.

syspublications

The syspublications table contains a single entry for each publication posted by the publishing server. The syspublications table is new for SQL Server 6.0.
Table Structure
Column Datatype
description varchar(255)
name varchar(30)
repl_freq tinyint
restricted bit
status tinyint
sync_method tinyint
taskid int


Index Unique nonclustered index on pubid. Unique nonclustered index on name.

sysreferences

The sysreferences table contains foreign key table mappings from each foreign key to its respective reference tables. The sysreferences table is new for SQL Server 6.0.
Table Structure
Column Datatype
constid int
fkeydbid smallint
rkeyid int
rkeydbid smallint
rkeyindid smallint
keycnt smallint
fkey1 tinyint
fkey2 tinyint
fkey3 tinyint
fkey4 tinyint
fkey5 tinyint
fkey6 tinyint
fkey7 tinyint
fkey8 tinyint
fkey9 tinyint
fkey10 tinyint
fkey11 tinyint
fkey12 tinyint
fkey13 tinyint
fkey14 tinyint
fkey15 tinyint
fkey16 tinyint
rkey1 tinyint
rkey2 tinyint
rkey3 tinyint
rkey4 tinyint
rkey5 tinyint
rkey6 tinyint
rkey7 tinyint
rkey8 tinyint
rkey9 tinyint
rkey10 tinyint
rkey11 tinyint
rkey12 tinyint
rkey13 tinyint
rkey14 tinyint
rkey15 tinyint
rkey16 tinyint


Index Unique clustered index on constid. Nonclustered index on fkeyid. Nonclustered index on rkeyid.

syssegments

The syssegments table contains a single entry for each defined segment.
Table Structure
Column Datatype
segment smallint
name varchar(30)
status smallint


Index None.

syssubscriptions

The syssubscriptions table is used to associate published articles with receiving subscription servers. The syssubscriptions table is new for SQL Server 6.0.
Table Structure
Column Datatype
artid int
srvid smallint
dest_db varchar(30)
status tinyint
sync_type tinyint
timestamp timestamp


Index Unique nonclustered index on artid and srvid.

systypes

The systypes table contains a single entry for all user-defined and system datatypes.
Table Structure
Column Datatype
uid smallint
usertype smallint
variable bit
allownulls bit
type tinyint
length tinyint
tdefault int
domain int
name varchar(30)
printfmt varchar(255)
prec tinyint
scale tinyint


NOTE: The columns prec and scale are new in SQL Sever 6.0.

Index Unique clustered index on name. Unique nonclustered index on usertype.

sysusers

The sysusers table contains a single row for each user and group allowed to use the database. Table Structure
Table Structure
Column Datatype
suid smallint
uid smallint
gid smallint
name varchar(30)
environ varchar(255)


Index Unique clustered index on suid. Unique nonclustered index on name. Unique nonclustered index on uid.


DISCLAIMER


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select
Talk to Us.

© 1997, QUE Corporation, an imprint of
Macmillan Publishing USA, a Simon and Schuster Company.