系統資料庫System Databases

[SQL Server]SQL Server 包括下列系統資料庫。includes the following system databases.

系統資料庫System database 描述Description
master 資料庫master Database 記錄 [SQL Server]SQL Server執行個體的所有系統層級資訊。Records all the system-level information for an instance of [SQL Server]SQL Server.
msdb 資料庫msdb Database 由 SQL Server Agent 用於排程警示和作業。Is used by SQL Server Agent for scheduling alerts and jobs.
Model 資料庫model Database 用來當作 [SQL Server]SQL Server執行個體上建立之所有資料庫的範本。Is used as the template for all databases created on the instance of [SQL Server]SQL Server. model 資料庫進行的修改 (例如,資料庫大小、定序、復原模式和其他資料庫選項) 會套用到之後建立的任何資料庫。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 資料庫Resource Database 是一個唯讀的資料庫,其中包含 [SQL Server]SQL Server擁有的系統物件。Is a read-only database that contains system objects that are included with [SQL Server]SQL Server. 系統物件實際上會保存在 Resource 資料庫中,但邏輯上會出現在每個資料庫的 sys 結構描述中。System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
tempdb 資料庫tempdb Database 是保存暫存物件或中繼結果集的工作空間。Is a workspace for holding temporary objects or intermediate result sets.

修改系統資料Modifying System Data

[SQL Server]SQL Server 不支援使用者直接更新系統物件中的資訊,例如系統資料表、系統預存程序和目錄檢視。does not support users directly updating the information in system objects such as system tables, system stored procedures, and catalog views. [SQL Server]SQL Server 另外提供了一組完整的管理工具,讓使用者可以完全管理他們的系統,並管理資料庫中所有的使用者與物件。Instead, [SQL Server]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:

  • 管理公用程式,例如 SQL Server Management StudioSQL Server Management StudioAdministration utilities, such as SQL Server Management StudioSQL Server Management Studio.

  • SQL-SMO API。SQL-SMO API. 可讓程式設計人員加入完整的功能,以在應用程式中管理 [SQL Server]SQL ServerThis lets programmers include complete functionality for administering [SQL Server]SQL Server in their applications.

  • Transact-SQLTransact-SQL 指令碼和預存程序。scripts and stored procedures. 上述項目可以使用系統預存程序和 Transact-SQLTransact-SQL DDL 陳述式。These can use system stored procedures and Transact-SQLTransact-SQL DDL statements.

這些工具可避免應用程式在系統物件中被變更。These tools shield applications from changes in the system objects. 例如, [SQL Server]SQL Server 有時需要在新版的 [SQL Server]SQL Server 中變更系統資料表,以支援加入該版本的新功能。For example, [SQL Server]SQL Server sometimes has to change the system tables in new versions of [SQL Server]SQL Server to support new functionality that is being added in that version. 提出直接參考系統資料表的 SELECT 陳述式之應用程式,它們通常依賴系統資料表的舊有格式。Applications issuing SELECT statements that directly reference system tables are frequently dependent on the old format of the system tables. 站台可能要等到重寫從系統資料表選取的應用程式之後,才能夠升級到新版的 [SQL Server]SQL ServerSites may not be able to upgrade to a new version of [SQL Server]SQL Server until they have rewritten applications that are selecting from system tables. [SQL Server]SQL Server 會考量系統預存程序、DDL 和 SQL-SMO 發行的介面,並努力維護這些介面的回溯相容性。considers the system stored procedures, DDL, and SQL-SMO published interfaces, and works to maintain the backward compatibility of these interfaces.

[SQL Server]SQL Server 並不支援在系統資料表上定義的觸發程序,因為這些觸發程序可能會修改系統的作業。does not support triggers defined on the system tables, because they might modify the operation of the system.

注意

系統資料庫無法位於 UNC 共用目錄。System databases cannot reside on UNC share directories.

檢視系統資料庫資料Viewing System Database Data

您不應撰寫直接查詢系統資料表的 Transact-SQLTransact-SQL 陳述式,除非這是取得應用程式所需資訊的唯一方式。You should not code Transact-SQLTransact-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-SMOSQL-SMO

  • Windows Management Instrumentation (WMI) 介面Windows Management Instrumentation (WMI) interface

  • 用於應用程式的資料 API 之 Catalog 函數、方法、屬性 (attribute) 或屬性 (property),例如 ADO、OLE DB 或 ODBC。Catalog functions, methods, attributes, or properties of the data API used in the application, such as ADO, OLE DB, or ODBC.

  • Transact-SQLTransact-SQL 系統預存程序和內建函數。system stored procedures and built-in functions.

系統資料庫的備份與還原 (SQL Server)Back Up and Restore of System Databases (SQL Server)

在物件總管中隱藏系統物件Hide System Objects in Object Explorer

目錄檢視 (Transact-SQL)Catalog Views (Transact-SQL)

資料庫Databases