Skip to content

system-stored-procedures-transact-sql.md

Latest commit

 

History

History
750 lines (733 loc) · 23.5 KB

system-stored-procedures-transact-sql.md

File metadata and controls

750 lines (733 loc) · 23.5 KB
description System Stored Procedures (Transact-SQL)
title System Stored Procedures (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 07/25/2022
ms.service sql
ms.reviewer randolphwest
ms.subservice system-objects
ms.topic reference
f1_keywords
sql13.TSQLSysNoExpandPortal.f1
sql13.TSQLSysNoExpandPortal.f1_TSQL
dev_langs
TSQL
helpviewer_keywords
stored procedures [SQL Server]
APIs [SQL Server]
stored procedures [SQL Server], categories
system stored procedures [SQL Server], categories
system stored procedures [SQL Server]
ms.assetid a5c4d5b8-5a24-4a2d-99b4-d003b546ee3a
author VanMSFT
ms.author vanto
monikerRange =azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

System Stored Procedures (Transact-SQL)

[!INCLUDE sqlserver2016-asdb-asdbmi]

In [!INCLUDEssnoversion], 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 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.

Important

Stretch Database is deprecated in [!INCLUDE sssql22-md]. [!INCLUDE ssNoteDepFutureAvoid-md]

API System Stored Procedures

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:

:::row::: :::column::: sp_catalogs :::column-end::: :::column::: sp_column_privileges :::column-end::: :::row-end:::
:::row::: :::column::: sp_column_privileges_ex :::column-end::: :::column::: sp_columns :::column-end::: :::row-end:::
:::row::: :::column::: sp_columns_ex :::column-end::: :::column::: sp_databases :::column-end::: :::row-end:::
:::row::: :::column::: sp_cursor :::column-end::: :::column::: sp_cursorclose :::column-end::: :::row-end:::
:::row::: :::column::: sp_cursorexecute :::column-end::: :::column::: sp_cursorfetch :::column-end::: :::row-end:::
:::row::: :::column::: sp_cursoroption :::column-end::: :::column::: sp_cursoropen :::column-end::: :::row-end:::
:::row::: :::column::: sp_cursorprepare :::column-end::: :::column::: sp_cursorprepexec :::column-end::: :::row-end:::
:::row::: :::column::: sp_cursorunprepare :::column-end::: :::column::: sp_execute :::column-end::: :::row-end:::
:::row::: :::column::: sp_datatype_info :::column-end::: :::column::: sp_fkeys :::column-end::: :::row-end:::
:::row::: :::column::: sp_foreignkeys :::column-end::: :::column::: sp_indexes :::column-end::: :::row-end:::
:::row::: :::column::: sp_pkeys :::column-end::: :::column::: sp_primarykeys :::column-end::: :::row-end:::
:::row::: :::column::: sp_prepare :::column-end::: :::column::: sp_prepexec :::column-end::: :::row-end:::
:::row::: :::column::: sp_prepexecrpc :::column-end::: :::column::: sp_unprepare :::column-end::: :::row-end:::
:::row::: :::column::: sp_server_info :::column-end::: :::column::: sp_special_columns :::column-end::: :::row-end:::
:::row::: :::column::: sp_sproc_columns :::column-end::: :::column::: sp_statistics :::column-end::: :::row-end:::
:::row::: :::column::: sp_table_privileges :::column-end::: :::column::: sp_table_privileges_ex :::column-end::: :::row-end:::
:::row::: :::column::: sp_tables :::column-end::: :::column::: sp_tables_ex :::column-end::: :::row-end:::

 

The following stored procedures are not documented:

:::row::: :::column::: sp_assemblies_rowset :::column-end::: :::column::: sp_assemblies_rowset_rmt :::column-end::: :::row-end:::
:::row::: :::column::: sp_assemblies_rowset2 :::column-end::: :::column::: sp_assembly_dependencies_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_assembly_dependencies_rowset_rmt :::column-end::: :::column::: sp_assembly_dependencies_rowset2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_bcp_dbcmptlevel :::column-end::: :::column::: sp_catalogs_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_catalogs_rowset;2 :::column-end::: :::column::: sp_catalogs_rowset;5 :::column-end::: :::row-end:::
:::row::: :::column::: sp_catalogs_rowset_rmt :::column-end::: :::column::: sp_catalogs_rowset2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_check_constbytable_rowset :::column-end::: :::column::: sp_check_constbytable_rowset;2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_check_constbytable_rowset2 :::column-end::: :::column::: sp_check_constraints_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_check_constraints_rowset;2 :::column-end::: :::column::: sp_check_constraints_rowset2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_column_privileges_rowset :::column-end::: :::column::: sp_column_privileges_rowset;2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_column_privileges_rowset;5 :::column-end::: :::column::: sp_column_privileges_rowset_rmt :::column-end::: :::row-end:::
:::row::: :::column::: sp_column_privileges_rowset2 :::column-end::: :::column::: sp_columns_90 :::column-end::: :::row-end:::
:::row::: :::column::: sp_columns_90_rowset :::column-end::: :::column::: sp_columns_90_rowset_rmt :::column-end::: :::row-end:::
:::row::: :::column::: sp_columns_90_rowset2 :::column-end::: :::column::: sp_columns_ex_90 :::column-end::: :::row-end:::
:::row::: :::column::: sp_columns_rowset :::column-end::: :::column::: sp_columns_rowset;2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_columns_rowset;5 :::column-end::: :::column::: sp_columns_rowset_rmt :::column-end::: :::row-end:::
:::row::: :::column::: sp_columns_rowset2 :::column-end::: :::column::: sp_constr_col_usage_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_datatype_info_90 :::column-end::: :::column::: sp_ddopen;1 :::column-end::: :::row-end:::
:::row::: :::column::: sp_ddopen;10 :::column-end::: :::column::: sp_ddopen;11 :::column-end::: :::row-end:::
:::row::: :::column::: sp_ddopen;12 :::column-end::: :::column::: sp_ddopen;13 :::column-end::: :::row-end:::
:::row::: :::column::: sp_ddopen;2 :::column-end::: :::column::: sp_ddopen;3 :::column-end::: :::row-end:::
:::row::: :::column::: sp_ddopen;4 :::column-end::: :::column::: sp_ddopen;5 :::column-end::: :::row-end:::
:::row::: :::column::: sp_ddopen;6 :::column-end::: :::column::: sp_ddopen;7 :::column-end::: :::row-end:::
:::row::: :::column::: sp_ddopen;8 :::column-end::: :::column::: sp_ddopen;9 :::column-end::: :::row-end:::
:::row::: :::column::: sp_foreign_keys_rowset :::column-end::: :::column::: sp_foreign_keys_rowset;2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_foreign_keys_rowset;3 :::column-end::: :::column::: sp_foreign_keys_rowset;5 :::column-end::: :::row-end:::
:::row::: :::column::: sp_foreign_keys_rowset_rmt :::column-end::: :::column::: sp_foreign_keys_rowset2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_foreign_keys_rowset3 :::column-end::: :::column::: sp_indexes_90_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_indexes_90_rowset_rmt :::column-end::: :::column::: sp_indexes_90_rowset2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_indexes_rowset :::column-end::: :::column::: sp_indexes_rowset;2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_indexes_rowset;5 :::column-end::: :::column::: sp_indexes_rowset_rmt :::column-end::: :::row-end:::
:::row::: :::column::: sp_indexes_rowset2 :::column-end::: :::column::: sp_linkedservers_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_linkedservers_rowset;2 :::column-end::: :::column::: sp_linkedservers_rowset2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_oledb_database :::column-end::: :::column::: sp_oledb_defdb :::column-end::: :::row-end:::
:::row::: :::column::: sp_oledb_deflang :::column-end::: :::column::: sp_oledb_language :::column-end::: :::row-end:::
:::row::: :::column::: sp_oledb_ro_usrname :::column-end::: :::column::: sp_primary_keys_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_primary_keys_rowset;2 :::column-end::: :::column::: sp_primary_keys_rowset;3 :::column-end::: :::row-end:::
:::row::: :::column::: sp_primary_keys_rowset;5 :::column-end::: :::column::: sp_primary_keys_rowset_rmt :::column-end::: :::row-end:::
:::row::: :::column::: sp_primary_keys_rowset2 :::column-end::: :::column::: sp_procedure_params_90_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_procedure_params_90_rowset2 :::column-end::: :::column::: sp_procedure_params_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_procedure_params_rowset;2 :::column-end::: :::column::: sp_procedure_params_rowset2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_procedures_rowset :::column-end::: :::column::: sp_procedures_rowset;2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_procedures_rowset2 :::column-end::: :::column::: sp_provider_types_90_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_provider_types_rowset :::column-end::: :::column::: sp_schemata_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_schemata_rowset;3 :::column-end::: :::column::: sp_special_columns_90 :::column-end::: :::row-end:::
:::row::: :::column::: sp_sproc_columns_90 :::column-end::: :::column::: sp_statistics_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_statistics_rowset;2 :::column-end::: :::column::: sp_statistics_rowset2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_stored_procedures :::column-end::: :::column::: sp_table_constraints_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_table_constraints_rowset;2 :::column-end::: :::column::: sp_table_constraints_rowset2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_table_privileges_rowset :::column-end::: :::column::: sp_table_privileges_rowset;2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_table_privileges_rowset;5 :::column-end::: :::column::: sp_table_privileges_rowset_rmt :::column-end::: :::row-end:::
:::row::: :::column::: sp_table_privileges_rowset2 :::column-end::: :::column::: sp_table_statistics_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_table_statistics_rowset;2 :::column-end::: :::column::: sp_table_statistics2_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_tablecollations :::column-end::: :::column::: sp_tablecollations_90 :::column-end::: :::row-end:::
:::row::: :::column::: sp_tables_info_90_rowset :::column-end::: :::column::: sp_tables_info_90_rowset_64 :::column-end::: :::row-end:::
:::row::: :::column::: sp_tables_info_90_rowset2 :::column-end::: :::column::: sp_tables_info_90_rowset2_64 :::column-end::: :::row-end:::
:::row::: :::column::: sp_tables_info_rowset :::column-end::: :::column::: sp_tables_info_rowset;2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_tables_info_rowset_64 :::column-end::: :::column::: sp_tables_info_rowset_64;2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_tables_info_rowset2 :::column-end::: :::column::: sp_tables_info_rowset2_64 :::column-end::: :::row-end:::
:::row::: :::column::: sp_tables_rowset;2 :::column-end::: :::column::: sp_tables_rowset;5 :::column-end::: :::row-end:::
:::row::: :::column::: sp_tables_rowset_rmt :::column-end::: :::column::: sp_tables_rowset2 :::column-end::: :::row-end:::
:::row::: :::column::: sp_usertypes_rowset :::column-end::: :::column::: sp_usertypes_rowset_rmt :::column-end::: :::row-end:::
:::row::: :::column::: sp_usertypes_rowset2 :::column-end::: :::column::: sp_views_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_views_rowset2 :::column-end::: :::column::: sp_xml_schema_rowset :::column-end::: :::row-end:::
:::row::: :::column::: sp_xml_schema_rowset2 :::column-end::: :::column::: :::column-end::: :::row-end:::

See Also

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