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.
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.
The syscharsets table contains a single row for each valid character set and sort order available.
Column | Datatype |
type | smallint |
id | tinyint |
csid | tinyint |
status | smallint |
name | varchar(30) |
description | varchar(255) |
definition | image |
The sysconfigures table contains one row for each user-configurable configuration parameter.
Column | Datatype |
config | smallint |
value | int |
comment | varchar(255) |
status | smallint |
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.
Column | Datatype |
config | smallint |
value | int |
comment | varchar(255) |
status | smallint |
The sysdatabases table contains an entry for each database on SQL Server.
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.
The sysdevices table contains a row for every device on the SQL Server.
Column | Datatype |
low | |
int | |
high | int |
status | smallint |
cntrltype | smallint |
name | varchar(30) |
phyname | varchar(127) |
mirrorname | varchar(127) |
stripeset | varchar(30) |
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.
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) |
The syslocks table is dynamically built when queried by a user; it contains information about active locks.
Column | Datatype |
id | int |
dbid | smallint |
page | int |
type | smallint |
spid | smallint |
The syslogins table contains a single row for each valid SQL Server login account.
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) |
The sysmessages table contains the system errors and warning messages returned by SQL Server.
Column | Datatype |
error | int |
severity | smallint |
dlevel | smallint |
description | varchar(255) |
langid | smallint |
The sysprocesses table is built dynamically when queried by a user; it contains information about SQL Server processes.
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.
The sysremotelogins table contains a single row for each remote user who is allowed to execute remote procedure calls.
Column | Datatype |
remoteserverid | smallint |
remoteusername | varchar(30) |
suidsmall | int |
status | smallint |
The sysservers table contains a single row for each remote SQL Server. These SQL Servers can execute remote procedures.
Column | Datatype |
srvid | smallint |
srvstatus | smallint |
srvname | varchar(30) |
srvnetname | varchar(32) |
topologyx | int |
topologyy | int |
The sysusages table contains a single row for each disk allocation piece assigned to a database.
Column | Datatype |
dbid | smallint |
segmap | int |
lstart | int |
size | int |
vstart | int |
The following tables are the system tables found in each database, including the master database, on SQL Server.
The sysalternates table contains a single row for each aliased user in the database.
Column | Datatype |
suid | smallint |
altsuid | smallint |
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.
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) |
The syscolumns table contains a single row for each parameter in a stored procedure and each column in a table and view.
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 |
The syscomments table contains entries for database objects such as views, rules, defaults, triggers, and procedures.
Column | Datatype |
id | int |
number | smallint |
colid | tinyint |
language | smallint |
text | varchar(255) |
The sysconstraints table contains constraint mappings to owned objects. The sysconstraints table is new for SQL Server 6.0.
Column | Datatype |
constid | int |
id | int |
colid | tinyint |
spare1 | tinyint |
status | int |
actions | int |
error | int |
The sysdepends table contains rows for object dependencies. Table Structure
Column | Datatype |
id | int |
number | smallint |
depid | int |
depnumber | smallint |
depdbid | smallint |
depsiteid | smallint |
status | smallint |
selall | bit |
resultobj | bit |
readobj | bit |
The sysindexes table contains a single row for each of the following:
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.
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.
Column | Datatype |
xactid | binary(6) |
op | tinyint |
The sysobjects table contains a single entry for each database object, such as tables, views, stored procedures, constraints, rules, and so on. 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 |
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.
The sysprocedures table contains entries for defaults, rules, view, triggers, and stored procedures.
Column | Datatype |
type | smallint |
id | int |
sequence | smallint |
status | smallint |
number | smallint |
The sysprotects table contains user GRANT and REVOKE permission information.
Column | Datatype |
id | int |
uid | smallint |
action | tinyint |
protecttype | tinyint |
columns | varbinary(32) |
grantor | smallint |
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.
Column | Datatype |
description | varchar(255) |
name | varchar(30) |
repl_freq | tinyint |
restricted | bit |
status | tinyint |
sync_method | tinyint |
taskid | int |
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.
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 |
The syssegments table contains a single entry for each defined segment.
Column | Datatype |
segment | smallint |
name | varchar(30) |
status | smallint |
The syssubscriptions table is used to associate published articles with receiving subscription servers. The syssubscriptions table is new for SQL Server 6.0.
Column | Datatype |
artid | int |
srvid | smallint |
dest_db | varchar(30) |
status | tinyint |
sync_type | tinyint |
timestamp | timestamp |
The systypes table contains a single entry for all user-defined and system datatypes.
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.
The sysusers table contains a single row for each user and group allowed to use the database. Table Structure
Column | Datatype |
suid | smallint |
uid | smallint |
gid | smallint |
name | varchar(30) |
environ | varchar(255) |
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.