System Stored Procedures (Transact-SQL)
TOC
Collapse the table of content
Expand the table of content

System Stored Procedures (Transact-SQL)

 

Updated: February 21, 2016

Applies To: SQL Server (starting with 2016 CTP3)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

In SQL Server 2016, 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.

CategoryDescription
Active Geo-Replication Stored ProceduresUsed to manage to manage Active Geo-Replication configurations in Azure SQL Database
Catalog Stored ProceduresUsed to implement ODBC data dictionary functions and isolate ODBC applications from changes to underlying system tables.
Change Data Capture Stored ProceduresUsed to enable, disable, or report on change data capture objects.
Cursor Stored ProceduresUsed to implements cursor variable functionality.
Data Collector Stored ProceduresUsed to work with the data collector and the following components: collection sets, collection items, and collection types.
Database Engine Stored ProceduresUsed for general maintenance of the SQL Server Database Engine.
Database Mail Stored Procedures (Transact-SQL)Used to perform e-mail operations from within an instance of SQL Server.
Database Maintenance Plan Stored ProceduresUsed to set up core maintenance tasks that are required to manage database performance.
Distributed Queries Stored ProceduresUsed 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 ProceduresUsed to implement and query full-text indexes.
General Extended Stored ProceduresUsed to provide an interface from an instance of SQL Server to external programs for various maintenance activities.
Log Shipping Stored ProceduresUsed 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 ProceduresUsed to enable standard Automation objects for use within a standard Transact-SQL batch.
Policy-Based Management Stored ProceduresUsed for Policy-Based Management.
PolyBase stored proceduresAdd or remove a computer from a PolyBase scale-out group.
Query Store Stored Procedures (Transact-SQL)Used to tune performance.
Replication Stored ProceduresUsed to manage replication.
Security Stored ProceduresUsed to manage security.
Snapshot Backup Stored ProceduresUsed to delete the FILE_SNAPSHOT backup along with all of its snapshots or to delete an individual backup file snapshot.
Spatial Index Stored ProceduresUsed to analyze and improve the indexing performance of spatial indexes.
SQL Server Agent Stored ProceduresUsed by SQL Server Profiler to monitor performance and activity.
SQL Server Profiler Stored ProceduresUsed by SQL Server Agent to manage scheduled and event-driven activities.
Stretch Database Stored ProceduresUsed to manage stretch databases.
Temporal Tables Stored ProceduresUse for temporal tables
XML Stored ProceduresUsed for XML text management.
System_CAPS_ICON_note.jpg 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 SQL Server Profiler against ADO, OLE DB, and ODBC applications may notice these applications using system stored procedures that are not covered in the Transact-SQL Reference. These stored procedures are used by the Microsoft SQL Server Native Client OLE DB Provider and the SQL Server 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 SQL Server. They are intended only for the internal use of the provider or the driver. Calling them explicitly from a SQL Server-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 SQL Server 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 SQL Server 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 SQL Server Books Online:

sp_catalogssp_column_privileges
sp_column_privileges_exsp_columns
sp_columns_exsp_databases
sp_cursorsp_cursorclose
sp_cursorexecutesp_cursorfetch
sp_cursoroptionsp_cursoropen
sp_cursorpreparesp_cursorprepexec
sp_cursorunpreparesp_execute
sp_datatype_infosp_fkeys
sp_foreignkeyssp_indexes
sp_pkeyssp_primarykeys
sp_preparesp_prepexec
sp_prepexecrpcsp_unprepare
sp_server_infosp_special_columns
sp_sproc_columnssp_statistics
sp_table_privilegessp_table_privileges_ex
sp_tablessp_tables_ex

The following stored procedures are not documented:

sp_assemblies_rowsetsp_assemblies_rowset_rmt
sp_assemblies_rowset2sp_assembly_dependencies_rowset
sp_assembly_dependencies_rowset_rmtsp_assembly_dependencies_rowset2
sp_bcp_dbcmptlevelsp_catalogs_rowset
sp_catalogs_rowset;2sp_catalogs_rowset;5
sp_catalogs_rowset_rmtsp_catalogs_rowset2
sp_check_constbytable_rowsetsp_check_constbytable_rowset;2
sp_check_constbytable_rowset2sp_check_constraints_rowset
sp_check_constraints_rowset;2sp_check_constraints_rowset2
sp_column_privileges_rowsetsp_column_privileges_rowset;2
sp_column_privileges_rowset;5sp_column_privileges_rowset_rmt
sp_column_privileges_rowset2sp_columns_90
sp_columns_90_rowsetsp_columns_90_rowset_rmt
sp_columns_90_rowset2sp_columns_ex_90
sp_columns_rowsetsp_columns_rowset;2
sp_columns_rowset;5sp_columns_rowset_rmt
sp_columns_rowset2sp_constr_col_usage_rowset
sp_datatype_info_90sp_ddopen;1
sp_ddopen;10sp_ddopen;11
sp_ddopen;12sp_ddopen;13
sp_ddopen;2sp_ddopen;3
sp_ddopen;4sp_ddopen;5
sp_ddopen;6sp_ddopen;7
sp_ddopen;8sp_ddopen;9
sp_foreign_keys_rowsetsp_foreign_keys_rowset;2
sp_foreign_keys_rowset;3sp_foreign_keys_rowset;5
sp_foreign_keys_rowset_rmtsp_foreign_keys_rowset2
sp_foreign_keys_rowset3sp_indexes_90_rowset
sp_indexes_90_rowset_rmtsp_indexes_90_rowset2
sp_indexes_rowsetsp_indexes_rowset;2
sp_indexes_rowset;5sp_indexes_rowset_rmt
sp_indexes_rowset2sp_linkedservers_rowset
sp_linkedservers_rowset;2sp_linkedservers_rowset2
sp_oledb_databasesp_oledb_defdb
sp_oledb_deflangsp_oledb_language
sp_oledb_ro_usrnamesp_primary_keys_rowset
sp_primary_keys_rowset;2sp_primary_keys_rowset;3
sp_primary_keys_rowset;5sp_primary_keys_rowset_rmt
sp_primary_keys_rowset2sp_procedure_params_90_rowset
sp_procedure_params_90_rowset2sp_procedure_params_rowset
sp_procedure_params_rowset;2sp_procedure_params_rowset2
sp_procedures_rowsetsp_procedures_rowset;2
sp_procedures_rowset2sp_provider_types_90_rowset
sp_provider_types_rowsetsp_schemata_rowset
sp_schemata_rowset;3sp_special_columns_90
sp_sproc_columns_90sp_statistics_rowset
sp_statistics_rowset;2sp_statistics_rowset2
sp_stored_proceduressp_table_constraints_rowset
sp_table_constraints_rowset;2sp_table_constraints_rowset2
sp_table_privileges_rowsetsp_table_privileges_rowset;2
sp_table_privileges_rowset;5sp_table_privileges_rowset_rmt
sp_table_privileges_rowset2sp_table_statistics_rowset
sp_table_statistics_rowset;2sp_table_statistics2_rowset
sp_tablecollationssp_tablecollations_90
sp_tables_info_90_rowsetsp_tables_info_90_rowset_64
sp_tables_info_90_rowset2sp_tables_info_90_rowset2_64
sp_tables_info_rowsetsp_tables_info_rowset;2
sp_tables_info_rowset_64sp_tables_info_rowset_64;2
sp_tables_info_rowset2sp_tables_info_rowset2_64
sp_tables_rowset;2sp_tables_rowset;5
sp_tables_rowset_rmtsp_tables_rowset2
sp_usertypes_rowsetsp_usertypes_rowset_rmt
sp_usertypes_rowset2sp_views_rowset
sp_views_rowset2sp_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

Community Additions

ADD
Show:
© 2016 Microsoft