System Stored Procedures (Transact-SQL)

  • Article

In SQL Server 2005, 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 Directory Stored Procedures

Used to register instances of SQL Server and SQL Server databases in Microsoft Windows 2000 Active Directory.

Catalog Stored Procedures

Used to implement ODBC data dictionary functions and isolate ODBC applications from changes to underlying system tables.

Cursor Stored Procedures

Used to implements cursor variable functionality.

Database Engine Stored Procedures

Used for general maintenance of the SQL Server Database Engine.

Database Mail and SQL Mail Stored Procedures

Used to perform e-mail operations from within an instance of SQL 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.

Full-Text Search Stored Procedures

Used to implement and query full-text indexes.

Log Shipping Stored Procedures

Used to configure, modify, and monitor log shipping configurations.

Automation Stored Procedures

Enable standard Automation objects to be used within a standard Transact-SQL batch.

Notification Services Stored Procedures

Used to manage SQL Server 2005 Notification Services.

Replication Stored Procedures

Used to manage replication.

Security Stored Procedures

Used to manage security.

SQL Server Profiler Stored Procedures

Used by SQL Server Profiler to monitor performance and activity.

SQL Server Agent Stored Procedures

Used by SQL Server Agent to manage scheduled and event-driven activities.

Web Task Stored Procedures

Used for creating Web pages.

XML Stored Procedures

Used for XML text management.

General Extended Stored Procedures

Provide an interface from an instance of SQL Server to external programs for various maintenance activities.

Note

 Unless specifically documented otherwise, all system stored procedures return a value of 0. This indicates success. To indicate failure, a nonzero value is returned.

API System Stored Procedures

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 Native Client OLE DB Provider and the SQL 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 complete functionality from these stored procedures is made available to SQL Server-based applications through the API functions they support. For example, the cursor functionality of the sp_cursor system stored procedures is made available to OLE DB applications through the OLE DB API cursor properties and methods, and to ODBC applications through the ODBC cursor attributes and functions.

The following system stored procedures support the cursor functionality of ADO, OLE DB, and ODBC:

sp_cursor

sp_cursorclose

sp_cursorexecute

sp_cursorfetch

sp_cursoropen

sp_cursoroption

sp_cursorprepare

sp_cursorunprepare

 

The following system stored procedures support the prepare/execute model of executing Transact-SQL statements in ADO, OLE DB, and ODBC:

sp_execute

sp_prepare

sp_unprepare

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_catalogs

sp_column_privileges

sp_column_privileges_ex

sp_columns

sp_columns_ex

sp_databases

sp_datatype_info

sp_fkeys

sp_foreignkeys

sp_indexes

sp_pkeys

sp_primarykeys

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

See Also

Reference

CREATE PROCEDURE (Transact-SQL)

Other Resources

Stored Procedures (Database Engine)
Running Stored Procedures (OLE DB)
Running Stored Procedures

Help and Information

Getting SQL Server 2005 Assistance