System Tables

Transact-SQL Reference

previous page next page
Transact-SQL Reference

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

previous page start next page