System Databases

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (2)

Click here to expand

Collapse All

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

System Databases

SQL Server includes the following system databases.

System database	Description
master Database	Records all the system-level information for an instance of SQL Server.
msdb Database	Is used by SQL Server Agent for scheduling alerts and jobs.
model Database	Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.
Resource Database	Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
tempdb Database	Is a workspace for holding temporary objects or intermediate result sets.

Modifying System Data

SQL Server does not support users directly updating the information in system objects such as system tables, system stored procedures, and catalog views. Instead, SQL Server provides a complete set of administrative tools that let users fully administer their system and manage all users and objects in a database. These include the following:

Administration utilities, such as SQL Server Management Studio.
SQL-SMO API. This lets programmers include complete functionality for administering SQL Server in their applications.
Transact-SQL scripts and stored procedures. These can use system stored procedures and Transact-SQL DDL statements.

These tools shield applications from changes in the system objects. For example, SQL Server sometimes has to change the system tables in new versions of SQL Server to support new functionality that is being added in that version. Applications issuing SELECT statements that directly reference system tables are frequently dependent on the old format of the system tables. Sites may not be able to upgrade to a new version of SQL Server until they have rewritten applications that are selecting from system tables. SQL Server considers the system stored procedures, DDL, and SQL-SMO published interfaces, and works to maintain the backward compatibility of these interfaces.

SQL Server does not support triggers defined on the system tables, because they might modify the operation of the system.

Viewing System Database Data

You should not code Transact-SQL statements that directly query the system tables, unless that is the only way to obtain the information that is required by the application. Instead, applications should obtain catalog and system information by using the following:

System catalog views
SQL-SMO
Windows Management Instrumentation (WMI) interface
Catalog functions, methods, attributes, or properties of the data API used in the application, such as ADO, OLE DB, or ODBC.
Transact-SQL system stored procedures and built-in functions.

Concepts

Other Resources

Help and Information