System Tables

The information used by Microsoft® SQL Server™ 2000 and its components is stored in special tables known as system tables.

System tables should not be altered directly by any user. For example, do not attempt to modify system tables with DELETE, UPDATE, or INSERT statements, or user-defined triggers.

Reference of documented columns in system tables is permissible. However, many of the columns in system tables are not documented. Applications should not be written to query undocumented columns directly. Applications should instead use any of these components to retrieve information stored in the system tables:

• Information schema views
  
  
• System stored procedures
  
  
• Transact-SQL statements and functions
  
  
• SQL-DMO
  
  
• Database application programming interfaces (API) catalog functions

These components constitute a published API for obtaining system information from SQL Server. Microsoft maintains the compatibility of these components from release to release. The format of the system tables is dependent upon the internal architecture of SQL Server and may change from release to release. Therefore, applications that directly access the undocumented columns of system tables may have to be changed before they can access a later version of SQL Server.

System Tables in the master Database Only

These tables store server-level system information.

sysaltfiles	syslockinfo
syscacheobjects	syslogins
syscharsets	sysmessages
sysconfigures	sysoledbusers
syscurconfigs	sysperfinfo
sysdatabases	sysprocesses
sysdevices	sysremotelogins
syslanguages	sysservers

System Tables in Every Database

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

syscolumns	sysindexkeys
syscomments	sysmembers
sysconstraints	sysobjects
sysdepends	syspermissions
sysfilegroups	sysprotects
sysfiles	sysreferences
sysforeignkeys	systypes
sysfulltextcatalogs	sysusers
sysindexes

SQL Server Agent Tables in the msdb Database

These tables store information used by SQL Server Agent.

sysalerts	sysjobsteps
syscategories	sysnotifications
sysdownloadlist	sysoperators
sysjobhistory	systargetservergroupmembers
sysjobs	systargetservergroups
sysjobschedules	systargetservers
sysjobservers	systaskids

Tables in the msdb Database

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

backupfile	restorefile
backupmediafamily	restorefilegroup
backupmediaset	restorehistory
backupset

Tables Used to Store Replication Information

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

sysdatabases

sysservers

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

sysreplicationalerts

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

MSagent_parameters	Mspublisher_databases
MSagent_profiles	MSreplication_objects
MSarticles	MSreplication_subscriptions
MSdistpublishers	MSrepl_commands
MSdistributiondbs	MSrepl_errors
MSdistribution_agents	MSrepl_originators
MSdistribution_history	MSrepl_transactions
MSdistributor	MSrepl_version
MSlogreader_agents	MSsnapshot_agents
MSlogreader_history	MSsnapshot_history
MSmerge_agents	MSsubscriber_info
MSmerge_history	MSsubscriber_schedule
MSmerge_subscriptions	MSsubscriptions
MSpublication_access	MSsubscription_properties
Mspublications

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

MSmerge_contents	sysmergearticles
MSmerge_delete_conflicts	sysmergepublications
MSmerge_genhistory	sysmergeschemachange
MSmerge_replinfo	sysmergesubscriptions
MSmerge_tombstone	sysmergesubsetfilters
sysarticles	syspublications
sysarticleupdates	syssubscriptions