New System Stored Procedures

Microsoft® SQL Server™ includes these stored procedure:

• sp_add_agent_parameter adds a new parameter and its value to an agent’s profile.
• sp_add_agent_profile creates a new profile for a replication agent.
• sp_add_alert creates an alert.
• sp_addapprole adds a special type of role in the current database used for application security.
• sp_adddistpublisher creates a remote Publisher that uses the local Distributor.
• sp_adddistributiondb creates a new Distributor database and installs the Distributor schema.
• sp_add_category adds the specified job, alert, or operator category to the named server.
• sp_add_data_file_recover_suspect_db adds a data file to a file group when recovery cannot complete.
• sp_add_log_file_recover_suspect_db adds a log file to a file group when recovery cannot complete.
• sp_add_job adds a new job that can be executed by SQL Server Enterprise Manager.
• sp_add_jobschedule adds a job to the schedule of activities performed by SQL Server Enterprise Manager.
• sp_add_jobserver adds the specified server to the list of those servers available to process SQL Server jobs.
• sp_add_jobstep adds a step (operation) to a job.
• sp_addlinkedserver creates a linked server for use with distributed queries and OLE DB data sources.
• sp_addlinkedsrvlogin creates a new SQL Server login mapping for a linked server.
• sp_addmergearticle adds an article to an existing merge publication.
• sp_addmergefilter adds a new merge filter for the purpose of creating a partition based on a join with another table.
• sp_addmergepublication, creates a new merge publication.
• sp_addmergepullsubscription adds a merge subscription of type pull.
• sp_addmergepullsubscription_agent adds an agent for merge pull subscription at the Subscriber.
• sp_addmergesubscription creates a push or pull merge subscription.
• sp_add_notification sets up a notification for an alert.
• sp_add_operator sets up an operator (notification recipient) for use with alerts.
• sp_addpublication_snapshot creates a Snapshot Agent.
• sp_addpublisher70 adds a SQL Server version 7.0 Publisher at a SQL Server 6.5 Subscriber.
• sp_addpullsubscription adds a pull or an anonymous subscription at the current database of the Subscriber.
• sp_addpullsubscription_agent adds a new agent to the Subscriber’s database.
• sp_addrole creates a new Microsoft SQL Server role in the current database.
• sp_addrolemember adds a security account as a member of an existing SQL Server database role in the current database.
• sp_addsrvrolemember adds a login as a member of a fixed server role.
• sp_addsubscriber_schedule adds a schedule for the Distribution Agent and Merge Agent.
• sp_addsynctriggers creates synchronous transaction triggers at the Subscriber.
• sp_add_targetservergroup adds the specified server group name.
• sp_add_targetsvrgrp_member adds the specified group name. Adds the optionally specified server to the specified group.
• sp_apply_job_to_targets removes the specified job either from those servers on which the job has already been applied or from those servers on which the job is not currently applied.
• sp_approlepassword changes the password of an application role in the current database.
• sp_articlesynctranprocs generates synchronous transaction procedures at the Publisher.
• sp_attach_db attaches a database to the specified server.
• sp_attach_single_file_db works with a database having only one data file.
• sp_autostats immediately displays or changes the automatic statistics for one or many indexes.
• sp_browsereplcmds returns a result set containing the replicated commands stored in the distribution database.
• sp_catalogs returns the list of catalogs in the named linked server.
• sp_change_agent_parameter changes a parameter of a replication agent profile stored in Msagent_parameters.
• sp_change_agent_profile changes a parameter of a replication agent profile stored in Msagent_profiles
• sp_change_subscription_properties updates the security information in the MSsubscription_properties table.
• sp_changedistpublisher updates the properties of the distribution Publisher.
• sp_changedistributiondb updates the properties of the distribution database.
• sp_changedistributor_property updates the properties of the Distributor.
• sp_changedistributor_password changes the password for a Distributor.
• sp_changemergearticle changes the properties of a merge article.
• sp_changemergefilter changes some filter properties.
• sp_changemergepublication changes the properties of a merge publication.
• sp_changemergepullsubscription changes the properties of the merge pull subscription.
• sp_changemergesubscription changes a merge push or pull subscription.
• sp_changeobjectowner changes the owner of an object in the current database.
• sp_changesubscriber_schedule changes a Subscriber’s schedule for the Distribution Agent and Merge Agent.
• sp_check_for_sync_trigger determines if a user-defined trigger or stored procedure is being called in the context of an immediate-updating trigger.
• sp_column_privileges_ex returns column privileges for the specified table on the specified linked server.
• sp_columns_ex returns the column information for the given table.
• sp_createstats creates statistics for all eligible columns for all user-defined tables in the specified database.
• sp_cursor_list reports the attributes of server cursors currently open for the connection and visible to Transact-SQL statements.
• sp_cycle_errorlog closes the current error log file and cycles the error log extension numbers.
• sp_dbcmptlevel sets certain database behaviors to be compatible with a specified earlier version of SQL Server.
• sp_dbfixedrolepermission displays the permissions for each fixed database role.
• sp_delete_alert removes an alert used by SQL Server Enterprise Manager to respond to special conditions or events in SQL Server.
• sp_delete_backuphistory deletes the entries in the backup and restore history tables for old backup sets.
• sp_delete_category removes the specified category from the named server.
• sp_delete_job deletes a job.
• sp_delete_jobschedule removes a job from the scheduled list of jobs to be executed by SQL Server Enterprise Manager.
• sp_delete_jobserver removes the specified server from processing SQL Server jobs.
• sp_delete_jobstep removes a job step from a job used by SQL Server Enterprise Manager to perform automated activities.
• sp_delete_notification removes a notification used by SQL Server Enterprise Manager to send an alert to an operator in the event of an error returned by SQL Server.
• sp_delete_operator removes an operator used by SQL Server Enterprise Manager to identify the person notified when an alert occurs.
• sp_delete_targetserver removes the specified server from the list of available job servers.
• sp_delete_targetservergroup removes the specified server from the target server group.
• sp_delete_targetsvrgrp_member removes the specified group name.
• sp_deletemergeconflictrow deletes rows from the msmerge_delete_conflicts table.
• sp_denylogin prevents a Microsoft Windows NT® user or group from connecting to SQL Server.
• sp_describe_cursor reports the attributes of a server cursor.
• sp_describe_cursor_columns reports the attributes of the columns in the result set of a server cursor.
• sp_describe_cursor_tables reports the base tables referenced by a server cursor.
• sp_detach_db detaches a database from the specified server.
• sp_drop_agent_parameter drops one or all profiles from the MSagent_parameters table.
• sp_drop_agent_profile drops or all parameters of one or all profiles in the MSagent_parameters table.
• sp_dropapprole removes an application role from the current database.
• sp_dropdistpublisher drops a Publisher that serves as its own Distributor.
• sp_dropdistributiondb drops a Distributor database.
• sp_droplinkedsrvlogin removes an existing SQL Server login mapping to a linked server.
• sp_dropmergearticle removes an article from a merge publication.
• sp_dropmergefilter drops a merge filter.
• sp_dropmergepublication drops a merge publication and its associated Snapshot Agent.
• sp_dropmergepullsubscription drops a merge pull subscription.
• sp_dropmergesubscription drops a subscription to a merge publication and its associated Merge Agent.
• sp_droppullsubscription drops a subscription at the current database of the Subscriber.
• sp_droprole which removes a SQL Server role from the current database.
• sp_droprolemember removes a security account from a Microsoft SQL Server role in the current database.
• sp_dropsrvrolemember removes a SQL Server login, or a Windows NT user or group, from a fixed server role.
• sp_dumpparamcmd returns detailed information for a parameterized command stored in the distribution database.
• sp_enumcodepages returns a three-column list: code page, character set, and code page description.
• sp_enumcustomresolvers returns a listing of all available custom resolvers.
• sp_expired_subscription_cleanup periodically checks the status of all the subscriptions of every publication and identifies those that have expired.
• sp_foreignkeys returns the foreign keys defined on the specified remote table.
• sp_fulltext_catalog administers a full-text catalog.
• sp_fulltext_column administers a full-text indexed column.
• sp_fulltext_database initializes or disables full-text indexing.
• sp_fulltext_service changes full-text service settings.
• sp_fulltext_table administers a full-text indexed table.
• sp_generatefilters creates filters on foreign key tables when a specified table is replicated.
• sp_getmergedeletetype returns the type of merge delete.
• sp_get_distributor determines whether a Distributor is installed on a server.
• sp_grant_publication_access adds a login to the publication's access list.
• sp_grantdbaccess adds a security account in the current database for a login, or Windows NT user or group, and enables it to be granted permissions to perform activities in the database.
• sp_grantlogin allows a Windows NT user or group account to connect to SQL Server.
• sp_help_agent_default retrieves the ID of the default configuration for the agent type passed as parameter.
• sp_help_agent_parameter returns all the parameters of a profile from the MSagent_parameters table.
• sp_help_agent_profile displays the profile of a specified agent.
• sp_help_alert reports information about the alerts defined for the server.
• sp_help_category provides information about the specified classes of jobs, alerts, or operators.
• sp_helpdbfixedrole returns a list of the fixed database roles.
• sp_helpdistpublisher returns properties of a Publisher that serves as its own Distributor.
• sp_helpdistributiondb returns properties of the specified Distributor database(s).
• sp_help_downloadlist lists all rows in the sysdownloadlist system table for the supplied job, or all rows if no job is specified.
• sp_helpfile returns the physical names and attributes of files associated with the current database.
• sp_helpfilegroup returns the names and attributes of filegroups associated with the current database.
• sp_help_fulltext_catalogs gets a list of full-text catalogs.
• sp_help_fulltext_catalogs_cursor gets a list of full-text catalogs.
• sp_help_fulltext_tables gets a list of full-text indexed tables.
• sp_help_fulltext_tables_cursor gets a list of full-text indexed tables.
• sp_help_fulltext_columns gets a list of full-text indexed columns.
• sp_help_fulltext_columns_cursor gets a list of full-text indexed columns.
• sp_help_job returns information about jobs that are used by SQL Server Enterprise Manager to perform automated activities in SQL Server.
• sp_help_jobhistory provides information about the jobs for servers in the multiserver administration domain.
• sp_help_jobschedule returns information about the scheduling of jobs used by SQL Server Enterprise Manager to perform automated activities.
• sp_help_jobserver returns information about the specified job.
• sp_help_jobstep returns information for the steps in a job used by SQL Server Enterprise Manager to perform automated activities.
• sp_helpmergearticle returns information about an article.
• sp_helpmergearticleconflicts returns the articles in the publication that have conflicts.
• sp_helpmergeconflictrows returns the rows in the specified conflict table.
• sp_helpmergedeleteconflictrows returns the rows in the specified msmerge_delete_conflicts table.
• sp_helpmergefilter returns information about merge filter(s).
• sp_helpmergepublication returns information about a merge publication.
• sp_helpmergepullsubscription returns information about the pull subscription.
• sp_helpmergesubscription returns information about a push subscription.
• sp_help_notification reports a list of notifications.
• sp_helpntgroup reports information about Windows NT groups with accounts in the current database.
• sp_help_operator reports information about the operators defined for the server.
• sp_helppullsubscription displays information about one or more subscriptions at the Subscriber.
• sp_helpreplicationdboption shows the databases that have the replication option enabled.
• sp_helprole returns information about the roles in the current database.
• sp_helprolemember returns information about the members of a role in the current database.
• sp_helpsrvrole returns a list of the SQL Server fixed server roles.
• sp_helpsrvrolemember returns information about the members of a SQL Server fixed server role.
• sp_helpsubscription lists subscription information associated with a particular publication, article, Subscriber, or set of subscriptions.
• sp_helpsubscription_properties retrieves security information from the Mssubscription_properties table.
• sp_help_targetserver lists all target servers.
• sp_help_targetservergroup lists all target servers in the specified group. If no group is specified, SQL Server returns information on all target server groups.
• sp_helptrigger returns the type or types of triggers defined on the specified table for the current database.
• sp_indexes returns index information for the specified remote table.
• sp_indexoption sets option values for user-defined indexes.
• sp_link_publication sets the configuration and security information used by synchronization triggers when connecting to the Publisher.
• sp_linkedservers returns the list of linked servers defined in the local server.
• sp_makewebtask has additional parameters.
• sp_manage_jobs_by_login deletes or reassigns jobs belonging to the specified login.
• sp_mergedummyupdate does a dummy update on the given row so that changes to text columns made using UPDATETEXT are sent during the next merge.
• sp_mergesubscription_cleanup removes metadata such as triggers and entries in sysmergesubscription and sysmergeaarticles when a merge subscription is dropped at a Subscriber.
• sp_msx_defect removes the current server from multiserver operations.
• sp_msx_enlist adds the current server to the list of servers available for multiserver operations.
• sp_primarykeys returns the primary key columns for the specified remote table.
• sp_post_msx_operation inserts rows into the sysdownloadlist system table for target servers to download posted job operations.
• sp_procoption sets procedure options.
• sp_purge_jobhistory removes the history records for jobs that have been performed by SQL Server Enterprise Manager.
• sp_refreshsubscriptions adds subscriptions to new articles in a pull publication for all the existing Subscribers to the publication.
• sp_refreshview refreshes the metadata for the specified view.
• sp_reinitmergepullsubscription marks a merge subscription for reinitialization the next time the Merge Agent runs.
• sp_reinitmergesubscription marks a merge subscription for reinitialization the next time the Merge Agent runs.
• sp_reinitpullsubscription marks a merge subscription for reinitialization the next time the Merge Agent runs.
• sp_reinitsubscription resynchronizes the subscription.
• sp_remove_job_from_targets removes the specified job from the given target servers or target server groups.
• sp_replicationdboption sets a replication database option for the current database.
• sp_replication_agent_checkup checks each distribution database for replication agents that are running but have not logged history within the specified heartbeat interval.
• sp_replshowcmds returns the commands for transactions marked for replication in a readable format.
• sp_removedbreplication removes all replication objects from a database without updating data at the Distributor.
• sp_resync_targetserver resynchronizes the specified server in the multiserver domain.
• sp_revoke_publication_access removes the login from a publications access list.
• sp_revokedbaccess removes a security account from the current database.
• sp_revokelogin removes the login entries for a Windows NT user or group created with sp_grantlogin or sp_denylogin from SQL Server.
• sp_script_synctran_commands generates a script containing the sp_addsynctrigger calls to be applied at Subscribers for nonsynchronous Subscriptions.
• sp_scriptdelproc enerates the CREATE PROCEDURE statement to create a custom stored procedure.
• sp_scriptinsproc generates the CREATE PROCEDURE statement to create a custom stored procedure.
• sp_scriptupdproc generates the CREATE PROCEDURE statement to create a custom stored procedure.
• sp_serveroption provides these new server options:

• sp_setapprole activates the permissions associated with an application role in the current database.
• sp_srvrolepermission returns the permissions applied to a fixed server role.
• sp_start_job informs SQL Server Agent to begin the execution of a job.
• sp_stop_job informs SQL Server Agent to cancel the execution of a job.
• sp_subscription_cleanup cleans up metadata when a subscription is dropped at a Subscriber.
• sp_table_privileges_ex returns privilege information about the specified table from the specified linked server.
• sp_tables_ex returns table information on the matching remote tables and type of table.
• sp_update_agent_profile updates the profile for a type of replication agent.
• sp_update_alert updates information for an existing alert.
• sp_update_category changes the name of a category.
• sp_update_job changes the attributes of a job.
• sp_update_jobschedule changes the settings for a scheduled job.
• sp_update_jobstep changes the setting for a step in a job that is used by SQL Server Enterprise Manager to perform automated activities.
• sp_update_notification updates the notification method of an alert notification.
• sp_update_operator updates information about an operator.
• sp_updatestats runs UPDATE STATISTICS against all user-defined tables in the current database.
• sp_update_targetservergroup changes the name of the specified server group name.
• sp_validname checks for valid SQL Server identifier names.
• sp_validatelogins reports information about orphaned Windows NT users and groups that no longer exist in the Windows NT environment but still have entries in the SQL Server system tables.
• xp_sqlmaint calls the sqlmaint utility with a string containing sqlmaint switches.
• xp_trace_addnewqueue adds a new trace queue and sets trace queue configuration values.
• xp_trace_deletequeuedefinition deletes the named trace queue from the registry.
• xp_trace_destroyqueue destroys the given trace queue, but does not delete the queue definition.
• xp_trace_enumqueuedefname lists all currently defined trace queue names in alphabetic order.
• xp_trace_enumqueuehandles lists all currently active trace queue handles in numeric order.
• xp_trace_eventclassrequired returns the class name for the event classes being captured for the given queue handle when event class information is provided.
• xp_trace_flushqueryhistory flushes the last 100 SQL Server events.
• xp_trace_generate_event adds a user-defined event to all queues.
• xp_trace_getappfilter retrieves the current application filter value.
• xp_trace_getconnectionidfilter retrieves the current connection ID filter value.
• xp_trace_getcpufilter retrieves the current CPU filter value.
• xp_trace_getdbidfilter retrieves the current database identification number (DBID) filter value.
• xp_trace_getdurationfilter retrieves the current definition of the duration filter value (the event duration).
• xp_trace_geteventfilter retrieves the current event text filter definition.
• xp_trace_geteventnames lists the names of all event classes.
• xp_trace_getevents removes events from the trace queue created with xp_trace_seteventclassrequired and returns the events as a result set.
• xp_trace_gethostfilter retrieves the current host filter value is configured by executing xp_trace_sethostfilter and associated with the specified trace queue.
• xp_trace_gethpidfilter retrieves the host process identification number (HPID) filter.
• xp_trace_getindidfilter retrieves the current index identification number (INDID) filter.
• xp_trace_getntdmfilter retrieves the current Windows NT domain name filter.
• xp_trace_getntnmfilter retrieves the current Windows NT computer name filter.
• xp_trace_getobjidfilter retrieves the current object identification number (OBJID) filter.
• xp_trace_getqueueautostart retrieves the current trace queue’s startup configuration information.
• xp_trace_getqueuedestination retrieves the current trace queue destination filter value.
• xp_trace_getqueueproperties retrieves all current filters associated with the specified trace queue.
• xp_trace_getreadfilter retrieves the current read filter.
• xp_trace_getserverfilter retrieves the current server filter.
• xp_trace_getseverityfilter retrieves the current severity filter.
• xp_trace_getspidfilter retrieves the current system (internal) process identification number (SPID) filter.
• xp_trace_getsysobjectsfilter retrieves the current system objects filter.
• xp_trace_gettextfilter retrieves the current text filter.
• xp_trace_getuserfilter retrieves the current user filter.
• xp_trace_getwritefilter retrieves the current write filter.
• xp_trace_loadqueuedefinition loads a saved trace queue definition.
• xp_trace_pausequeue suspends new events from being added to the trace queue.
• xp_trace_restartqueue restarts a previously paused trace queue.
• xp_trace_savequeuedefinition specifies the trace queue definition to save.
• xp_trace_setappfilter specifies the application filter.
• xp_trace_setconnectionidfilter specifies the connection ID filter.
• xp_trace_setcpufilter specifies the CPU filter.
• xp_trace_setdbidfilter specifies the database identification number (ID) filter.
• xp_trace_setdurationfilter specifies the duration filter.
• xp_trace_seteventclassrequired specifies the event classes to trace or not trace for the specified trace queue.
• xp_trace_seteventfilter specifies the event description filter (entered as a text string).
• xp_trace_sethostfilter specifies the host name filter.
• xp_trace_sethpidfilter specifies the host process identification number (HPID) filter.
• xp_trace_setindidfilter specifies the index identification number (INDID) filter.
• xp_trace_setntdmfilter specifies the existing Windows NT domain name filter.
• xp_trace_setntnmfilter specifies the Windows NT username filter.
• xp_trace_setobjidfilter specifies the object identification number (OBJID) filter.
• xp_trace_setqueryhistory captures the last 100 SQL Server events.
• xp_trace_setqueueautostart configures the specified trace queue for automatic startup on the server’s next restart.
• xp_trace_setqueuecreateinfo specifies the trace queue properties to modify.
• xp_trace_setqueuedestination specifies the trace queue destination filter.
• xp_trace_setreadfilter specifies the read filter.
• xp_trace_setserverfilter specifies the SQL Server name filter.
• xp_trace_setseverityfilter specifies the severity filter.
• xp_trace_setspidfilter specifies the system process identification number (SPID) filter.
• xp_trace_setsysobjectsfilter specifies the system objects filter.
• xp_trace_settextfilter specifies the text filter.
• xp_trace_setuserfilter specifies the SQL Server username filter.
• xp_trace_setwritefilter specifies the write filter.
• xp_trace_startconsumer starts the specified consumer for processing trace queue information.