Skip to content

system-databases.md

Latest commit

 

History

History
73 lines (53 loc) · 5.09 KB

system-databases.md

File metadata and controls

73 lines (53 loc) · 5.09 KB
title System Databases | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic article
helpviewer_keywords
system databases [SQL Server]
displaying system database data
modifying system data
viewing system database data
ms.assetid 30468a7c-4225-4d35-aa4a-ffa7da4f1282
caps.latest.revision 25
author BYHAM
ms.author rickbyh
manager jhubbard

System Databases

[!INCLUDEssNoVersion] includes the following system databases.

System database Description
master Database Records all the system-level information for an instance of [!INCLUDEssNoVersion].
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 [!INCLUDEssNoVersion]. 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 [!INCLUDEssNoVersion]. 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

[!INCLUDEssNoVersion] does not support users directly updating the information in system objects such as system tables, system stored procedures, and catalog views. Instead, [!INCLUDEssNoVersion] 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 [!INCLUDEssManStudioFull].

  • SQL-SMO API. This lets programmers include complete functionality for administering [!INCLUDEssNoVersion] in their applications.

  • [!INCLUDEtsql] scripts and stored procedures. These can use system stored procedures and [!INCLUDEtsql] DDL statements.

These tools shield applications from changes in the system objects. For example, [!INCLUDEssNoVersion] sometimes has to change the system tables in new versions of [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] until they have rewritten applications that are selecting from system tables. [!INCLUDEssNoVersion] considers the system stored procedures, DDL, and SQL-SMO published interfaces, and works to maintain the backward compatibility of these interfaces.

[!INCLUDEssNoVersion] does not support triggers defined on the system tables, because they might modify the operation of the system.

Note

System databases cannot reside on UNC share directories.

Viewing System Database Data

You should not code [!INCLUDEtsql] 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.

  • [!INCLUDEtsql] system stored procedures and built-in functions.

Related Tasks

Back Up and Restore of System Databases (SQL Server)

Hide System Objects in Object Explorer

Related Content

Catalog Views (Transact-SQL)

Databases