Beginning Transact-SQL With SQL Server 2000 and 2005
Overview
SQL Server 2000 and 2005 support the following system and extended stored procedures. Procedures introduced with SQL Server 2005 are so indicated.
Active Directory
| Procedure Name | Return Type | Description |
|---|---|---|
| sp_ActiveDirectory_Obj | Int | Adds, updates, or removes the registration record of a SQL Server database in the Active Directory. |
| sp_ActiveDirectory_SCP | Int | Adds, updates, or removes the registration record of a SQL Server instance in the Active Directory. |
Catalog
| Procedure Name | Return Type | Description |
|---|---|---|
| sp_column_privileges | Table | Returns column privileges for a table in the current session. |
| sp_column_privileges_ex | Table | Returns column privileges for a table on a linked or remote server. |
| sp_columns | Table | Returns column information for a table or view. |
| sp_columns_ex | Table | Returns column information for a table or view on a linked or remote server. |
| sp_databases | Table | Returns information about databases on the local server. |
| sp_fkeys | Table | Returns foreign key information for a table. |
| sp_pkeys | Table | Returns primary key information for a table. |
| sp_server_info | Table | Returns server attributes for the server on a specified connection. |
| sp_special_columns | Table | Returns columns used to uniquely identify a row (that is, primary key and unique constraints) and columns with programmatically updated values and defaults. |
| sp_sproc_columns | Table | Returns column information for a stored procedure or user-defined function. |
| sp_statistics | Table | Returns information about indexes and statistics for a table. |
| sp_stored_procedures | Table | Returns information about all stored procedures matching a name or wildcard pattern. |
| sp_table_privileges | Table | Returns information about permissions for a table or tables matching a wildcard pattern in the current session. |
| sp_table_privileges_ex | Table | Returns information about permissions for a table or tables matching a wildcard pattern on a linked or remote server. |
| sp_tables | Table | Returns information about all tables matching a name or wildcard pattern. |
Cursor Management
| Procedure Name | Return Type | Description |
|---|---|---|
| sp_cursor_list | Cursor | Returns attributes and information about currently open cursor(s). |
| sp_describe_cursor | Cursor | Returns attributes and information about a specific cursor. |
| sp_describe_cursor_columns | Cursor | Returns information about columns used to populate a cursor. |
| sp_describe_cursor_tables | Cursor | Returns information about tables used to populate a cursor. |
Database Engine
| Procedure Name | Return Type | Description |
|---|---|---|
| sp_add_data_file_recover_suspect_db | Int | Adds a database file to a file group after a disk full error. Similar to ALTER DATABASE ADD FILE. |
| sp_add_log_file_recover_suspect_db | Int | Adds a transaction log file to a file group after a disk full error. Similar to ALTER DATABASE ADD LOG FILE. |
| sp_addextendedproc | Int | Adds and registers an extended stored procedure to the server meta data. |
| sp_addextendedproperty | Int | Adds an extended property to the server metadata. |
| sp_addmessage | Int | Adds a custom error message to the server messages meta data. |
| sp_addtype | Int | Adds a user-defined data type to a database. |
| sp_addumpdevice | Int | Adds a backup device (file, tape drive, or other device) to the server. |
| sp_altermessage | Int | Modifies an existing error message (number, severity, category, or message text). |
| sp_attach_db | Int | Attaches a database file to a SQL Server instance and makes it available as an active database. |
| sp_attach_single_file_db | Int | Similar to sp_attach_db but only for single-file databases. Builds new transactionlog file. |
| sp_autostats | Int | Returns or modifies the UPDATE STATISTICS setting for a table's index or statistics in the current database. |
| sp_bindefault | Int | Associates a defined default with a table's column as a shared default. |
| sp_bindrule | Int | Associates a defined rule with a table's column as a default check constraint. |
| sp_bindsession | Int | Allows multiple connections to participate in a single transaction by associating them to an established session. |
| sp_certify_removable | Int | Verifies (certifies) that a database may be actively used on removable media. |
| sp_configure | Int | Returns or modifies server configuration settings. |
| sp_create_removable | Int | Creates a set of files and a new database to be used on removable media. |
| sp_createstats | Int | Generates statistics for all tables and candidate columns in the current database. |
| sp_cycle_errorlog | (none) | Closes the current error log and initiates a new error log with a default name and settings (as if for a server restart). |
| sp_datatype_info | Table | Returns detailed information about all current data types or information for a specific data type (system and user-defined data types). |
| sp_dbcmptlevel | Int | Sets the SQL Server version database compatibility level. Setting the level to an older version number disables certain product features to emulate the capabilities of an older SQL Server version. |
| sp_dboption | Int & Table | Sets user database options similar to those set using ALTER DATABASE. |
| sp_dbremove | Int | Removes a database and associated files. |
| sp_delete_backuphistory | Int | Removes backup history information for a database. |
| sp_depends | Int & Table | Lists the dependent objects for a database object. |
| sp_detach_db | Int | Detaches a database's file(s) from the server. |
| sp_dropdevice | Int | Removes a database or backup device record from the server. |
| sp_dropextendedproc | Int | Drops an extended stored procedure from the server. |
| sp_dropextendedproperty | Int | Drops an extended property from the server. |
| sp_dropmessage | Int | Removes an error message record from the server. |
| sp_droptype | Int | Removes a user-defined data type from the server. |
| sp_executesql | Int | Executes a parameterized Transact-SQL statement. |
| sp_getapplock | Int | Places a lock on an application or system resource outside of SQL Server for the duration of a transaction or session. |
| sp_getbindtoken | (none) | Uses a VarChar type output parameter to return a unique ID for a transaction. |
| sp_help | Int & Table | Returns descriptive help information specific to a database object. |
| sp_helpconstraint | Int & Table | Returns help information for a specified constraint. |
| sp_helpdb | Int & Table | Returns help information for a specified database. |
| sp_helpdevice | Int & Table | Returns help information for a specified device. |
| sp_helpextendedproc | Int & Table | Returns help information for a specified extended stored procedure. |
| sp_helpfile | Int & Table | Returns help information for a specified database file. |
| sp_helpfilegroup | Int & Table | Returns help information for a specified database file group. |
| sp_helpindex | Int & Table | Returns help information for a specified index. |
| sp_helplanguage | Int & Table | Returns help information for a specified server language. |
| sp_helpserver | Int & Table | Returns help information for a specified server (local or remote). |
| sp_helpsort | Int & Table | Returns a description of the server's collation and sort order. |
| sp_helpstats | Int & Table | Returns help information regarding the statistics associated with the indexes for a specified table. |
| sp_helptext | Int & Table | Returns the definition of a rule, default, stored procedure, user-defined function, trigger, or view. |
| sp_helptrigger | Int & Table | Returns information about the triggers associated with a specified table. |
| sp_indexoption | Int | Allows default level-locking options (that is, row, page, table) to be overridden for a specified index. |
| sp_invalidate_textptr | Int | Invalidates a specified in-row text pointer, or all in-row text pointers in a transaction. |
| sp_lock | Int & Table | Returns information about all active locks. |
| sp_monitor | Int & Table | Returns the results from several system functions to show the status of server and system resources. |
| sp_procoption | (none - uses error codes) | Enables one of several procedure options to be set. |
| sp_recompile | Int | Recompiles a stored procedure or trigger. |
| sp_refreshview | Int | Updates the metadata for a specified view. |
| sp_releaseapplock | Int | Removes a lock set on an application or external system resource that may have been set using sp_getapplock. |
| sp_rename | Int | Renames a database object. |
| sp_renamedb | Int | Renames a database. |
| sp_resetstatus | Int | Resets the suspect status of a database back to normal status. |
| sp_serveroption | Int | Sets specified server options for a remote and linked server. |
| sp_setnetname | Int | Sets the network name for a linked or remote server. |
| sp_settriggerorder | Int | Sets a specified trigger for a table to execute first or last. |
| sp_spaceused | Int & Table | Returns information about the disk space used by rows, table, and a database. |
| sp_tableoption | Int | Sets one of several table options. |
| sp_unbindefault | Int | Removes a specified default from a column or user-defined data type. |
| sp_unbindrule | Int | Removes a specified rule from a column or user-defined data type. |
| sp_updateextendedproperty | Int | Updates the value of a specified extended property. |
| sp_updatestats | Int | Updates all index statistics in the database. |
| sp_validname | Int | Checks a specified character string for validity as an object name. If invalid, raises an option error. |
| sp_who | Int | Returns information about current connections and user sessions on a server. |
Database Maintenance
| Procedure Name | Return Type | Description |
|---|---|---|
| sp_add_maintenance_plan | Int | Adds a maintenance plan to the server and returns the plan ID. |
| sp_add_maintenance_plan_db | Int | Associates a database with a maintenance plan. (A maintenance plan is added using sp_add_maintenance_plan.) |
| sp_add_maintenance_plan_job | Int | Associates a maintenance plan with an existing job. |
| sp_delete_maintenance_plan | Int | Deletes a maintenance plan based in the specified plan ID. |
| sp_delete_maintenance_plan_db | Int | Removes an associated maintenance plan from the specified database. |
| sp_delete_maintenance_plan_job | Int | Removes an associated maintenance plan from the specified job. |
| sp_help_maintenance_plan | Int & Table | Returns information about maintenance plans on the server. |
Distributed Queries
| Procedure Name | Return Types | Description |
|---|---|---|
| sp_addlinkedserver | Int | Adds a linked server to the current server, allowing persistent access to a remote SQL Server from the current server. |
| sp_addlinkedsrvlogin | Int | Adds the association of a local login to a linked server login for user connectivity to a linked server. |
| sp_catalogs | Table | Returns the list of databases on a linked server. |
| sp_column_privileges_ex | Table | Returns column-level security access privilege information for a specified table on a linked server. |
| sp_columns_ex | Table | Returns column information for a table or view on a linked server. |
| sp_droplinkedsrvlogin | Int | Removes the association of a local login to the login on a linked server. |
| sp_foreignkeys | Table | Returns information about foreign key columns related to a specified primary key for tables on a linked server. |
| sp_indexes | Table | Returns index information for a remote or linked server table. |
| sp_linkedservers | Table | Returns information about all linked servers. |
| sp_primarykeys | Table | Returns information about primary key columns for a specified remote or linked server table. |
| sp_serveroption | Int | Sets server options for remote servers and linked servers. |
| sp_table_privileges_ex | Table | Returns information about column and table-level security privileges for a specified remote or linked table. |
| sp_tables_ex | Table | Returns information about tables on a remote or linked server. |
External Systems and Extended Procedures
| Procedure Name | Return Types | Description |
|---|---|---|
| xp_cmdshell | Int | Executes an operating system shell command, as if entered at the command prompt on the server. |
| xp_enumgroups | Int & Table | Returns information about Windows domain groups. |
| xp_findnextmsg | Int | Uses an output parameter to return a MAPI message ID from the SQL Server Inbox. |
| xp_grantlogin | Int | Calls sp_grantlogin for backward compatibility. Creates a SQL Server login for an associated Windows user or group. |
| xp_logevent | Int | Logs a message to the SQL Server log file without raising a SQL Server error. |
| xp_loginconfig | Int & Table | Returns SQL Server security configuration information. |
| xp_logininfo | Int & Table | Returns detailed information about a SQL Server login and related privileges. |
| xp_msver | Table | Returns detailed information about the instance of SQL Server and the operating system environment. |
| xp_revokelogin | Int | Calls sp_revokelogin for backward compatibility. Revokes permissions of a SQL Server login. |
| xp_sprintf | Int | Uses an output parameter to return a character string. Used to assemble a character string from parameterized values. |
| xp_sqlmaint | (none) | Calls the SQLMAIN command-line tool to set SQL Server maintenance options. |
| xp_sscanf | Int | Uses an output parameter to return a character string. Used to dissassemble a character string into corresponding parameterized values. This is the converse of the xp_sprintf procedure. |
Full-Text Index/Search
| Procedure Name | Return Types | Description |
|---|---|---|
| sp_fulltext_catalog | Int | Creates or maintains a full-text catalog to be used to store and maintain full-text indexes. |
| sp_fulltext_column | Int | Indicates whether a specified column should be included in a full-text index. |
| sp_fulltext_database | Int | Enables or disables full-text indexing for a database. |
| sp_fulltext_service | Int | Used to manage full-text indexing services on a server. |
| sp_fulltext_table | Int | Manages and enables actions for full-text indexing on a specific table. |
| sp_help_fulltext_catalogs | Int & Table | Returns information about the tables and attributes for a full-text catalog. |
| sp_help_fulltext_catalogs_cursor | Int & Table | Returns information about the tables and attributes for a full-text catalog. |
| sp_help_fulltext_columns | Int & Table | Returns information about the columns contained in a full-text index for a table. |
| sp_help_fulltext_columns_cursor | Int & Cursor | Returns information about the columns contained in a full-text index for a table. |
| sp_help_fulltext_tables | Int & Table | Returns information about the tables contained in a full-text catalog. |
| sp_help_fulltext_tables_cursor | Int & Cursor | Returns information about the tables contained in a full-text catalog. |
Log Shipping
Procedures added in SQL Server 2005.
| Procedure Name | Return Type |
|---|---|
| sp_add_log_shipping_alert_job | Int |
| sp_add_log_shipping_primary_database | Int |
| sp_add_log_shipping_primary_secondary | Int |
| sp_add_log_shipping_secondary_database | Int |
| sp_add_log_shipping_secondary_primary | Int |
| sp_change_log_shipping_primary_database | Int |
| sp_change_log_shipping_secondary_database | Int |
| sp_change_log_shipping_secondary_primary | Int |
| sp_cleanup_log_shipping_history | Int |
| sp_delete_log_shipping_alert_job | Int |
| sp_delete_log_shipping_primary_database | Int |
| sp_delete_log_shipping_primary_secondary | Int |
| sp_delete_log_shipping_secondary_database | Int |
| sp_delete_log_shipping_secondary_primary | Int |
| sp_help_log_shipping_alert_job | Table |
| sp_help_log_shipping_monitor_primary | Table |
| sp_help_log_shipping_monitor_secondary | Table |
| sp_help_log_shipping_primary_database | Table |
| sp_help_log_shipping_primary_secondary | Table |
| sp_help_log_shipping_secondary_database | Table |
| sp_help_log_shipping_secondary_primary | Table |
| sp_refresh_log_shipping_monitor | Int |
| sp_resolve_logins | Int |
The xp_readmail and xp_sendmail procedures are valid in SQL Server 2000. All others procedures are new in SQL Server 2005.
| Procedure Name | Return Type |
|---|---|
| sp_send_dbmail | Int |
| sysmail_add_account_sp | Int |
| sysmail_add_principalprofile_sp | Int |
| sysmail_add_profile_sp | Int |
| sysmail_add_profileaccount_sp | Int |
| sysmail_configure_sp | Int |
| sysmail_delete_account_sp | Int |
| sysmail_delete_principalprofile_sp | Int |
| sysmail_delete_profile_sp | Int |
| sysmail_delete_profileaccount_sp | Int |
| sysmail_help_account_sp | Table |
| sysmail_help_configure_sp | Table |
| sysmail_help_principalprofile_sp | Table |
| sysmail_help_profile_sp | Table |
| sysmail_help_profileaccount_sp | Table |
| sysmail_start_sp | Int |
| sysmail_stop_sp | Int |
| sysmail_update_account_sp | Int |
| sysmail_update_principalprofile_sp | Int |
| sysmail_update_profile_sp | Int |
| sysmail_update_profileaccount_sp | Int |
| xp_readmail | Int & Table |
| Xp_sendmail | Int |
Notification Services
Procedures added in SQL Server 2005.
| Procedure Name | Return Type |
|---|---|
| NSAdministrationHistory | Table |
| NSDiagnosticDeliveryChannel | Table |
| NSDiagnosticEventClass | Table |
| NSDiagnosticEventProvider | Table |
| NSDiagnosticFailedNotifications | Table |
| NSDiagnosticNotificationClass | Table |
| NSDiagnosticSubscriptionClass | Table |
| NSEventBatchDetails | Table |
| NSEventBeginBatch | Table |
| NSEventFlushBatch | Table |
| NSEventSubmitBatch | Table |
| NSEventWrite | Int |
| NSExecuteRuleFiring | Table |
| NSNotificationBatchDetails | Table |
| NSPrepareRuleFiring | Table |
| NSQuantumDetails | Table |
| NSQuantumExecutionTime | Table |
| NSQuantumFailures | Table |
| NSQuantumList | Table |
| NSQuantumPerformance | Table |
| NSQuantumsSkipped | Table |
| NSScheduledSubscriptionDetails | Table |
| NSScheduledSubscriptionList | Table |
| NSSetQuantumClock | Int |
| NSSetQuantumClockDate | Int |
| NSSnapshotApplications | Int |
| NSSnapshotDeliveryChannels | Table |
| NSSnapshotEvents | Table |
| NSSnapshotProviders | Table |
| NSSnapshotSubscriptions | Table |
| NSVacuum | Table |
OLE Automation
Using these OLE automation stored procedures, it's possible to execute certain application code from SQL queries to perform actions and automate applications outside of SQL Server. Using custom-created COM components; practically any programmatic interaction is possible.
| Procedure Name | Return Type | Description |
|---|---|---|
| sp_OACreate | Int | Instantiates an OLE object from a specified class using either the ProgID or CLSID. If stopped, starts the OLE automation execution process on the server. |
| sp_OADestroy | Int | Destroys a previously instantiated OLE object. |
| sp_OAGetErrorInfo | Int & Table | Returns the error information associated with an OLE object instance and actions. |
| sp_OAGetProperty | Int & Table | Uses either an output parameter or a result set to return the value(s) or structured information for a specified object property. |
| sp_OAMethod | Int & Table | Calls a method of an OLE object. Uses either an output parameter or a result set to return the value(s) or structured information returned by the method call. |
| sp_OASetProperty | Int | Sets an object property to a specified value. |
| sp_OAStop | Int | Stops the OLE automation execution process environment on the server. Immediately terminates all OLE automation activity for all sessions. |
Profiler
| Procedure Name | Return Type | Description |
|---|---|---|
| sp_trace_create | Int | Creates a new Profiler trace. |
| sp_trace_generateevent | Int | Creates a new Profiler event. |
| sp_trace_setevent | Int | Adds an existing event to a trace. These items may be created using the sp_trace_create and sp_trace_generateevent procedures. |
| sp_trace_setfilter | Int | Adds a filter to an existing trace. |
| sp_trace_setstatus | Int | Modifies an existing trace. |
Replication
| Procedure Name | Return Type |
|---|---|
| sp_add_agent_parameter | Int |
| sp_add_agent_profile | Int |
| sp_addarticle | Int |
| sp_adddistpublisher | Int |
| sp_adddistributiondb | Int |
| sp_adddistributor | Int |
| sp_addmergealternatepublisher | Int |
| sp_addmergearticle | Int |
| sp_addmergefilter | Int |
| sp_addmergepublication | Int |
| sp_addmergepullsubscription | Int |
| sp_addmergepullsubscription_agent | Int |
| sp_addmergesubscription | Int |
| sp_addpublication | Int |
| sp_addpublication_snapshot | Int |
| sp_addpublisher70 | Int |
| sp_addpullsubscription | Int |
| sp_addpullsubscription_agent | Int |
| sp_addscriptexec | Int |
| sp_addsubscriber | Int |
| sp_addsubscriber_schedule | Int |
| sp_addsubscription | Int |
| sp_addsynctriggers | Int |
| sp_addtabletocontents | Int |
| sp_adjustpublisheridentityrange | Int |
| sp_article_validation | Int |
| sp_articlecolumn | Int |
| sp_articlefilter | Int |
| sp_articlesynctranprocs | Int |
| sp_articleview | Int |
| sp_attachsubscription | Int |
| sp_browsemergesnapshotfolder | Table |
| sp_browsereplcmds | Table |
| sp_browsesnapshotfolder | Table |
| sp_change_agent_parameter | Int |
| sp_change_agent_profile | Int |
| sp_change_subscription_properties | Int |
| sp_changearticle | Int |
| sp_changedistpublisher | Int |
| sp_changedistributiondb | Int |
| sp_changedistributor_password | Int |
| sp_changedistributor_property | Int |
| sp_changemergearticle | Int |
| sp_changemergefilter | Int |
| sp_changemergepublication | Int |
| sp_changemergepullsubscription | Int |
| sp_changemergesubscription | Int |
| sp_changepublication | Int |
| sp_changesubscriber | Int |
| sp_changesubscriber_schedule | Int |
| sp_changesubscriptiondtsinfo | Int |
| sp_changesubscriptiondtsinfo | Int |
| sp_changesubstatus | Int |
| sp_check_for_sync_trigger | Int |
| sp_copymergesnapshot | Int |
| sp_copysnapshot | Int |
| sp_copysubscription | Int |
| sp_deletemergeconflictrow | Int |
| sp_disableagentoffload | Int |
| sp_drop_agent_parameter | Int |
| sp_drop_agent_profile | Int |
| sp_dropanonymouseagent | Int |
| sp_droparticle | Int |
| sp_dropdistpublisher | Int |
| sp_dropdistributiondb | Int |
| sp_dropdistributor | Int |
| sp_dropmergealternatepublisher | Int |
| sp_dropmergearticle | Int |
| sp_dropmergefilter | Int |
| sp_dropmergepublication | Int |
| sp_dropmergepullsubscription | Int |
| sp_dropmergesubscription | Int |
| sp_droppublication | Int |
| sp_droppullsubscription | Int |
| sp_dropsubscriber | Int |
| sp_dropsubscription | Int |
| sp_dsninfo | Int & Table |
| sp_dumpparamcmd | Int |
| sp_enableagentoffload | Int |
| sp_enumcustomresolvers | Int & Table |
| sp_enumdsn | Int & Table |
| sp_enumfullsubscribers | Int & Table |
| sp_expired_subscription_cleanup | Int |
| sp_generatefilters | Int |
| sp_get_distributor | Int |
| sp_getagentoffloadinfo | Int & Table |
| sp_getmergedeletetype | Int |
| sp_getqueuedrows | Int |
| sp_getsubscriptiondtspackagename | Int |
| sp_grant_publication_access | Int |
| sp_help_agent_default | Table |
| sp_help_agent_parameter | Table |
| sp_help_agent_profile | Table |
| sp_help_publication_access | Table |
| sp_helparticle | Table |
| sp_helparticlecolumns | Table |
| sp_helparticledts | Table |
| sp_helpdistpublisher | Table |
| sp_helpdistributiondb | Table |
| sp_helpdistributor | Table |
| sp_helpmergealternatepublisher | Table |
| sp_helpmergealternatepublisher | Table |
| sp_helpmergearticle | Table |
| sp_helpmergearticlecolumn | Table |
| sp_helpmergearticleconflicts | Table |
| sp_helpmergeconflictrows | Table |
| sp_helpmergedeleteconflictrows | Table |
| sp_helpmergefilter | Table |
| sp_helpmergepublication | Table |
| sp_helpmergepullsubscription | Table |
| sp_helpmergesubscription | Table |
| sp_helppublication | Table |
| sp_helppullsubscription | Table |
| sp_helpreplfailovermode | Table |
| sp_helpreplicationdboption | Table |
| sp_helpreplicationoption | Table |
| sp_helpsubscriberinfo | Table |
| sp_helpsubscription | Table |
| sp_helpsubscription_properties | Table |
| sp_ivindexhasnullcols | Int |
| sp_link_publication | Int |
| sp_marksubscriptionvalidation | Int |
| sp_mergearticlecolumn | Int |
| sp_mergecleanupmetadata | Int |
| sp_mergedummyupdate | Int |
| sp_mergesubscription_cleanup | Int |
| sp_publication_validation | Int |
| sp_refreshsubscriptions | Int |
| sp_reinitmergepullsubscription | Int |
| sp_reinitmergesubscription | Int |
| sp_reinitpullsubscription | Int |
| sp_reinitsubscription | Int |
| sp_removedbreplication | Int |
| sp_repladdcolumn | Int |
| sp_replcmds | Table |
| sp_replcounters | Table |
| sp_repldone | Int |
| sp_repldropcolumn | Int |
| sp_replflush | Int |
| sp_replication_agent_checkup | Int |
| sp_replicationdboption | Int |
| sp_replqueuemonitor | Int |
| sp_replsetoriginator | Int |
| sp_replshowcmds | Table |
| sp_repltrans | Table |
| sp_restoredbreplication | (none) |
| sp_resyncmergesubscription | Int |
| sp_revoke_publication_access | Int |
| sp_script_synctran_commands | Int & Table |
| sp_setreplfailovermode | Int |
| sp_showrowreplicainfo | Table |
| sp_subscription_cleanup | Int |
| sp_table_validation | Int |
| sp_update_agent_profile | Int |
| sp_validatemergepublication | Int |
| sp_validatemergesubscription | Int |
| sp_vupgrade_replication | Int |
Security
| Procedure Name | Return Type | Description |
|---|---|---|
| sp_addalias | Int | Matches a server login to a database user. This is an older alternative to using role-based security. |
| sp_addapprole | Int | Adds an application role to a database to be used for programmatic access from an application component. |
| sp_addgroup | Int | Adds a user group to a database. This is an older alternative to using role-based security. |
| sp_addlinkedsrvlogin | Int | Matches a local server login to a linked server login for access to a remote database server. |
| sp_addlogin | Int | Adds a new server login. |
| sp_addremotelogin | Int | Adds a login to the local server for use by remote users. |
| sp_addrole | Int | Adds a new database role. |
| sp_addrolemember | Int | Adds a SQL Server user, role, Windows user, or group to a SQL Server role. |
| sp_addserver | Int | Obsolete. Similar to sp_addlinkedserver, adds the metadata representing a registered linked server with persistent access from the local server. |
| sp_addsrvrolemember | Int | Adds a server login to a server role. |
| sp_adduser | Int | Obsolete. Similar to sp_grantdbaccess, adds a SQL Server user, role, Windows user, or group to a database. |
| sp_approlepassword | Int | Modifies the password for an application role. |
| sp_change_users_login | Int & Table | Modifies the association between a server login and a database user. |
| sp_changedbowner | Int | Modifies the owner of a database. |
| sp_changegroup | Int | Obsolete. Similar to sp_addrolemember, modifies the role membership for a user. |
| sp_changeobjectowner | Int | Modifies the owner of any database object. |
| sp_dbfixedrolepermission | Int & Table | Returns permission information for all fixed database roles. |
| sp_defaultdb | Int | Modifies the default database setting for a login. |
| sp_defaultlanguage | Int | Modifies the default language setting for a login. |
| sp_denylogin | Int | Denies access to the server for a Windows user or group. |
| sp_dropalias | Int | Obsolete. Drops an alias associated with a database user. This is an older technique used before SQL Server role-based security. sp_droprolemember provides similar functionality as a recommended practice. |
| sp_dropapprole | Int | Drops an application role. |
| sp_dropgroup | Int & Table | Removes a database role. This is an older procedure provided for compatibility. |
| sp_droplinkedsrvlogin | Int | Removes the association between a local server login and a linked server login. |
| sp_droplogin | Int | Drops a local server login. |
| sp_dropremotelogin | Int | Drops a remote login from the local server. |
| sp_droprolemember | Int | Removes a user, login, Windows user, or group from a database role. |
| sp_dropserver | Int | Removes the record of a linked or remote server from a local server. |
| sp_dropsrvrolemember | Int | Removes a server login, Windows user, or group from a server role. |
| sp_dropuser | Int | Obsolete. Similar to sp_revokedbaccess, removes access to a database for a SQL Server user, Windows user, or group. |
| sp_grantdbaccess | Int | Adds access to a database for a server login, Windows user, or group. |
| sp_grantlogin | Int | Adds access for a Windows user of group to the database server using Windows Integrated Security. |
| sp_helpdbfixedrole | Table | Returns information about fixed database roles. |
| sp_helpgroup | Table | Obsolete. Returns information about database groups. |
| sp_helplinkedsrvlogin | Table | Returns information about linked server logins. |
| sp_helplogins | Table | Returns information about local server logins. |
| sp_helpntgroup | Table | Returns information about Windows groups. |
| sp_helpremotelogin | Table | Returns information about remote logins registered with the local server. |
| sp_helprole | Table | Returns information about fixed database roles. |
| sp_helprolemember | Table | Returns information about the roles for a database. |
| sp_helprotect | Table | Returns permissions information related to a specified database object. |
| sp_helpsrvrole | Table | Returns information about server roles. |
| sp_helpsrvrolemember | Table | Returns information about the logins, Windows user, and groups that are members of a specified server role. |
| sp_helpuser | Table | Returns information about database users, Windows users, groups, and database roles in a database. |
| sp_MShasdbaccess | Int & Table | Returns database information accessible to a user. |
| sp_password | Int | Adds or modifies the password for a login. |
| sp_remoteoption | Int & Table | Returns or modifies option settings for a remote login. |
| sp_revokedbaccess | Int | Removes a database user, Windows user, or group from a database. |
| sp_revokelogin | Int | Removes a login associated with a Windows user or group. |
| sp_setapprole | Int | Enables an application role for a database. Used to allow programmatic access from an application component. |
| sp_srvrolepermission | Int & Table | Returns permission information for a server role. |
| sp_validatelogins | Int & Table | Returns Windows user and group entries in the database server that no longer exist in the operating system or Windows domain. |
SQL Agent
| Procedure Name | Return Type | Description |
|---|---|---|
| sp_add_alert | Int | Creates a new alert. |
| sp_add_category | Int | Creates a new category that may be associated with jobs, operators, and so on. |
| sp_add_job | Int | Creates a new job to contain steps. |
| sp_add_jobschedule | Int | Creates a new schedule for an existing job. |
| sp_add_jobserver | Int | Changes the server that will run a job. |
| sp_add_jobstep | Int | Adds a step to an existing job. |
| sp_add_notification | Int | Creates and adds a notification for an alert. |
| sp_add_operator | Int | Creates an operator associated with a job and an alert. |
| sp_add_proxy | Int | (SQL Server 2005) Creates a proxy account for users and roles to execute jobs with elevated permissions, without administrative role membership. |
| sp_add_schedule | Int | (SQL Server 2005) Creates a schedule for use with a job. |
| sp_add_targetservergroup | Int | Creates a server group to associate a job with a group of servers. |
| sp_add_targetsvrgrp_member | Int | Adds a server to a target server group. |
| sp_apply_job_to_targets | Int | Associates a job with one or more target servers. |
| sp_attach_schedule | Int | (SQL Server 2005) Associates a schedule with a job. |
| sp_cycle_agent_errorlog | Int | (SQL Server 2005) Closes the agent error log and initializes a new log file. |
| sp_cycle_errorlog | Int | Closes the current error log and starts a new log file. |
| sp_delete_alert | Int | Deletes an alert. |
| sp_delete_category | Int | Deletes a category. |
| sp_delete_job | Int | Deletes a job. |
| sp_delete_jobschedule | Int | Deletes the schedule for a job. |
| sp_delete_jobserver | Int | Deletes the association between a job and a server. |
| sp_delete_jobstep | Int | Deletes a specified job step. |
| sp_delete_jobsteplog | Int | (SQL Server 20005) Deletes a specified job step, all job steps for a specified job, or those that meet other criteria. |
| sp_delete_notification | Int | Deletes notifications for an operator and an alert. |
| sp_delete_operator | Int | Deletes a specified operator. |
| sp_delete_proxy | Int | (SQL Server 20005) Removes a user proxy. |
| sp_delete_schedule | Int | (SQL Server 2005) Deletes a job schedule. |
| sp_delete_targetserver | Int | Removes a target server designation for a job. |
| sp_delete_targetservergroup | Int | Deletes a target server group. |
| sp_delete_targetsvrgrp_member | Int | Removes a target server from a group. |
| sp_detach_schedule | Int | (SQL Server 2005) Associates a schedule to a job. |
| sp_enum_login_for_proxy | Table | (SQL Server 2005) Returns logins associated with a proxy. |
| sp_enum_proxy_for_subsystem | Table | (SQL Server 2005) Returns proxy users that have access to a specified subsystem. |
| sp_enum_sqlagent_subsystems | Table | Returns the subsystems (process threads) for SQL Agent. |
| sp_grant_login_to_proxy | Int | (SQL Server 2005) Grants a login, user, Windows user, or group access to a proxy. |
| sp_grant_proxy_to_subsystem | Int | (SQL Server 2005) Assigns a DTS/Integration Services subsystem to a proxy user. |
| sp_help_alert | Table | Returns information about an alert or alerts. |
| sp_help_category | Table | Returns information about a category or categories. |
| sp_help_downloadlist | Table | Returns information about queued target server download instructions. |
| sp_help_job | Table | Returns information about a job or jobs. |
| sp_help_jobactivity | Table | Returns information about job activities. |
| sp_help_jobcount | Int | (SQL Server 2005) Returns the count of jobs for an associated schedule. |
| sp_help_jobhistory | Table | Returns information about jobs for associated servers. |
| sp_help_jobs_in_schedule | Table | (SQL Server 2005) Returns the information about jobs for an associated schedule. |
| sp_help_jobschedule | Table | Returns information about automated job scheduling. |
| sp_help_jobserver | Table | Returns information about a server associated with a job. |
| sp_help_jobstep | Table | Returns information about the steps for a job. |
| sp_help_notification | Table | (SQL Server 2005) Returns information about notifications. |
| sp_help_operator | Table | Returns information about an operator or operators. |
| sp_help_proxy | Table | (SQL Server 2005) Returns information about a proxy user or proxies. |
| sp_help_schedule | Table | Returns information about a schedule or schedules. |
| sp_help_targetserver | Table | Returns information about a job target server or servers. |
| sp_help_targetservergroup | Table | Returns information about a job target server group or groups. |
| sp_manage_jobs_by_login | Int | Removes or modifies jobs for a specified login. |
| sp_msx_defect | Int | Modifies the system registry to remove the server from target multiserver operations. |
| sp_msx_enlist | Int | Modifies the system registry to add the server to the available multiserver target list. |
| sp_msx_get_account | Table | Returns credentials information for a target server, used to log into a master server. |
| sp_msx_set_account | Int | Sets credentials for a target server to log into a master server. |
| sp_notify_operator | Int | Sends an e-mail message to an operator by using SQLiMail. |
| sp_post_msx_operation | Int | Inserts job information into the sysdownloadlist table for target servers to execute. |
| sp_purge_jobhistory | Int | Removes history metadata associated with a job. |
| sp_remove_job_from_targetss | Int | Removes the association between a job and a target server. |
| sp_resync_targetserver | Int | Synchronizes all job metadata from remote servers to the target server. |
| sp_revoke_login_from_proxy | (SQL Server 2005) | |
| sp_revoke_proxy_from_subsystem | (SQL Server 2005) Removes access to a subsystem for a proxy. | |
| sp_start_job | Int | Starts executing a job regardless of its schedule. |
| sp_stop_job | Int | Stops executing a job. |
| sp_update_alert | Int | Modifies the settings for an alert. |
| sp_update_category | Int | Modifies the name of a category. |
| sp_update_job | Int | Modifies the settings for a job. |
| sp_update_jobschedule | Int | Modifies the settings for a job's schedule. |
| sp_update_jobstep | Int | Modifies the settings for a step. |
| sp_update_notification | Int | (SQL Server 2005) Modifies the settings for a notification. |
| sp_update_operator | Int | Modifies the information for an operator. |
| sp_update_proxy | (SQL Server 2005) Modifies the information for a proxy user. | |
| sp_update_schedule | Int | (SQL Server 2005) Modifies an agent schedule. |
| sp_update_targetservergroup | Int | Modifies the name of a target server group. |
| sp_xml_preparedocument | Int | Uses an output parameter to return a numeric handle to a cached copy of a well-formed and prepared XML document structure. The initial XML document is passed into this procedure as a VarChar type. |
| sp_xml_removedocument | Int | Removes data from the server's XML cache. |
Категории