System Tables

SQL Server uses system tables to hold its configuration and object information. The tables are generally named with a sys prefix, such as sysdatabases. For this reason, system tables are often referred to as the sys tables. Some of these, such as syslogins, are actually implemented as views. The system tables are broken into two basic categories. The system catalog tables are contained in the master database and contain information common to the entire installation. The database catalog is contained in each database, including the master, and contains information specific to that database. The msdb database has?in addition to its database catalog?additional system tables related to SQL Agent, Backup and Recovery, and log shipping (Enterprise Edition only). When replication is configured, additional system tables are created in master, distribution, and the user databases involved in replication.

System Catalog

Table 10.1 lists the system catalog tables and views.

Table 10.1. The System Catalog Tables and Views
Name Type Description
sysaltfiles Table Contains database file information for tempdb. As tempdb is rebuilt on system startup, this information must be stored in master.
syscacheobjects Table Keeps track of compiled object usage in cache. This is a virtual table materialized each time it is queried.
syscharsets Table Contains installed character sets and sort orders.
sysconfigures Table Holds system configuration values to be used at the next system startup.
syscurconfigs Table Contains current system configuration. This is a virtual table materialized each time it is queried.
sysdatabases Table Contains information on all databases.
sysdevices Table Contains an entry for each database file, as well as disk and tape backup files.
syslanguages Table Contains an entry for each language installed on the server. There is no entry for U.S. English; however, it is always available.
syslockinfo Table Contains information on all active locks on the system.
syslogins View Contains information on each login ID, based on the sysxlogins table.
sysmessages Table Contains system error and warning messages.
sysoledbusers View Contains user and password mapping for linked servers. Based on sysxlogins.
sysperfinfo Table Contains performance counters that can be displayed by Performance Monitor.
sysprocesses Table Contains information on all client and system processes running on SQL Server.
sysremotelogins View Contains an entry for each remote user who can call remote stored procedures.
sysxlogins Table Contains information on each login ID.

Database Catalog

Table 10.2 lists the database catalog tables and views.

Table 10.2. Database Catalog Tables and Views
Name Type Description
syscolumns Table Contains an entry for every column in all tables and views, and a row for each parameter in a stored procedure.
syscomments Table Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The text column contains the SQL statement to create the object.
sysconstraints View Maps constraints to the objects that own them. Based on the sysobject table.
sysdepends Table Stores relationships between dependent objects, such as the relationship of views to tables.
sysfilegroups Table Contains information about filegroups.
sysfiles Table Contains information on database files, including the logical and physical name and size.
sysforeignkeys Table Contains foreign key relationships in the database.
sysfulltextcatalogs Table Contains an entry for each full-text catalog.
sysindexes Table Contains information about indexes in the database.
sysindexkeys View Contains information about index keys.
sysmembers Table Contains member information for database roles.
sysobjects View Contains one entry for every object in the database.
syspermissions Table Contains permissions granted and denied to users, groups, and roles.
sysprotects Table Contains permissions applied to security accounts with the GRANT and DENY statements.
sysreferences Table Contains column mapping for foreign key constraints.
systypes Table Contains information on all system and user-defined datatypes.
sysusers Table Contains an entry for each Windows user, Windows group, SQL Server user, or SQL Server role that has access to the database.

Replication Catalog

The replication catalog tables are not created until replication is configured. When replication is defined, tables are created in the master, msdb, distribution, and user databases involved in replication. The configuration will vary depending on the replication model.

System Tables in msdb

In addition to the standard database catalog tables, msdb contains tables specific to the SQL Server Agent, backup, Log Shipping, and the Database Maintenance Plan.

Table 10.3 lists the SQL Server Agent Tables.

Table 10.3. SQL Server Agent Tables
Name Description
sysalerts Name, error number, and so on for all defined alerts.
syscategories The categories used to organize jobs, alerts, and operators.
sysdownloadlist Queue of jobs to be downloaded to other servers.
sysjobhistory History table for scheduled jobs.
sysjobs Information on SQL Server Agent jobs.
sysjobschedules Scheduling information for jobs.
sysjobservers The relationship of a job with one or more target servers.
sysjobsteps Steps stored for each scheduled job.
sysnotifications Notification information for each operator by job.
sysoperators Operator information, such as name, e-mail address, and so on.
systargetservergroupmembers The target servers that are included in a multiserver group.
systargetservergroups The target server groups that are defined on the server.
systargetservers The enlisted target servers.
systaskids Mappings of jobs from earlier versions to current version jobs.

Table 10.4 lists the backup tables.

Table 10.4. The Backup Tables
Name Description
backupfile Contains information on each data or log file that is backed up.
backupmediafamily Contains information on backup media families, such as name, mediaset, physical and logical filenames, and so on.
backupmediaset Contains name, description, and so on for each media set.
backupset Contains a row for each backup set.
logmarkhistory Contains information on each committed marked transaction.
restorefile Contains an entry for each file that has been restored.
restorefilegroup Contains an entry for each filegroup restored.
restorehistory Contains one row for each restore operation.

Table 10.5 lists the maintenance plan tables.

Table 10.5. Maintenance Plan Tables
Name Description
sysdbmaintplan_databases One row for each database included in the plan.
sysdbmaintplan_history Maintenance plan history for executed job.
sysdbmaintplan_jobs One row for each job in the plan.
sysdbmaintplans One row for each plan. Includes name, ID, and creation date.

Table 10.6 lists the log shipping tables.

Table 10.6. Log Shipping Tables
Name Description
log_shipping_databases Name and ID of the database being shipped.
log_shipping_monitor Name of the log shipping monitor server.
log_shipping_plan_databases Database names, enabled options for a log shipping plan.
log_shipping_plan_history History information for a log shipping plan.
log_shipping_plans Configuration information for a log shipping plan.
log_shipping_primaries Primary server, configuration, history, and thresholds.
log_shipping_secondaries Secondary server, configuration, history, and thresholds.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features