SQL-Query Basics  «Prev  Next»
Lesson 4 System catalogs
ObjectiveDescribe How to use SQL Server System Catalogs

How to use SQ-Server System Catalogs

System catalogs are tables used by SQL Server to store information about databases, users, and security.
System catalogs are sometimes said to store meta-data because they store data that describes the data contained within
  1. databases,
  2. tables,
  4. stored procedures, and
other database objects. Each system catalog is a SQL Server table, which you can access by issuing a query against that table.
A Database Administrator (DBA) might use system catalogs when he/she is trying to retrieve information about the number of users, locks, or other system-related information. There are over 200 system catalogs, located in various SQL Server databases, including user-created databases.

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

  1. Meta-data: Information (data) about database structures. For example, the meta-data about a table includes its name, its column names, and the filegroups in which it resides. In other words, meta-data is data about data.
  2. System catalogs: Also known as system tables, these tables store meta-data, which is data that describes other data.
System catalogs are also referred to as system tables.

Example: the Sysobjects table

The Sysobjects table stores all objects in a database. If you wanted to identify the user-defined tables in your Timesheets database, you could issue the statement shown in the following Slideshow:

1) The first step is to switch to the appropriate database by issuing the USE command, followed by the name of the desired database, Timesheets
USE Timesheets
FROM sysobjects
WHERE type = 'U'
The first step is to switch to the appropriate database by issuing the USE command, followed by the name of the desired database, Timesheets

2) The GO statement informs SQL Server that a new batch has begun
2) The GO statement informs SQL Server that a new batch has begun.

3) The SELECT statement designates which columns from the sysobjects table will be returned by the query.
3) The SELECT statement designates which columns from the sysobjects table will be returned by the query

4) The FROM clause designates that the sysobjects table will be used in this query.
4) The FROM clause designates that the sysobjects table will be used in this query.

5) The WHERE clause limits the returned rows to those that are type 'U', which stands for 'User Table'.
5) The WHERE clause limits the returned rows to those that are type 'U', which stands for 'User Table'.
USE Timesheets
FROM sysobjects
WHERE type = 'U'

SQL Server Use Command

USE { database }

-- Azure SQL Data Warehouse and Parallel Data Warehouse
USE database_name 

SQL Server Arguments

database: Is the name of the database or database snapshot to which the user context is switched. Database and database snapshot names must comply with the rules for identifiers.
In Azure SQL Database, the database parameter can only refer to the current database. The USE statement does not switch between databases, and will result in
error code 40508 is returned. To change databases, you must directly connect to the database.

You will receive an error if you query a system table that does not exist in the current database. Therefore, always change to the desired database before issuing your query against a system table.

Example: the Sysusers table

The Sysusers table stores information about users or roles of a database. The following statement will identify which users have access to the Timesheets database.

USE Timesheets
FROM sysusers
WHERE issqlrole = 0

Notice the WHERE clause, which specifies that no SQL Server role records will be returned. If you use this WHERE clause, your returned data won't be cluttered with these roles if you only want to see users.
A group of users who are allowed the same access permission to certain objects. There are two main types of roles that come pre-installed in SQL Server 2012.0:..1. Fixed Server Role - allowed to perform administrative functions on specific, pre-defined categories of built-in objects.
2. Fixed Database Role - allowed to perform database-specific tasks, such as INSERTING, UPDATING, DELETING, or SELECTING data. In the next lesson, you will learn about and practice writing logic within queries.

System Catalogs Quiz

Before moving on to the next module, click the Quiz link below to check your knowledge of batch queries and system catalogs with a short, multiple-choice quiz.
System Catalogs - Quiz