Special Edition Using Microsoft SQL Server 6.5 Second Edition

Previous chapterNext chapterContents


- C -
Understanding the SQL System Tables

SQL Server is built by using the "practice what you preach" motto. The entire system, from the tables you create, to the columns in those tables, to configuration options that control SQL Server's operation, is stored in relational tables within the system.

These tables, installed automatically when you install SQL Server on your system, contain the information that SQL Server needs to manage and operate your system. In this appendix, you'll get a better idea about what's in each of these tables. While it's not typically necessary to manually modify these tables, it can be helpful to understand the purpose of each, and what you can expect each to contain.

Starting with Table C.1, each table provides a table name and a brief description of the purpose of the table.

Table C.1 SQL Server System Tables

Table Name Description
Sysalternates Maps the user's name for a given database. The name is looked up in the table, based on the suid, then validated as being allowed access when the user logs in.
Sysarticles Manages the articles available for publication when you're using replication. You can review this table, and the articles represented by the rows in the table, by using the system-provided sp_article* stored procedures.
Syscharsets Contains the different character data sets available to SQL Server. For example, on a default installation, the character sets shown in Listing C.1A are installed on your server. This table only exists in the master database, and sysconfigures references one row of the table, determining which character set is in use on your system.
syscolumns Contains one row for each column defined in every other table or view. The rows define the datatypes and behavior of each of the columns in the other tables.
syscomments Syscomments is used to document the tables in your system. The rows in the table contain information regarding each rule, default, trigger, and constraint. They also outline the stored procedures in your system. The TEXT column of the table contains the SQL statement that makes up the object. If the SQL statement is too long for a text column, or 255 characters, there can be more than one row in the table, essentially pieced back together when they are needed. A given object definition can take up to 255 rows for its definition and associated SQL statement.
sysconfigures Set for your SQL Server. For example, as mentioned above, it references the character set definition, from the syscharsets table in use. This table is found only in the master database.
sysconstraints All constraints, and the type of constraint they represent, are defined in this table. Types of constraints, determined by the status column, can be Primary Key, Unique Key, Foreign Key, Check, or Default.
syscurconfigs Much like sysconfigures, syscurconfigs contains the values that represent the configuration of the system. Sysconfigures contains the values that are the current saved values, while syscurconfigs contains the values currently in effect. If you make changes, the syscurconfigs table contains those changes until the next time you restart your server.
sysdatabases As the name suggests, sysdatabases contains one row for each database in your system. You can select * from sysdatabases to see each database defined in your system, along with the different attributes that define it.
sysdepends Outlines all dependencies between stored procedures, tables, keys, and so on.
sysdevices Sysdevices contains a single row for each device on your system. The row defines the type of device and its associated configuration parameters. When you install SQL Server, two floppy disk devices, one master database device, and one disk dump device are defined automatically.
sysindexes Contains one row per index defined for a given database's tables. Indexes defined include both clustered and non-clustered. Sysindexes has replaced the previous syskeys table, though syskeys is still included for legacy systems.
syslanguages For each language known to SQL Server, there is a single row in this table. It is important to note that English is always available and not included in the listing.
syslocks When locks are issued against a table, they are represented by a row in this table. The table is built dynamically when you query it, so it is more of a view than a static representation of the locks currently held. Remember that, when you view this table, the locks shown were in effect when the query was issued but may have been released in the time since the query. The TYPE column indicates the type of lock, as shown in Table C.2.
If any of the locks appear with 256 added to them, it indicates that the lock is blocking another user. You can query the spid to determine what process is holding the lock.
syslogins There is one row in the syslogins table for each login for the system. This table also indicates the default database for a user, along with the other configuration options, including disk allocations, for the user.
syslogs Syslogs contains the transaction log, which is the heart of the SQL Server recovery systems.
sysmessages Containing roughly 2,200 rows, this table contains all the text messages that you receive when SQL Server issues an alert, error message, or informational message.
sysobjects From tables to indexes, this table contains the information that defines the objects in your system, including their object ID and type of object.
sysprocedures Contains the information that serves as a reference into the syscomments table.
sysprocesses As with several other system tables, the sysprocesses table is built dynamically when you query it. It contains information about all current processes being managed by SQL Server at the time of the query.
sysprotects Provides the security parameters for users of the system. It provides the information on what types of restrictions are applied to user IDs.
syspublications Contains one row for each publication defined in your system as a replication object.
sysreferences The sysreferences table contains information on all foreign keys in your tables.
sysremotelogins This table controls remote procedure call user access to your SQL Server. There is one entry in the table for each login allowed to access your system and run remote stored procedures.
syssegments Defines the fragments that make up your database storage subsystem.
sysservers The inverse of sysremotelogins, sysservers defines the servers on which you can run remote stored procedures.
syssubscriptions Lists the subscriptions on your system when you implement replication.
systypes For each datatype in your system, there is one row in this table. This includes both user-defined datatypes and those provided by the system. By default, those types shown in Table C.3 are installed on your system.
sysusages As your database grows, each page of the database is recorded in sysusages. The table contains a row for each portion of each database on your system.
sysusers This table, which is database-specific, contains one row for each user allowed access to the database. It contains information about the group the user belongs to, which will relate the security information as it relates to that database/user combination.

Listing C.1A Default Character Sets

ISO 8859-1 (Latin-1) - Western European 8-bit character set.

Code Page 850 (Multilingual) character set.

Code Page 437 (United States) character set.

Code page 1250

Code Page 1251

Code page 1253

Code page 1254

Code page 1255

Code page 1256

Code page 1257

Code page 949

Code page 950

Code page 936

Binary Sort Order for the CodePage 850 Character Set

Case-insensitive dictionary sort order for use with several Western-European languages including English, French, and German. Uses the ISO 8859-1 character set.

Code page 932

Table C.2 Type of Locks

Value Description
1 Table lock, exclusive
2 Table lock, shared
3 Intent lock, exclusive
4 Intent lock, shared
5 Page lock, exclusive
6 Page lock, shared
7 Page lock, update - if the page is updated, this lock will change to an exclusive lock
8 Extent lock, exclusive
9 Extent lock, update
10 Extent lock, next in sequence
11 Previous extent lock

Table C.3 Default Data Types

binary bit
char datetime
datetimn decimal
decimaln float
floatn image
int intn
money moneyn
numeric numericn
real smalldatetime
smallint smallmoney
sysname text
timestamp tinyint
varbinary varchar


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.