| 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. | |
