Jasinski Technical Wiki

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

PoweredBy

System Tables - SQL Server 2000

RSS
Modified on Fri, May 01, 2009, 11:04 AM by Administrator Categorized as SQL Server System Tables
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.


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.

TableDescription
sysobjectsContains 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.
sysusersContains one row for each Microsoft® Windows user, Windows group, Microsoft SQL Server™ user, or SQL Server role in the database.
syscolumnsContains one row for every column in every table and view, and a row for each parameter in a stored procedure.
systypesContains one row for each system-supplied and each user-defined data type. This table is stored in each database.
sysforeignkeysContains information regarding the FOREIGN KEY constraints that are in table definitions.
sysreferencesContains mappings of FOREIGN KEY constraint definitions to the referenced columns.
sysconstraintsContains mappings of constraints to the objects that own the constraints.
sysindexesContains one row for each index and table in the database.
sysindexkeysContains information for the keys or columns in an index.
syscommentsContains 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.
sysdependsContains dependency information between objects (views, procedures, and triggers), and the objects (tables, views, and procedures) contained in their definition.
sysmembersContains a row for each member of a database role.
sysprotectsContains information about permissions that have been applied to security accounts with the GRANT and DENY statements.
syspermissionsContains information about permissions granted and denied to users, groups, and roles in the database.
sysfulltextcatalogsLists the set of full-text catalogs.
sysfilesContains one row for each file in a database. This system table is a virtual table; it cannot be updated or modified directly.
sysfilegroupsContains 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.

TableDescription
syscacheobjectsContains information about how the cache is used.
sysloginsContains one row for each login account.
sysprocessesThis table holds information about processes running on Microsoft® SQL Server™, both client processes and system processes.
syslockinfoContains 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.

TableDescription
systargetservergroupsRecords which target server groups are currently enlisted in this multiserver environment.
systargetserversRecords which target servers are currently enlisted in this multiserver operation domain.
sysjobserversStores the association or relationship of a particular job with one or more target servers.
sysjobhistoryContains information about the execution of scheduled jobs by SQL Server Agent.
sysjobschedulesContains schedule information for jobs to be executed by SQL Server Agent.
sysdownloadlistHolds the queue of download instructions for all target servers.
systaskidsContains a mapping of tasks created in earlier versions of Microsoft® SQL Server™ to SQL Server Enterprise Manager jobs in the current version.
sysjobsStores the information for each scheduled job to be executed by SQL Server Agent.
syscategoriesContains the categories used by SQL Server Enterprise Manager to organize jobs, alerts, and operators.
sysoperatorsContains one row for each operator.
systargetservergroupmembersRecords which target servers are currently enlisted in this multiserver group.
sysjobstepsContains the information for each step in a job to be executed by SQL Server Agent.
sysalertsContains 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.
sysnotificationsContains 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.

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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.