SQL-Query Basics  «Prev 

System Catalog Repository

SQL-Server System Catalogs

Depending on the type of information being stored, the system table might reside in only the
  1. master database,
  2. msdb database,
  3. database used for distribution,
  4. replication,
  5. subscription, or
  6. a user-created database.

The table below lists all of the system tables, their location, and a brief description of the table.
Note that you may never need many of these tables, but it is a good idea to read through the table to understand the breadth, depth, and complexity of the information stored in SQL Server. During the course of your career using SQL Server 2012, you will need to access some of these tables.

System TablePurposeDescription
BackupfileMsdbLists all files that are backed up
BackupmediafamilyMsdbLists information about media families used for backups
BackupmediasetMsdbLists information about media sets used for backups
BackupsetMsdbLists all backup sets for the server
MSagent_parametersDistributionStores parameters used for replication agents on the server
MSagent_profilesDistributionStores profile information for replication agents on the server
MsarticlesDistributionStores information about articles that are replicated on the server
MsdistpublishersDistributionStores information about remote publishers on the server
MSdistribution_agentsDistributionStores information about distribution agents
MSdistribution_historyDistributionStores historical information about distribution agents
MsdistributiondbsDistributionContains database information for each server configured as a distributor
MsdistributorDistributionStores distribution properties
MSlogreader_agentsDistributionStores log reader agent information
MSlogreader_historyDistributionStores historical data about log reader agents
MSmerge_agentsDistributionStores configuration information about merge agents for a distributor
MSmerge_contentsUserContains changes to a publication in a database
MSmerge_delete_conflictsUserLists deleted rows for subscriptions in a database that were removed due to conflicts
MSmerge_genhistoryUserContains history of generated subscriptions in a database
MSmerge_historyDistributionContains historical merge information for a distributor
MSmerge_replinfoUserContains information about replication information that has been sent to or received from a subscription article
MSmerge_subscriptionsDistributionContains subscription information used by a distributor
MSmerge_tombstoneUserLists deleted rows for subscriptions in a database
MSpublication_accessDistributionStores publications that each SQL Server login has access to
MspublicationsDistributionStores information about each publication that a distributor is responsible for
Mspublisher_databasesDistributionStores information about which databases a distributor is responsible for
MSrepl_commandsDistributionStores information about commands that are replicated
MSrepl_errorsDistributionStores information about errors generated during replication
Msrepl_originatorsDistributionStores information relating to subscriber and originator
MSrepl_transactionsDistributionStores information replicated transactions
MSrepl_versionDistributionStores information about the version of replication used for a distributor
MSreplication_objectsDistributionStores information about each object that is to be replicated by a distributor
MSreplication_subscriptionsDistributionStores replication information about each subscription
MSsnapshot_agentsDistributionStores information about each snapshot agent that the distributor is responsible for
MSsnapshot_historyDistributionContains historical information about snapshot replication
MSsubscriber_infoDistributionContains information about subscribers that a distributor is responsible for
MSsubscriber_scheduleDistributionContains information about the schedules of subscribers that a distributor is responsible for
Mssubscription_propertiesDistributionContains information about the properties of subscriptions that a distributor is responsible for
MssubscriptionsDistributionContains information about subscriptions that a distributor is responsible for
RestorefileMsdbLists all files that are restored for the server
RestorefilegroupMsdbLists all file groups that are restored for the server
RestorehistoryMsdbLists information about all prior restorations of data for the server
SysalertsMsdbStores job alert information for SQL Server
SysallocationsUserStores allocation units in a database
SysaltfilesMasterLists location of database files and properties of those files, such as growth allowances for the server
SysarticlesMasterStores all defined replication articles in all databases
SysarticleupdatesUserStores all articles in a database that can immediately update any subscriber for the article
SyscacheobjectsMasterStores information about the usage of the system cache for the server
SyscategoriesMsdbStores categories of job-related information, such as jobs, alerts, and operators
SyscharsetsMasterStores all character sets that are installed on SQL Server
SyscolumnsUserStores all of the columns and views for all tables of a database
SyscommentsUserStores the text that makes up stored procedures, views, rules, defaults, and triggers. However, this text will not be readable if it isencrypted.
SysconfiguresMasterStores all of the configuration options for the server
SysconstraintsUserStores information about all of the constraints in a database
SyscurconfigsMasterStores the current configuration options for the server
SysdatabasesMasterLists all of the databases and their properties for the server
SysdependsUserLists all dependency information objects which depend on other objects) for all objects in a database.
SysdevicesMasterStores location of backup devices for the server
SysdownloadlistMsdbStores all download instructions for all target servers
SysfilegroupsUserStores information about the filegroups created in a database
SysfilesUserVirtual table, or view, that stores information about database files
SysforeignkeysUserStores all foreign key constraints in a database
SysfulltextcatalogsUserStores full-text searching catalogs in a database
SysindexesUserStores all indexing information for all indexes in a database
SysindexkeysUserStores the key fields that are contained in an index
SysjobhistoryMsdbStores job history and status for prior jobs in SQL Server
SysjobsMsdbStores job information for SQL Server
SysjobschedulesMsdbStores job scheduling information for SQL Server
SysjobserversMsdbStores the relationship of servers that are used in job processing
SysjobstepsMsdbStores the steps of each job for SQL Server
SyslanguagesMasterStores all languages that are installed on SQL Server
SyslockinfoMasterStores information about current SQL Server locks for the server
SysloginsMasterStores all logins that are allowed to log into SQL Server in a virtual table, or a view
SysmembersUserStores all users who are members of roles within a database
SysmergearticlesUserStores all merge articles in a database
SysmergepublicationsUserStores all merge publications in a database
SysmergeschemachangeUserStores information about snapshot articles in a database
SysmergesubscriptionsUserStores information about subscribers of articles in a database
SysmergesubsetfiltersUserStores information about partitioned articles
SysmessagesMasterLists all error and warning messages
SysnotificationsMsdbStores job notification information for the server
SysobjectsUserStores all objects in a database, such as stored procedures, views, and triggers
SysoledbusersMasterStores user name and password for each linked SQL Server
SysoperatorsMsdbStores information about job operators for the server
SysperfinfoMasterStores information about SQL Server’s performance counters
SyspermissionsUserStores permissions for users, groups, and roles in a database
SysprocessesMasterLists data about the currently running processes in SQL Server
SysprotectsUserStores permissions for objects in a database
SyspublicationsMasterStores all defined replication publications in all databases
SysreferencesUserStores foreign key mappings for each foreign key defined in a database
SysremoteloginsMasterStores information about logins that are allowed to run remote stored procedures for the server
SysreplicationalertsMasterStores all defined replication alert conditions in all databases
SysserversMasterStores all servers that can be accessed by using OLE DB.
SyssubscriptionsMasterStores all defined replication publication in all databases
SystargetservergroupmembersMsdbStores information about which servers are members of server groups
SystargetservergroupsMsdbStores information about groups of servers that are used within jobs
SystargetserversMsdbStores information about servers that are used within jobs
SystaskidsMsdbStores information about tasks used in prior versions of SQL Server
SystypesUserStores user-defined type information as defined within a database
SysusersUserStores information about users or roles of a database