Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

System Tables - SQL Server 2000

Information in this article and the associated system table pages is reproduced here in HTML format from the systbl_rev.chm file, available from Microsoft's website.

Table of Contents [Hide/Show]    1 - System Tables in Every Database    2 - System Tables in the MASTER Database Only    3 - SQL Server Agent Tables in the MSDB Database    4 - Database Maintenance Plan Tables in the MSDB Database    5 - Replication Tables       5.1 - Replication Tables in Each User Database       5.2 - Replication Tables in Each Distribution Database       5.3 - Replication Tables in the MSDB Database    6 - Backup Tables in the MSDB Database    7 - Log Shipping Tables in the MSDB Database

1 - System Tables in Every Database¶

These tables store database-level system information for each database.

You can view a diagram of the relationships among these tables.

Table	Description
sysobjects	Contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb only, this table includes a row for each temporary object.
sysusers	Contains one row for each Microsoft® Windows user, Windows group, Microsoft SQL Server™ user, or SQL Server role in the database.
syscolumns	Contains one row for every column in every table and view, and a row for each parameter in a stored procedure.
systypes	Contains one row for each system-supplied and each user-defined data type. This table is stored in each database.
sysforeignkeys	Contains information regarding the FOREIGN KEY constraints that are in table definitions.
sysreferences	Contains mappings of FOREIGN KEY constraint definitions to the referenced columns.
sysconstraints	Contains mappings of constraints to the objects that own the constraints.
sysindexes	Contains one row for each index and table in the database.
sysindexkeys	Contains information for the keys or columns in an index.
syscomments	Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The text column contains the original SQL definition statements, which are limited to a maximum size of 4 MB. This table is stored in each database.
sysdepends	Contains dependency information between objects (views, procedures, and triggers), and the objects (tables, views, and procedures) contained in their definition.
sysmembers	Contains a row for each member of a database role.
sysprotects	Contains information about permissions that have been applied to security accounts with the GRANT and DENY statements.
syspermissions	Contains information about permissions granted and denied to users, groups, and roles in the database.
sysfulltextcatalogs	Lists the set of full-text catalogs.
sysfiles	Contains one row for each file in a database. This system table is a virtual table; it cannot be updated or modified directly.
sysfilegroups	Contains one row for each filegroup in a database. There is at least one entry in this table that is for the primary filegroup.

2 - System Tables in the MASTER Database Only¶

These tables store server-level system information.

You can view a diagram of the relationships among these tables.

Table	Description
syscacheobjects	Contains information about how the cache is used.
syslogins	Contains one row for each login account.
sysprocesses	This table holds information about processes running on Microsoft® SQL Server™, both client processes and system processes.
syslockinfo	Contains information on all granted, converting, and waiting lock requests. This table is a denormalized tabular view of internal data structures of the lock manager.

3 - SQL Server Agent Tables in the MSDB Database¶

These tables store information used by SQL Server Agent.

You can view a diagram of the relationships among these tables.

Table	Description
systargetservergroups	Records which target server groups are currently enlisted in this multiserver environment.
systargetservers	Records which target servers are currently enlisted in this multiserver operation domain.
sysjobservers	Stores the association or relationship of a particular job with one or more target servers.
sysjobhistory	Contains information about the execution of scheduled jobs by SQL Server Agent.
sysjobschedules	Contains schedule information for jobs to be executed by SQL Server Agent.
sysdownloadlist	Holds the queue of download instructions for all target servers.
systaskids	Contains a mapping of tasks created in earlier versions of Microsoft® SQL Server™ to SQL Server Enterprise Manager jobs in the current version.
sysjobs	Stores the information for each scheduled job to be executed by SQL Server Agent.
syscategories	Contains the categories used by SQL Server Enterprise Manager to organize jobs, alerts, and operators.
sysoperators	Contains one row for each operator.
systargetservergroupmembers	Records which target servers are currently enlisted in this multiserver group.
sysjobsteps	Contains the information for each step in a job to be executed by SQL Server Agent.
sysalerts	Contains one row for each alert. An alert is a message sent in response to an event. An alert can forward messages beyond the Microsoft® SQL Server™ environment, and an alert can be an e-mail or pager message. An alert also can generate a task.
sysnotifications	Contains one row for each notification.

4 - Database Maintenance Plan Tables in the MSDB Database¶

These tables are used for database maintenance.

You can view a diagram of the relationships among these tables.

• sysdbmainplan_history
• sysdbmaintplan_databases
• sysdbmaintplan_jobs
• sysdbmaintplans
  

5 - Replication Tables¶

5.1 - Replication Tables in Each User Database¶

These tables are used by replication and stored in the user's database.

You can view a diagram of the relationships among these tables.

Table	Publishing Database	Subscribing Database
MSdynamicsnapshotsjobs	x	-
MSdynamicsnapshotviews	x	-
MSmerge_altsyncpartners	x	x
MSmerge_contents	x	x
MSmerge_delete_conflicts	x	x
MSmerge_errorlineage	x	x
MSmerge_genhistory	x	x
MSmerge_replinfo	x	x
MSmerge_tombstone	x	x
MSpub_identity_range	x	-
MSreplication_objects	-	x
MSreplication_queue	-	x
MSreplication_subscriptions	-	x
MSsub_identity_range	-	x
MSsubscription_agents	-	x
MSsubscription_articles	-	x
MSsubscription_properties	-	x
sysarticles	x	-
sysarticleupdates	x	-
sysmergearticles	x	x
sysmergepublications	x	x
sysmergeschemaarticles	x	x
sysmergeschemachange	x	x
sysmergesubscriptions	x	x
sysmergesubsetfilters	x	x
syspublications	x	-
sysschemaarticles	x	-
syssubscriptions	x	-
systranschemas	x	x

5.2 - Replication Tables in Each Distribution Database¶

These tables are used by replication and stored in the distribution database.

You can view a diagram of the relationships among these tables.

• MSarticles
• MSdistribution_agents
• MSdistribution_history
• MSlogreader_agents
• MSlogreader_history
• MSmerge_agents
• MSmerge_history
• MSmerge_subscriptions
• MSpublication_access
• MSpublications
• MSpublisher_databases
• MSqreader_agents
• MSqreader_history
• MSrepl_backup_lsns
• MSrepl_commands
• MSrepl_errors
• MSrepl_identity_range
• MSrepl_originators
• MSrepl_transactions
• MSrepl_version
• MSsnapshot_agents
• MSsnapshot_history
• MSsubscriber_info
• MSsubscriber_schedule
• MSsubscriptions
• MSsync_states
  

5.3 - Replication Tables in the MSDB Database¶

These tables are used by replication and are stored in the msdb database.

You can view a diagram of the relationships among these tables.

• sysdbmaintplan_databases
• sysdbmaintplans
• sysdbmaintplan_jobs
• sysdbmainplan_history
  

6 - Backup Tables in the MSDB Database¶

These tables store information used for database backup and restore operations.

You can view a diagram of the relationships among these tables.

• backupfile
• backupmediafamily
• backupmediaset
• backupset
• logmarkhistory
• restorefile
• restorefilegroup
• restorehistory
  

7 - Log Shipping Tables in the MSDB Database¶

These tables are used to ship transaction logs from one database to another on a constant basis. Log shipping requires SQL Server Enterprise Edition.

You can view a diagram of the relationships among these tables.

• log_shipping_databases
• log_shipping_monitor
• log_shipping_plans
• log_shipping_plan_databases
• log_shipping_plan_history
• log_shipping_primaries
• log_shipping_secondaries