Database Engine stored procedures are the system procedures SQL Server provides for general maintenance of an instance: configuring options, attaching and detaching databases, managing statistics, renaming objects and checking who is connected.
Database Engine stored procedures I use most
- sp_configure: view and change instance settings, as in Database Mail Setup and Troubleshooting.
- sp_executesql: run parameterised dynamic SQL so plans are reused (see Plan Caching and Parameterization).
- sp_spaceused, sp_helpdb and sp_helpfile: quick checks on database and file sizes.
- sp_who: who is connected, though the DMVs give far more detail.
Several in the list are deprecated, including sp_attach_db, sp_dbcmptlevel, sp_lock, sp_renamedb and the bind and unbind procedures. Use CREATE DATABASE … FOR ATTACH, ALTER DATABASE and the DMVs instead.
The full list of Database Engine stored procedures (SQL Server 2014)
| sp_add_data_file_recover_suspect_db | sp_executesql |
| sp_add_log_file_recover_suspect_db | sp_getapplock |
| sp_addextendedproc | sp_getbindtoken |
| sp_addextendedproperty | sp_help |
| sp_addmessage | sp_helpconstraint |
| sp_addtype | sp_helpdb |
| sp_addumpdevice | sp_helpdevice |
| sp_altermessage | sp_helpextendedproc |
| sp_attach_db | sp_helpfile |
| sp_attach_single_file_db | sp_helpfilegroup |
| sp_autostats | sp_helpindex |
| sp_bindefault | sp_helplanguage |
| sp_bindrule | sp_helpserver |
| sp_bindsession | sp_helpsort |
| sp_certify_removable | sp_helpstats |
| sp_clean_db_file_free_space | sp_helptext |
| sp_clean_db_free_space | sp_helptrigger |
| sp_configure | sp_indexoption |
| sp_control_plan_guide | sp_invalidate_textptr |
| sp_create_plan_guide | sp_lock |
| sp_create_plan_guide_from_handle | sp_monitor |
| sp_create_removable | sp_prepare |
| sp_createstats | sp_prepexec |
| sp_cycle_errorlog | sp_prepexecrpc |
| sp_datatype_info | sp_procoption |
| sp_db_increased_partitions | sp_recompile |
| sp_dbcmptlevel | sp_refreshview |
| sp_dbmmonitoraddmonitoring | sp_releaseapplock |
| sp_dbmmonitorchangealert | sp_rename |
| sp_dbmmonitorchangemonitoring | sp_renamedb |
| sp_dbmmonitordropalert | sp_resetstatus |
| sp_dbmmonitordropmonitoring | sp_sequence_get_range |
| sp_dbmmonitorhelpalert | sp_serveroption |
| sp_dbmmonitorhelpmonitoring | sp_setnetname |
| sp_dbmmonitorresults | sp_settriggerorder |
| sp_delete_backuphistory | sp_spaceused |
| sp_depends | sp_tableoption |
| sp_describe_first_result_set | sp_unbindefault |
| sp_describe_undeclared_parameters | sp_unbindrule |
| sp_detach_db | sp_updateextendedproperty |
| sp_dropdevice | sp_updatestats |
| sp_dropextendedproc | sp_unprepare |
| sp_dropextendedproperty | sp_validname |
| sp_dropmessage | sp_who |
| sp_droptype | sys.sp_xtp_control_proc_exec_stats |
| sys.sp_flush_log | |
| sp_execute | sys.sp_xtp_control_query_exec_stats |
| sys.sp_xtp_bind_db_resource_pool | sys.sp_xtp_checkpoint_force_garbage_collection |
| sys.sp_xtp_unbind_db_resource_pool | sys.sp_merge_xtp_checkpoint_files |
For the syntax and details of each one, look them up in Microsoft’s Database Engine stored procedures reference.
From my SQL Server DBA handbook.