系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)

适用对象:yesSQL Server(从 2016 版开始)yesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server (starting with 2016) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 2017SQL Server 2017 中,可以使用系统存储过程来执行许多管理和信息活动。In SQL Server 2017SQL Server 2017, 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

类别Category DescriptionDescription
活动异地复制存储过程Active Geo-Replication Stored Procedures 用于管理来管理 Azure SQL 数据库中的活动异地复制配置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 Stored Procedures 用于 SQL Server 数据库引擎SQL Server Database Engine的常规维护。Used for general maintenance of the SQL Server 数据库引擎SQL Server Database Engine.
数据库邮件存储过程(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 数据库)Firewall Rules Stored Procedures (Azure SQL Database) 用于配置 Azure SQL 数据库防火墙。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 自动化存储过程OLE Automation Stored Procedures 用于使标准自动化对象能够在标准 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 代理存储过程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 代理用于管理计划的活动和事件驱动的活动。Used by SQL ServerSQL Server Agent to manage scheduled and event-driven activities.
Stretch Database 存储过程Stretch Database Stored Procedures 用于管理延伸数据库。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)
存储过程(数据库引擎) Stored Procedures (Database Engine)
运行存储的过程(OLE DB) Running Stored Procedures (OLE DB)
运行存储的过程 Running Stored Procedures
数据库引擎存储过程(Transact SQL) Database Engine Stored Procedures (Transact-SQL)
运行存储过程Running Stored Procedures