system-stored-procedures-transact-sql.md

title	System Stored Procedures (Transact-SQL)
description	System stored procedures (Transact-SQL)
author	VanMSFT
ms.author	vanto
ms.reviewer	randolphwest
ms.date	06/23/2025
ms.service	sql
ms.subservice	system-objects
ms.topic	reference
ms.custom	ignite-2025
f1_keywords	sql13.TSQLSysNoExpandPortal.f1 sql13.TSQLSysNoExpandPortal.f1_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]
dev_langs	TSQL
monikerRange	=azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

System stored procedures (Transact-SQL)

[!INCLUDE sqlserver2016-asdb-asdbmi-asa-fabricse-fabricdw-fabricsqldb]

In [!INCLUDE ssnoversion], 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 (Azure SQL Database)	Used to manage Active Geo-Replication and Auto-Failover Group 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 [!INCLUDE ssDEnoversion].
Database Mail stored procedures	Used to perform e-mail operations from within an instance of [!INCLUDE ssNoVersion].
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	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 and Semantic Search stored procedures	Used to implement and query full-text indexes.
General extended stored procedures	Used to provide an interface from an instance of [!INCLUDE ssNoVersion] 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	Used to configure the management data warehouse.
MSDTC stored procedures	Use for resetting the Microsoft Distributed Transaction Coordinator (MSDTC) log or looking at MSDTC statistics.
OLE Automation stored procedures	Used to enable standard Automation objects for use within a standard [!INCLUDE tsql] 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	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 [!INCLUDE ssNoVersion] Agent to manage scheduled and event-driven activities.
SQL Server Profiler stored procedures	Used by [!INCLUDE ssSqlProfiler] to monitor performance and activity.
Spatial index stored procedures - arguments and properties	Use for spacial indexes.
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.

API system stored procedures

Users that run [!INCLUDE ssSqlProfiler] against ADO, OLE DB, and ODBC applications might notice these applications using system stored procedures that aren't covered in the [!INCLUDE tsql] Reference. These stored procedures are used by the [!INCLUDE ssNoVersion] Native Client OLE DB Provider and the [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion]. They are intended only for the internal use of the provider or the driver. Calling them explicitly from a [!INCLUDE ssNoVersion]-based application isn't 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 [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] or through client APIs and aren't intended for general customer use. They are subject to change and compatibility isn't guaranteed.

The following stored procedures are documented:

:::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 (Transact SQL) :::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 aren't 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:::

Related content

• CREATE PROCEDURE (Transact-SQL)
• Stored Procedures (Database Engine)
• Running stored procedures (OLE DB)
• Running stored procedures
• Database Engine stored procedures (Transact-SQL)