系統預存程序 (Transact-SQL)System Stored Procedures (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL Server 中,許多管理和參考活動,都可以利用系統預存程序加以執行。In SQL ServerSQL Server, many administrative and informational activities can be performed by using system stored procedures. 系統預存程序是以下表所示的類別目錄加以分組。The system stored procedures are grouped into the categories shown in the following table.

本節內容In This Section

CategoryCategory 描述Description
作用中異地複寫預存程序Active Geo-Replication Stored Procedures 用來管理 Azure SQL Database 中的作用中異地複寫組態管理Used to manage to manage Active Geo-Replication configurations in Azure SQL Database
目錄預存程序Catalog Stored Procedures 用來實作 ODBC 資料字典功能,以及隔離 ODBC 應用程式,不讓基礎系統資料表受到變更。Used to implement ODBC data dictionary functions and isolate ODBC applications from changes to underlying system tables.
異動資料擷取預存程序Change Data Capture Stored Procedures 用來啟用、停用或報告異動資料擷取物件。Used to enable, disable, or report on change data capture objects.
資料指標預存程序Cursor Stored Procedures 用來實作游標變數功能。Used to implements cursor variable functionality.
資料收集器預存程序Data Collector Stored Procedures 可搭配資料收集器和下列元件一起使用:收集組、收集項和收集類型。Used to work with the data collector and the following components: collection sets, collection items, and collection types.
Database Engine 預存程序Database Engine Stored Procedures 用於 SQL Server Database EngineSQL Server Database Engine 的一般維護。Used for general maintenance of the SQL Server Database EngineSQL Server Database Engine.
Database Mail 預存程序(Transact SQL)Database Mail Stored Procedures (Transact-SQL) SQL ServerSQL Server 的執行個體內,用來執行電子郵件作業。Used to perform e-mail operations from within an instance of SQL ServerSQL Server.
資料庫維護計畫預存程序Database Maintenance Plan Stored Procedures 用來設定管理資料庫效能所需的核心維護工作。Used to set up core maintenance tasks that are required to manage database performance.
分散式的查詢預存程序Distributed Queries Stored Procedures 用來實作和管理分散式查詢。Used to implement and manage distributed queries.
Filestream 和 FileTable 預存程序(Transact SQL)Filestream and FileTable Stored Procedures (Transact-SQL) 用來設定與管理 FILESTREAM 和 FileTable 功能。Used to configure and manage the FILESTREAM and FileTable features.
防火牆規則預存程序(Azure SQL Database)Firewall Rules Stored Procedures (Azure SQL Database) 用來設定 Azure SQL Database 防火牆。Used to configure the Azure SQL Database firewall.
全文檢索搜尋預存程序Full-Text Search Stored Procedures 用來實作和查詢全文檢索索引。Used to implement and query full-text indexes.
一般擴充預存程序General Extended Stored Procedures 用來針對各種維護活動,提供從 SQL ServerSQL Server 執行個體到外部程式的介面。Used to provide an interface from an instance of SQL ServerSQL Server to external programs for various maintenance activities.
記錄傳送預存程序Log Shipping Stored Procedures 用來設定、修改和監視記錄傳送組態。Used to configure, modify, and monitor log shipping configurations.
管理資料倉儲預存程序(Transact SQL)Management Data Warehouse Stored Procedures (Transact-SQL) 用來設定管理資料倉儲。Used to configure the management data warehouse.
OLE Automation 預存程序OLE Automation Stored Procedures 用來啟用標準 Automation 物件,以供標準 Transact-SQLTransact-SQL 批次使用。Used to enable standard Automation objects for use within a standard Transact-SQLTransact-SQL batch.
以原則為基礎的管理預存程序Policy-Based Management Stored Procedures 用於以原則為基礎的管理。Used for Policy-Based Management.
PolyBase 預存程序PolyBase stored procedures 新增或移除 PolyBase 向外延展群組中的電腦。Add or remove a computer from a PolyBase scale-out group.
查詢存放區預存程序(Transact SQL)Query Store Stored Procedures (Transact-SQL) 用來微調效能。Used to tune performance.
複寫預存程序Replication Stored Procedures 用來管理複寫。Used to manage replication.
安全性預存程序Security Stored Procedures 用來管理安全性。Used to manage security.
快照集備份的預存程序Snapshot Backup Stored Procedures 用來刪除 FILE_SNAPSHOT 備份,以及其所有快照集,或刪除的個別備份檔案快照集。Used to delete the FILE_SNAPSHOT backup along with all of its snapshots or to delete an individual backup file snapshot.
空間索引預存程序Spatial Index Stored Procedures 用來分析及改善的空間索引編製索引的效能。Used to analyze and improve the indexing performance of spatial indexes.
SQL Server Agent 預存程序SQL Server Agent Stored Procedures SQL Server ProfilerSQL Server Profiler 用來監視效能及活動。Used by SQL Server ProfilerSQL Server Profiler to monitor performance and activity.
SQL Server Profiler 預存程序SQL Server Profiler Stored Procedures SQL ServerSQL Server Agent 用來管理已排程和事件驅動的活動。Used by SQL ServerSQL Server Agent to manage scheduled and event-driven activities.
Stretch Database 預存程序Stretch Database Stored Procedures 用來管理 stretch database。Used to manage stretch databases.
時態表的預存程序Temporal Tables Stored Procedures 使用時態表Use for temporal tables
XML 預存程序XML Stored Procedures 用於 XML 文字管理。Used for XML text management.

注意

除非特別說明,否則所有系統預存程序都會傳回 0 值,以表示成功。Unless specifically documented otherwise, all system stored procedures return a value of 0 to indicate success. 若要表示失敗,則傳回非零值。To indicate failure, a nonzero value is returned.

API 系統預存程序API System Stored Procedures

對 ADO、OLE DB 和 ODBC 應用程式執行 SQL Server ProfilerSQL Server Profiler 的使用者,可能會注意到這些使用系統預存程序的應用程式沒有涵蓋在 Transact-SQLTransact-SQL 參考中。Users that run SQL Server ProfilerSQL Server Profiler against ADO, OLE DB, and ODBC applications may notice these applications using system stored procedures that are not covered in the Transact-SQLTransact-SQL Reference. 這些預存程序由MicrosoftMicrosoft SQL ServerSQL Server Native Client OLE DB 提供者和SQL ServerSQL ServerNative Client ODBC 驅動程式,來實作資料庫 API 的功能。These stored procedures are used by the MicrosoftMicrosoft SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver to implement the functionality of a database API. 這些預存程序只是提供者或驅動程式將使用者要求傳給 SQL ServerSQL Server 執行個體所用的機制。These stored procedures are just the mechanism the provider or driver uses to communicate user requests to an instance of SQL ServerSQL Server. 它們只做為提供者或驅動程式內部使用。They are intended only for the internal use of the provider or the driver. 呼叫它們明確地從SQL ServerSQL Server-不支援架構的應用程式。Calling them explicitly from a SQL ServerSQL Server-based application is not supported.

Sp_createorphan 和 sp_droporphans 預存程序用於 ODBC ntext文字,並映像處理。The sp_createorphan and sp_droporphans stored procedures are used for ODBC ntext, text, and image processing.

sp_reset_connection 預存程序是由 SQL ServerSQL Server 用來支援交易中的遠端預存程序呼叫。The sp_reset_connection stored procedure is used by SQL ServerSQL Server to support remote stored procedure calls in a transaction. 當重複使用連接集區中的連接時,這個預存程序也會引發 Audit Login 和 Audit Logout 事件。This stored procedure also causes Audit Login and Audit Logout events to fire when a connection is reused from a connection pool.

下列資料表中的系統預存程序,只能用在 SQL ServerSQL Server 或執行個體內,或是透過用戶端 API 使用,不供一般客戶使用。The system stored procedures in the following tables are used only within an instance of SQL ServerSQL Server or through client APIs and are not intended for general customer use. 它們隨時可以變更,而且不保證其相容性。They are subject to change and compatibility is not guaranteed.

下列預存程序記載於《SQL ServerSQL Server 線上叢書》:The following stored procedures are documented in SQL ServerSQL Server Books Online:

sp_catalogssp_catalogs sp_column_privilegessp_column_privileges
sp_column_privileges_exsp_column_privileges_ex sp_columnssp_columns
sp_columns_exsp_columns_ex sp_databasessp_databases
sp_cursorsp_cursor sp_cursorclosesp_cursorclose
sp_cursorexecutesp_cursorexecute sp_cursorfetchsp_cursorfetch
sp_cursoroptionsp_cursoroption sp_cursoropensp_cursoropen
sp_cursorpreparesp_cursorprepare sp_cursorprepexecsp_cursorprepexec
sp_cursorunpreparesp_cursorunprepare sp_executesp_execute
sp_datatype_infosp_datatype_info sp_fkeyssp_fkeys
sp_foreignkeyssp_foreignkeys sp_indexessp_indexes
sp_pkeyssp_pkeys sp_primarykeyssp_primarykeys
sp_preparesp_prepare sp_prepexecsp_prepexec
sp_prepexecrpcsp_prepexecrpc sp_unpreparesp_unprepare
sp_server_infosp_server_info sp_special_columnssp_special_columns
sp_sproc_columnssp_sproc_columns sp_statisticssp_statistics
sp_table_privilegessp_table_privileges sp_table_privileges_exsp_table_privileges_ex
sp_tablessp_tables sp_tables_exsp_tables_ex

沒有記載下列預存程序:The following stored procedures are not documented:

sp_assemblies_rowsetsp_assemblies_rowset sp_assemblies_rowset_rmtsp_assemblies_rowset_rmt
sp_assemblies_rowset2sp_assemblies_rowset2 sp_assembly_dependencies_rowsetsp_assembly_dependencies_rowset
sp_assembly_dependencies_rowset_rmtsp_assembly_dependencies_rowset_rmt sp_assembly_dependencies_rowset2sp_assembly_dependencies_rowset2
sp_bcp_dbcmptlevelsp_bcp_dbcmptlevel sp_catalogs_rowsetsp_catalogs_rowset
sp_catalogs_rowset;2sp_catalogs_rowset;2 sp_catalogs_rowset;5sp_catalogs_rowset;5
sp_catalogs_rowset_rmtsp_catalogs_rowset_rmt sp_catalogs_rowset2sp_catalogs_rowset2
sp_check_constbytable_rowsetsp_check_constbytable_rowset sp_check_constbytable_rowset;2sp_check_constbytable_rowset;2
sp_check_constbytable_rowset2sp_check_constbytable_rowset2 sp_check_constraints_rowsetsp_check_constraints_rowset
sp_check_constraints_rowset;2sp_check_constraints_rowset;2 sp_check_constraints_rowset2sp_check_constraints_rowset2
sp_column_privileges_rowsetsp_column_privileges_rowset sp_column_privileges_rowset;2sp_column_privileges_rowset;2
sp_column_privileges_rowset;5sp_column_privileges_rowset;5 sp_column_privileges_rowset_rmtsp_column_privileges_rowset_rmt
sp_column_privileges_rowset2sp_column_privileges_rowset2 sp_columns_90sp_columns_90
sp_columns_90_rowsetsp_columns_90_rowset sp_columns_90_rowset_rmtsp_columns_90_rowset_rmt
sp_columns_90_rowset2sp_columns_90_rowset2 sp_columns_ex_90sp_columns_ex_90
sp_columns_rowsetsp_columns_rowset sp_columns_rowset;2sp_columns_rowset;2
sp_columns_rowset;5sp_columns_rowset;5 sp_columns_rowset_rmtsp_columns_rowset_rmt
sp_columns_rowset2sp_columns_rowset2 sp_constr_col_usage_rowsetsp_constr_col_usage_rowset
sp_datatype_info_90sp_datatype_info_90 sp_ddopen;1sp_ddopen;1
sp_ddopen;10sp_ddopen;10 sp_ddopen;11sp_ddopen;11
sp_ddopen;12sp_ddopen;12 sp_ddopen;13sp_ddopen;13
sp_ddopen;2sp_ddopen;2 sp_ddopen;3sp_ddopen;3
sp_ddopen;4sp_ddopen;4 sp_ddopen;5sp_ddopen;5
sp_ddopen;6sp_ddopen;6 sp_ddopen;7sp_ddopen;7
sp_ddopen;8sp_ddopen;8 sp_ddopen;9sp_ddopen;9
sp_foreign_keys_rowsetsp_foreign_keys_rowset sp_foreign_keys_rowset;2sp_foreign_keys_rowset;2
sp_foreign_keys_rowset;3sp_foreign_keys_rowset;3 sp_foreign_keys_rowset;5sp_foreign_keys_rowset;5
sp_foreign_keys_rowset_rmtsp_foreign_keys_rowset_rmt sp_foreign_keys_rowset2sp_foreign_keys_rowset2
sp_foreign_keys_rowset3sp_foreign_keys_rowset3 sp_indexes_90_rowsetsp_indexes_90_rowset
sp_indexes_90_rowset_rmtsp_indexes_90_rowset_rmt sp_indexes_90_rowset2sp_indexes_90_rowset2
sp_indexes_rowsetsp_indexes_rowset sp_indexes_rowset;2sp_indexes_rowset;2
sp_indexes_rowset;5sp_indexes_rowset;5 sp_indexes_rowset_rmtsp_indexes_rowset_rmt
sp_indexes_rowset2sp_indexes_rowset2 sp_linkedservers_rowsetsp_linkedservers_rowset
sp_linkedservers_rowset;2sp_linkedservers_rowset;2 sp_linkedservers_rowset2sp_linkedservers_rowset2
sp_oledb_databasesp_oledb_database sp_oledb_defdbsp_oledb_defdb
sp_oledb_deflangsp_oledb_deflang sp_oledb_languagesp_oledb_language
sp_oledb_ro_usrnamesp_oledb_ro_usrname sp_primary_keys_rowsetsp_primary_keys_rowset
sp_primary_keys_rowset;2sp_primary_keys_rowset;2 sp_primary_keys_rowset;3sp_primary_keys_rowset;3
sp_primary_keys_rowset;5sp_primary_keys_rowset;5 sp_primary_keys_rowset_rmtsp_primary_keys_rowset_rmt
sp_primary_keys_rowset2sp_primary_keys_rowset2 sp_procedure_params_90_rowsetsp_procedure_params_90_rowset
sp_procedure_params_90_rowset2sp_procedure_params_90_rowset2 sp_procedure_params_rowsetsp_procedure_params_rowset
sp_procedure_params_rowset;2sp_procedure_params_rowset;2 sp_procedure_params_rowset2sp_procedure_params_rowset2
sp_procedures_rowsetsp_procedures_rowset sp_procedures_rowset;2sp_procedures_rowset;2
sp_procedures_rowset2sp_procedures_rowset2 sp_provider_types_90_rowsetsp_provider_types_90_rowset
sp_provider_types_rowsetsp_provider_types_rowset sp_schemata_rowsetsp_schemata_rowset
sp_schemata_rowset;3sp_schemata_rowset;3 sp_special_columns_90sp_special_columns_90
sp_sproc_columns_90sp_sproc_columns_90 sp_statistics_rowsetsp_statistics_rowset
sp_statistics_rowset;2sp_statistics_rowset;2 sp_statistics_rowset2sp_statistics_rowset2
sp_stored_proceduressp_stored_procedures sp_table_constraints_rowsetsp_table_constraints_rowset
sp_table_constraints_rowset;2sp_table_constraints_rowset;2 sp_table_constraints_rowset2sp_table_constraints_rowset2
sp_table_privileges_rowsetsp_table_privileges_rowset sp_table_privileges_rowset;2sp_table_privileges_rowset;2
sp_table_privileges_rowset;5sp_table_privileges_rowset;5 sp_table_privileges_rowset_rmtsp_table_privileges_rowset_rmt
sp_table_privileges_rowset2sp_table_privileges_rowset2 sp_table_statistics_rowsetsp_table_statistics_rowset
sp_table_statistics_rowset;2sp_table_statistics_rowset;2 sp_table_statistics2_rowsetsp_table_statistics2_rowset
sp_tablecollationssp_tablecollations sp_tablecollations_90sp_tablecollations_90
sp_tables_info_90_rowsetsp_tables_info_90_rowset sp_tables_info_90_rowset_64sp_tables_info_90_rowset_64
sp_tables_info_90_rowset2sp_tables_info_90_rowset2 sp_tables_info_90_rowset2_64sp_tables_info_90_rowset2_64
sp_tables_info_rowsetsp_tables_info_rowset sp_tables_info_rowset;2sp_tables_info_rowset;2
sp_tables_info_rowset_64sp_tables_info_rowset_64 sp_tables_info_rowset_64;2sp_tables_info_rowset_64;2
sp_tables_info_rowset2sp_tables_info_rowset2 sp_tables_info_rowset2_64sp_tables_info_rowset2_64
sp_tables_rowset;2sp_tables_rowset;2 sp_tables_rowset;5sp_tables_rowset;5
sp_tables_rowset_rmtsp_tables_rowset_rmt sp_tables_rowset2sp_tables_rowset2
sp_usertypes_rowsetsp_usertypes_rowset sp_usertypes_rowset_rmtsp_usertypes_rowset_rmt
sp_usertypes_rowset2sp_usertypes_rowset2 sp_views_rowsetsp_views_rowset
sp_views_rowset2sp_views_rowset2 sp_xml_schema_rowsetsp_xml_schema_rowset
sp_xml_schema_rowset2sp_xml_schema_rowset2

另請參閱See Also

CREATE PROCEDURE (Transact-SQL) CREATE PROCEDURE (Transact-SQL)
預存程序 (Database Engine) Stored Procedures (Database Engine)
執行預存程序(OLE DB) Running Stored Procedures (OLE DB)
執行預存程序 Running Stored Procedures
Database Engine 預存程序(Transact SQL) Database Engine Stored Procedures (Transact-SQL)
執行預存程序Running Stored Procedures