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 Table Purpose Description
Backupfile Msdb Lists all files that are backed up
Backupmediafamily Msdb Lists information about media families used for backups
Backupmediaset Msdb Lists information about media sets used for backups
Backupset Msdb Lists all backup sets for the server
MSagent_parameters Distribution Stores parameters used for replication agents on the server
MSagent_profiles Distribution Stores profile information for replication agents on the server
Msarticles Distribution Stores information about articles that are replicated on the server
Msdistpublishers Distribution Stores information about remote publishers on the server
MSdistribution_agents Distribution Stores information about distribution agents
MSdistribution_history Distribution Stores historical information about distribution agents
Msdistributiondbs Distribution Contains database information for each server configured as a distributor
Msdistributor Distribution Stores distribution properties
MSlogreader_agents Distribution Stores log reader agent information
MSlogreader_history Distribution Stores historical data about log reader agents
MSmerge_agents Distribution Stores configuration information about merge agents for a distributor
MSmerge_contents User Contains changes to a publication in a database
MSmerge_delete_conflicts User Lists deleted rows for subscriptions in a database that were removed due to conflicts
MSmerge_genhistory User Contains history of generated subscriptions in a database
MSmerge_history Distribution Contains historical merge information for a distributor
MSmerge_replinfo User Contains information about replication information that has been sent to or received from a subscription article
MSmerge_subscriptions Distribution Contains subscription information used by a distributor
MSmerge_tombstone User Lists deleted rows for subscriptions in a database
MSpublication_access Distribution Stores publications that each SQL Server login has access to
Mspublications Distribution Stores information about each publication that a distributor is responsible for
Mspublisher_databases Distribution Stores information about which databases a distributor is responsible for
MSrepl_commands Distribution Stores information about commands that are replicated
MSrepl_errors Distribution Stores information about errors generated during replication
Msrepl_originators Distribution Stores information relating to subscriber and originator
MSrepl_transactions Distribution Stores information replicated transactions
MSrepl_version Distribution Stores information about the version of replication used for a distributor
MSreplication_objects Distribution Stores information about each object that is to be replicated by a distributor
MSreplication_subscriptions Distribution Stores replication information about each subscription
MSsnapshot_agents Distribution Stores information about each snapshot agent that the distributor is responsible for
MSsnapshot_history Distribution Contains historical information about snapshot replication
MSsubscriber_info Distribution Contains information about subscribers that a distributor is responsible for
MSsubscriber_schedule Distribution Contains information about the schedules of subscribers that a distributor is responsible for
Mssubscription_properties Distribution Contains information about the properties of subscriptions that a distributor is responsible for
Mssubscriptions Distribution Contains information about subscriptions that a distributor is responsible for
Restorefile Msdb Lists all files that are restored for the server
Restorefilegroup Msdb Lists all file groups that are restored for the server
Restorehistory Msdb Lists information about all prior restorations of data for the server
Sysalerts Msdb Stores job alert information for SQL Server
Sysallocations User Stores allocation units in a database
Sysaltfiles Master Lists location of database files and properties of those files, such as growth allowances for the server
Sysarticles Master Stores all defined replication articles in all databases
Sysarticleupdates User Stores all articles in a database that can immediately update any subscriber for the article
Syscacheobjects Master Stores information about the usage of the system cache for the server
Syscategories Msdb Stores categories of job-related information, such as jobs, alerts, and operators
Syscharsets Master Stores all character sets that are installed on SQL Server
Syscolumns User Stores all of the columns and views for all tables of a database
Syscomments User Stores the text that makes up stored procedures, views, rules, defaults, and triggers. However, this text will not be readable if it is encrypted.
Sysconfigures Master Stores all of the configuration options for the server
Sysconstraints User Stores information about all of the constraints in a database
Syscurconfigs Master Stores the current configuration options for the server
Sysdatabases Master Lists all of the databases and their properties for the server
Sysdepends User Lists all dependency information objects which depend on other objects) for all objects in a database.
Sysdevices Master Stores location of backup devices for the server
Sysdownloadlist Msdb Stores all download instructions for all target servers
Sysfilegroups User Stores information about the filegroups created in a database
Sysfiles User Virtual table, or view, that stores information about database files
Sysforeignkeys User Stores all foreign key constraints in a database
Sysfulltextcatalogs User Stores full-text searching catalogs in a database
Sysindexes User Stores all indexing information for all indexes in a database
Sysindexkeys User Stores the key fields that are contained in an index
Sysjobhistory Msdb Stores job history and status for prior jobs in SQL Server
Sysjobs Msdb Stores job information for SQL Server
Sysjobschedules Msdb Stores job scheduling information for SQL Server
Sysjobservers Msdb Stores the relationship of servers that are used in job processing
Sysjobsteps Msdb Stores the steps of each job for SQL Server
Syslanguages Master Stores all languages that are installed on SQL Server
Syslockinfo Master Stores information about current SQL Server locks for the server
Syslogins Master Stores all logins that are allowed to log into SQL Server in a virtual table, or a view
Sysmembers User Stores all users who are members of roles within a database
Sysmergearticles User Stores all merge articles in a database
Sysmergepublications User Stores all merge publications in a database
Sysmergeschemachange User Stores information about snapshot articles in a database
Sysmergesubscriptions User Stores information about subscribers of articles in a database
Sysmergesubsetfilters User Stores information about partitioned articles
Sysmessages Master Lists all error and warning messages
Sysnotifications Msdb Stores job notification information for the server
Sysobjects User Stores all objects in a database, such as stored procedures, views, and triggers
Sysoledbusers Master Stores user name and password for each linked SQL Server
Sysoperators Msdb Stores information about job operators for the server
Sysperfinfo Master Stores information about SQL Server’s performance counters
Syspermissions User Stores permissions for users, groups, and roles in a database
Sysprocesses Master Lists data about the currently running processes in SQL Server
Sysprotects User Stores permissions for objects in a database
Syspublications Master Stores all defined replication publications in all databases
Sysreferences User Stores foreign key mappings for each foreign key defined in a database
Sysremotelogins Master Stores information about logins that are allowed to run remote stored procedures for the server
Sysreplicationalerts Master Stores all defined replication alert conditions in all databases
Sysservers Master Stores all servers that can be accessed by using OLE DB.
Syssubscriptions Master Stores all defined replication publication in all databases
Systargetservergroupmembers Msdb Stores information about which servers are members of server groups
Systargetservergroups Msdb Stores information about groups of servers that are used within jobs
Systargetservers Msdb Stores information about servers that are used within jobs
Systaskids Msdb Stores information about tasks used in prior versions of SQL Server
Systypes User Stores user-defined type information as defined within a database
Sysusers User Stores information about users or roles of a database