System Stored Procedures

Transact-SQL Reference

Transact-SQL Reference

System Stored Procedures

Many administrative and informational activities in Microsoft® SQL Server™ can be performed through system stored procedures. The system stored procedures are grouped into these categories.

Category Description
Active Directory Procedures Used to register instances of SQL Server and SQL Server databases in Microsoft Windows® 2000 Active Directory™.
Catalog Procedures Implements ODBC data dictionary functions and isolates ODBC applications from changes to underlying system tables.
Cursor Procedures Implements cursor variable functionality.
Database Maintenance Plan Procedures Used to set up core maintenance tasks necessary to ensure database performance.
Distributed Queries Procedures Used to implement and manage Distributed Queries.
Full-Text Search Procedures Used to implement and query full-text indexes.
Log Shipping Procedures Used to configure and manage log shipping.
OLE Automation Procedures Allows standard OLE automation objects to be used within a standard Transact-SQL batch.
Replication Procedures Used to manage replication.
Security Procedures Used to manage security.
SQL Mail Procedures Used to perform e-mail operations from within SQL Server.
SQL Profiler Procedures Used by SQL Profiler to monitor performance and activity.
SQL Server Agent Procedures Used by SQL Server Agent to manage scheduled and event-driven activities.
System Procedures Used for general maintenance of SQL Server.
Web Assistant Procedures Used by the Web Assistant.
XML Procedures Used for Extensible Markup Language (XML) text management.
General Extended Procedures Provides an interface from SQL Server to external programs for various maintenance activities.

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

Active Directory Procedures
sp_ActiveDirectory_Obj sp_ActiveDirectory_SCP

Catalog Procedures
sp_column_privileges sp_special_columns
sp_columns sp_sproc_columns
sp_databases sp_statistics
sp_fkeys sp_stored_procedures
sp_pkeys sp_table_privileges
sp_server_info sp_tables

Cursor Procedures
sp_cursor_list sp_describe_cursor_columns
sp_describe_cursor sp_describe_cursor_tables

Database Maintenance Plan Procedures
sp_add_maintenance_plan sp_delete_maintenance_plan_db
sp_add_maintenance_plan_db sp_delete_maintenance_plan_job
sp_add_maintenance_plan_job sp_help_maintenance_plan
sp_delete_maintenance_plan  

Distributed Queries Procedures
sp_addlinkedserver sp_indexes
sp_addlinkedsrvlogin sp_linkedservers
sp_catalogs sp_primarykeys
sp_column_privileges_ex sp_serveroption
sp_columns_ex sp_table_privileges_ex
sp_droplinkedsrvlogin sp_tables_ex
sp_foreignkeys  

Full-Text Search Procedures
sp_fulltext_catalog sp_help_fulltext_catalogs_cursor
sp_fulltext_column sp_help_fulltext_columns
sp_fulltext_database sp_help_fulltext_columns_cursor
sp_fulltext_service sp_help_fulltext_tables
sp_fulltext_table sp_help_fulltext_tables_cursor
sp_help_fulltext_catalogs  

Log Shipping Procedures
sp_add_log_shipping_database sp_delete_log_shipping_database
sp_add_log_shipping_plan sp_delete_log_shipping_plan
sp_add_log_shipping_plan_database sp_delete_log_shipping_plan_database
sp_add_log_shipping_primary sp_delete_log_shipping_primary
sp_add_log_shipping_secondary sp_delete_log_shipping_secondary
sp_can_tlog_be_applied sp_get_log_shipping_monitor_info
sp_change_monitor_role sp_remove_log_shipping_monitor
sp_change_primary_role sp_resolve_logins
sp_change_secondary_role sp_update_log_shipping_monitor_info
sp_create_log_shipping_monitor_account sp_update_log_shipping_plan
sp_define_log_shipping_monitor sp_update_log_shipping_plan_database

OLE Automation Extended Stored Procedures
sp_OACreate sp_OAMethod
sp_OADestroy sp_OASetProperty
sp_OAGetErrorInfo sp_OAStop
sp_OAGetProperty Object Hierarchy Syntax

Replication Procedures
sp_add_agent_parameter sp_enableagentoffload
sp_add_agent_profile sp_enumcustomresolvers
sp_addarticle sp_enumdsn
sp_adddistpublisher sp_enumfullsubscribers
sp_adddistributiondb sp_expired_subscription_cleanup
sp_adddistributor sp_generatefilters
sp_addmergealternatepublisher sp_getagentoffloadinfo
sp_addmergearticle sp_getmergedeletetype
sp_addmergefilter sp_get_distributor
sp_addmergepublication sp_getqueuedrows
sp_addmergepullsubscription sp_getsubscriptiondtspackagename
sp_addmergepullsubscription_agent sp_grant_publication_access
sp_addmergesubscription sp_help_agent_default
sp_addpublication sp_help_agent_parameter
sp_addpublication_snapshot sp_help_agent_profile
sp_addpublisher70 sp_helparticle
sp_addpullsubscription sp_helparticlecolumns
sp_addpullsubscription_agent sp_helparticledts
sp_addscriptexec sp_helpdistpublisher
sp_addsubscriber sp_helpdistributiondb
sp_addsubscriber_schedule sp_helpdistributor
sp_addsubscription sp_helpmergealternatepublisher
sp_addsynctriggers sp_helpmergearticle
sp_addtabletocontents sp_helpmergearticlecolumn
sp_adjustpublisheridentityrange sp_helpmergearticleconflicts
sp_article_validation sp_helpmergeconflictrows
sp_articlecolumn sp_helpmergedeleteconflictrows
sp_articlefilter sp_helpmergefilter
sp_articlesynctranprocs sp_helpmergepublication
sp_articleview sp_helpmergepullsubscription
sp_attachsubscription sp_helpmergesubscription
sp_browsesnapshotfolder sp_helppublication
sp_browsemergesnapshotfolder sp_help_publication_access
sp_browsereplcmds sp_helppullsubscription
sp_change_agent_parameter sp_helpreplfailovermode
sp_change_agent_profile sp_helpreplicationdboption
sp_changearticle sp_helpreplicationoption
sp_changedistpublisher sp_helpsubscriberinfo
sp_changedistributiondb sp_helpsubscription
sp_changedistributor_password sp_ivindexhasnullcols
sp_changedistributor_property sp_helpsubscription_properties
sp_changemergearticle sp_link_publication
sp_changemergefilter sp_marksubscriptionvalidation
sp_changemergepublication sp_mergearticlecolumn
sp_changemergepullsubscription sp_mergecleanupmetadata
sp_changemergesubscription sp_mergedummyupdate
sp_changepublication sp_mergesubscription_cleanup
sp_changesubscriber sp_publication_validation
sp_changesubscriber_schedule sp_refreshsubscriptions
sp_changesubscriptiondtsinfo sp_reinitmergepullsubscription
sp_changesubstatus sp_reinitmergesubscription
sp_change_subscription_properties sp_reinitpullsubscription
sp_check_for_sync_trigger sp_reinitsubscription
sp_copymergesnapshot sp_removedbreplication
sp_copysnapshot sp_repladdcolumn
sp_copysubscription sp_replcmds
sp_deletemergeconflictrow sp_replcounters
sp_disableagentoffload sp_repldone
sp_drop_agent_parameter sp_repldropcolumn
sp_drop_agent_profile sp_replflush
sp_droparticle sp_replicationdboption
sp_dropanonymouseagent sp_replication_agent_checkup
sp_dropdistpublisher sp_replqueuemonitor
sp_dropdistributiondb sp_replsetoriginator
sp_dropmergealternatepublisher sp_replshowcmds
sp_dropdistributor sp_repltrans
sp_dropmergearticle sp_restoredbreplication
sp_dropmergefilter sp_revoke_publication_access
  sp_scriptsubconflicttable
sp_dropmergepublication sp_script_synctran_commands
sp_dropmergepullsubscription sp_setreplfailovermode
  sp_showrowreplicainfo
sp_dropmergesubscription sp_subscription_cleanup
sp_droppublication sp_table_validation
sp_droppullsubscription sp_update_agent_profile
sp_dropsubscriber sp_validatemergepublication
sp_dropsubscription sp_validatemergesubscription
sp_dsninfo sp_vupgrade_replication
sp_dumpparamcmd  

Security Procedures
sp_addalias sp_droprolemember
sp_addapprole sp_dropserver
sp_addgroup sp_dropsrvrolemember
sp_addlinkedsrvlogin sp_dropuser
sp_addlogin sp_grantdbaccess
sp_addremotelogin sp_grantlogin
sp_addrole sp_helpdbfixedrole
sp_addrolemember sp_helpgroup
sp_addserver sp_helplinkedsrvlogin
sp_addsrvrolemember sp_helplogins
sp_adduser sp_helpntgroup
sp_approlepassword sp_helpremotelogin
sp_changedbowner sp_helprole
sp_changegroup sp_helprolemember
sp_changeobjectowner sp_helprotect
sp_change_users_login sp_helpsrvrole
sp_dbfixedrolepermission sp_helpsrvrolemember
sp_defaultdb sp_helpuser
sp_defaultlanguage sp_MShasdbaccess
sp_denylogin sp_password
sp_dropalias sp_remoteoption
sp_dropapprole sp_revokedbaccess
sp_dropgroup sp_revokelogin
sp_droplinkedsrvlogin sp_setapprole
sp_droplogin sp_srvrolepermission
sp_dropremotelogin sp_validatelogins
sp_droprole  

SQL Mail Procedures
sp_processmail xp_sendmail
xp_deletemail xp_startmail
xp_findnextmsg xp_stopmail
xp_readmail  

SQL Profiler Procedures
sp_trace_create sp_trace_setfilter
sp_trace_generateevent sp_trace_setstatus
sp_trace_setevent  

SQL Server Agent Procedures
sp_add_alert sp_help_jobhistory
sp_add_category sp_help_jobschedule
sp_add_job sp_help_jobserver
sp_add_jobschedule sp_help_jobstep
sp_add_jobserver sp_help_notification
sp_add_jobstep sp_help_operator
sp_add_notification sp_help_targetserver
sp_add_operator sp_help_targetservergroup
sp_add_targetservergroup sp_helptask
sp_add_targetsvrgrp_member sp_manage_jobs_by_login
sp_addtask sp_msx_defect
sp_apply_job_to_targets sp_msx_enlist
sp_delete_alert sp_post_msx_operation
sp_delete_category sp_purgehistory
sp_delete_job sp_purge_jobhistory
sp_delete_jobschedule sp_reassigntask
sp_delete_jobserver sp_remove_job_from_targets
sp_delete_jobstep sp_resync_targetserver
sp_delete_notification sp_start_job
sp_delete_operator sp_stop_job
sp_delete_targetserver sp_update_alert
sp_delete_targetservergroup sp_update_category
sp_delete_targetsvrgrp_member sp_update_job
sp_droptask sp_update_jobschedule
sp_help_alert sp_update_jobstep
sp_help_category sp_update_notification
sp_help_downloadlist sp_update_operator
sp_helphistory sp_update_targetservergroup
sp_help_job sp_updatetask
  xp_sqlagent_proxy_account

System Procedures
sp_add_data_file_recover_suspect_db sp_helpconstraint
sp_addextendedproc sp_helpdb
sp_addextendedproperty sp_helpdevice
sp_add_log_file_recover_suspect_db sp_helpextendedproc
sp_addmessage sp_helpfile
sp_addtype sp_helpfilegroup
sp_addumpdevice sp_helpindex
sp_altermessage sp_helplanguage
sp_autostats sp_helpserver
sp_attach_db sp_helpsort
sp_attach_single_file_db sp_helpstats
sp_bindefault sp_helptext
sp_bindrule sp_helptrigger
sp_bindsession sp_indexoption
sp_certify_removable sp_invalidate_textptr
sp_configure sp_lock
sp_create_removable sp_monitor
sp_createstats sp_procoption
sp_cycle_errorlog sp_recompile
sp_datatype_info sp_refreshview
sp_dbcmptlevel sp_releaseapplock
sp_dboption sp_rename
sp_dbremove sp_renamedb
sp_delete_backuphistory sp_resetstatus
sp_depends sp_serveroption
sp_detach_db sp_setnetname
sp_dropdevice sp_settriggerorder
sp_dropextendedproc sp_spaceused
sp_dropextendedproperty sp_tableoption
sp_dropmessage sp_unbindefault
sp_droptype sp_unbindrule
sp_executesql sp_updateextendedproperty
sp_getapplock sp_updatestats
sp_getbindtoken sp_validname
sp_help sp_who

Web Assistant Procedures
sp_dropwebtask sp_makewebtask
sp_enumcodepages sp_runwebtask

XML Procedures
sp_xml_preparedocument sp_xml_removedocument

General Extended Procedures
xp_cmdshell xp_logininfo
xp_enumgroups xp_msver
xp_findnextmsg xp_revokelogin
xp_grantlogin xp_sprintf
xp_logevent xp_sqlmaint
xp_loginconfig xp_sscanf

API System Stored Procedures

Users running SQL Profiler against ADO, OLE DB, ODBC, and DB-Library applications may notice the use of system stored procedures that are not covered in the Transact-SQL Reference. These stored procedures are used by the Microsoft OLE DB Provider for SQL Server, the SQL Server ODBC driver, and the DB-Library dynamic-link library (DLL) to implement the functionality of a database API. These stored procedures are simply the mechanism the provider or drivers use to communicate user requests to SQL Server. They are intended only for the internal use of the OLE DB Provider for SQL Server, the SQL Server ODBC driver, and the DB-Library DLL. Calling them explicitly from an SQL Server application is not supported.

The complete functionality from these stored procedures is made available to SQL Server 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, to ODBC applications through the ODBC cursor attributes and functions, and to DB-Library applications through the DB-Library Cursor Library.

These system stored procedures support the cursor functionality of ADO, OLE DB, ODBC, and the DB-Library Cursor Library:

sp_cursor sp_cursorclose sp_cursorexecute
sp_cursorfetch sp_cursoropen sp_cursoroption
sp_cursorprepare sp_cursorunprepare  

These 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.

The sp_sdidebug stored procedure is used by SQL Server for debugging Transact-SQL statements.