| title | System Stored Procedures (Transact-SQL) | Microsoft Docs | |||||
|---|---|---|---|---|---|---|
| ms.custom | ||||||
| ms.date | 02/21/2016 | |||||
| ms.prod | sql-non-specified | |||||
| ms.prod_service | database-engine, sql-database | |||||
| ms.service | ||||||
| ms.component | system-stored-procedures | |||||
| ms.reviewer | ||||||
| ms.suite | sql | |||||
| ms.technology |
|
|||||
| ms.tgt_pltfrm | ||||||
| ms.topic | language-reference | |||||
| applies_to |
|
|||||
| f1_keywords |
|
|||||
| dev_langs |
|
|||||
| helpviewer_keywords |
|
|||||
| ms.assetid | a5c4d5b8-5a24-4a2d-99b4-d003b546ee3a | |||||
| caps.latest.revision | 49 | |||||
| author | edmacauley | |||||
| ms.author | edmaca | |||||
| manager | cguyer | |||||
| ms.workload | On Demand |
[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx-md]
In [!INCLUDEssCurrent], 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.
| Category | Description |
|---|---|
| Active Geo-Replication Stored Procedures | Used to manage to manage Active Geo-Replication configurations in Azure SQL Database |
| Catalog Stored Procedures | 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 | Used for general maintenance of the [!INCLUDEssDEnoversion]. |
| Database Mail Stored Procedures (Transact-SQL) | Used to perform e-mail operations from within an instance of [!INCLUDEssNoVersion]. |
| 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 and FileTable Stored Procedures (Transact-SQL) | Used to configure and manage the FILESTREAM and FileTable features. |
| Firewall Rules Stored Procedures (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 | Used to provide an interface from an instance of [!INCLUDEssNoVersion] to external programs for various maintenance activities. |
| Log Shipping Stored Procedures | Used to configure, modify, and monitor log shipping configurations. |
| Management Data Warehouse Stored Procedures (Transact-SQL) | Used to configure the management data warehouse. |
| OLE Automation Stored Procedures | Used to enable standard Automation objects for use within a standard [!INCLUDEtsql] batch. |
| Policy-Based Management Stored Procedures | Used for Policy-Based Management. |
| PolyBase stored procedures | Add or remove a computer from a PolyBase scale-out group. |
| 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 | 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 Stored Procedures | Used by [!INCLUDEssSqlProfiler] to monitor performance and activity. |
| SQL Server Profiler Stored Procedures | Used by [!INCLUDEssNoVersion] Agent to manage scheduled and event-driven activities. |
| Stretch Database Stored Procedures | Used to manage stretch databases. |
| Temporal Tables Stored Procedures | Use for temporal tables |
| XML Stored Procedures | Used for XML text management. |
Note
Unless specifically documented otherwise, all system stored procedures return a value of 0 to indicate success. To indicate failure, a nonzero value is returned.
Users that run [!INCLUDEssSqlProfiler] against ADO, OLE DB, and ODBC applications may notice these applications using system stored procedures that are not covered in the [!INCLUDEtsql] Reference. These stored procedures are used by the [!INCLUDEmsCoName] [!INCLUDEssNoVersion] Native Client OLE DB Provider and the [!INCLUDEssNoVersion] Native Client ODBC driver to implement the functionality of a database API. These stored procedures are just the mechanism the provider or driver uses to communicate user requests to an instance of [!INCLUDEssNoVersion]. They are intended only for the internal use of the provider or the driver. Calling them explicitly from a [!INCLUDEssNoVersion]-based application is not supported.
The sp_createorphan and sp_droporphans stored procedures are used for ODBC ntext, text, and image processing.
The sp_reset_connection stored procedure is used by [!INCLUDEssNoVersion] to support remote stored procedure calls in a transaction. This stored procedure also causes Audit Login and Audit Logout events to fire when a connection is reused from a connection pool.
The system stored procedures in the following tables are used only within an instance of [!INCLUDEssNoVersion] or through client APIs and are not intended for general customer use. They are subject to change and compatibility is not guaranteed.
The following stored procedures are documented in [!INCLUDEssNoVersion] Books Online:
| sp_catalogs | sp_column_privileges |
| sp_column_privileges_ex | sp_columns |
| sp_columns_ex | sp_databases |
| sp_cursor | sp_cursorclose |
| sp_cursorexecute | sp_cursorfetch |
| sp_cursoroption | sp_cursoropen |
| sp_cursorprepare | sp_cursorprepexec |
| sp_cursorunprepare | sp_execute |
| sp_datatype_info | sp_fkeys |
| sp_foreignkeys | sp_indexes |
| sp_pkeys | sp_primarykeys |
| sp_prepare | sp_prepexec |
| sp_prepexecrpc | sp_unprepare |
| sp_server_info | sp_special_columns |
| sp_sproc_columns | sp_statistics |
| sp_table_privileges | sp_table_privileges_ex |
| sp_tables | sp_tables_ex |
The following stored procedures are not documented:
| sp_assemblies_rowset | sp_assemblies_rowset_rmt |
| sp_assemblies_rowset2 | sp_assembly_dependencies_rowset |
| sp_assembly_dependencies_rowset_rmt | sp_assembly_dependencies_rowset2 |
| sp_bcp_dbcmptlevel | sp_catalogs_rowset |
| sp_catalogs_rowset;2 | sp_catalogs_rowset;5 |
| sp_catalogs_rowset_rmt | sp_catalogs_rowset2 |
| sp_check_constbytable_rowset | sp_check_constbytable_rowset;2 |
| sp_check_constbytable_rowset2 | sp_check_constraints_rowset |
| sp_check_constraints_rowset;2 | sp_check_constraints_rowset2 |
| sp_column_privileges_rowset | sp_column_privileges_rowset;2 |
| sp_column_privileges_rowset;5 | sp_column_privileges_rowset_rmt |
| sp_column_privileges_rowset2 | sp_columns_90 |
| sp_columns_90_rowset | sp_columns_90_rowset_rmt |
| sp_columns_90_rowset2 | sp_columns_ex_90 |
| sp_columns_rowset | sp_columns_rowset;2 |
| sp_columns_rowset;5 | sp_columns_rowset_rmt |
| sp_columns_rowset2 | sp_constr_col_usage_rowset |
| sp_datatype_info_90 | sp_ddopen;1 |
| sp_ddopen;10 | sp_ddopen;11 |
| sp_ddopen;12 | sp_ddopen;13 |
| sp_ddopen;2 | sp_ddopen;3 |
| sp_ddopen;4 | sp_ddopen;5 |
| sp_ddopen;6 | sp_ddopen;7 |
| sp_ddopen;8 | sp_ddopen;9 |
| sp_foreign_keys_rowset | sp_foreign_keys_rowset;2 |
| sp_foreign_keys_rowset;3 | sp_foreign_keys_rowset;5 |
| sp_foreign_keys_rowset_rmt | sp_foreign_keys_rowset2 |
| sp_foreign_keys_rowset3 | sp_indexes_90_rowset |
| sp_indexes_90_rowset_rmt | sp_indexes_90_rowset2 |
| sp_indexes_rowset | sp_indexes_rowset;2 |
| sp_indexes_rowset;5 | sp_indexes_rowset_rmt |
| sp_indexes_rowset2 | sp_linkedservers_rowset |
| sp_linkedservers_rowset;2 | sp_linkedservers_rowset2 |
| sp_oledb_database | sp_oledb_defdb |
| sp_oledb_deflang | sp_oledb_language |
| sp_oledb_ro_usrname | sp_primary_keys_rowset |
| sp_primary_keys_rowset;2 | sp_primary_keys_rowset;3 |
| sp_primary_keys_rowset;5 | sp_primary_keys_rowset_rmt |
| sp_primary_keys_rowset2 | sp_procedure_params_90_rowset |
| sp_procedure_params_90_rowset2 | sp_procedure_params_rowset |
| sp_procedure_params_rowset;2 | sp_procedure_params_rowset2 |
| sp_procedures_rowset | sp_procedures_rowset;2 |
| sp_procedures_rowset2 | sp_provider_types_90_rowset |
| sp_provider_types_rowset | sp_schemata_rowset |
| sp_schemata_rowset;3 | sp_special_columns_90 |
| sp_sproc_columns_90 | sp_statistics_rowset |
| sp_statistics_rowset;2 | sp_statistics_rowset2 |
| sp_stored_procedures | sp_table_constraints_rowset |
| sp_table_constraints_rowset;2 | sp_table_constraints_rowset2 |
| sp_table_privileges_rowset | sp_table_privileges_rowset;2 |
| sp_table_privileges_rowset;5 | sp_table_privileges_rowset_rmt |
| sp_table_privileges_rowset2 | sp_table_statistics_rowset |
| sp_table_statistics_rowset;2 | sp_table_statistics2_rowset |
| sp_tablecollations | sp_tablecollations_90 |
| sp_tables_info_90_rowset | sp_tables_info_90_rowset_64 |
| sp_tables_info_90_rowset2 | sp_tables_info_90_rowset2_64 |
| sp_tables_info_rowset | sp_tables_info_rowset;2 |
| sp_tables_info_rowset_64 | sp_tables_info_rowset_64;2 |
| sp_tables_info_rowset2 | sp_tables_info_rowset2_64 |
| sp_tables_rowset;2 | sp_tables_rowset;5 |
| sp_tables_rowset_rmt | sp_tables_rowset2 |
| sp_usertypes_rowset | sp_usertypes_rowset_rmt |
| sp_usertypes_rowset2 | sp_views_rowset |
| sp_views_rowset2 | sp_xml_schema_rowset |
| sp_xml_schema_rowset2 |
CREATE PROCEDURE (Transact-SQL)
Stored Procedures (Database Engine)
Running Stored Procedures (OLE DB)
Running Stored Procedures
Database Engine Stored Procedures (Transact-SQL)
Running Stored Procedures