System Stored Procedures

Returns a list of stored procedures in the current environment.

Removes metadata when a subscription is dropped at a Subscriber. For a synchronizing transaction subscription, it also includes immediate-updating triggers. This stored procedure is executed at the Subscriber on the subscription database.

Creates a collection item in a user-defined collection set. A collection item defines the data to be collected and the frequency with which the data is collected. The unique local identifier for the collection set. The GUID that identifies the collector type to use for this item. , with no default. For a list of collector types, query the The name of the collection

Creates a new collection set. You can use this stored procedure to create a custom collection In cases where the Windows account configured as a proxy is a non-interactive or interactive user that hasn't yet logged in, the profile directory will not exist, and the creation of the staging directory will fail.

Creates a collector type for the data collector. A collector type is a logical wrapper around the SSIS packages that provide the actual mechanism for collecting data and uploading it to the The GUID for the collector type. is an OUTPUT parameter of type it will be automatically created and returned as The name of the collector type. The XML schema for this collector

Deletes a collection item from a collection set. The unique identifier for the collection item. @collection_item_id , with a default of @collection_item_id must have a value if The name of the collection item. , with a default of an empty string. must have a value if @collection_item_id

Deletes a user-defined collection set and all its collection items. The unique identifier for the collection set. @collection_set_id , with a default of @collection_set_id must have a value if The name of the collection set. , with a default of @collection_set_id

Deletes the definition of a collector type. The GUID for the collector type. @collector_type_uid , with a default of and must have a value if The name of the collector type.

Deletes all the log entries for the run of a single collection set. It also deletes the log entries from the SSIS tables for that run. The unique identifier for the collection set log. The identifier for the collection set.

Disables the data collector. Because there's only one data collector per server, no parameters Defaults to the data collector on the server. (with EXECUTE permission) fixed database role to execute this procedure. The following example disables the data collector.

Enables the data collector. Because there's only one data collector per server, no parameters Defaults to the data collector on the server. (with EXECUTE permission) fixed database role to execute this procedure. The following example enables the data collector.

Starts a collection set if the collector is already enabled, and the collection set is configured for enables a user to take on-demand data snapshots. The unique local identifier for the collection set.

Specifies the directory where collected data is stored before it's uploaded to the management The directory in the file system where collected data is stored temporarily. , with a default of an empty string. If no value is specified, the default temporary You must disable the data collector before changing the cache directory configuration.

Sets the number of times to attempt a data upload if a failure occurs. Retrying an upload in the event of a failure mitigates the risk of losing collected data. The number of times a failed data upload to the management data warehouse is retried Cache all the upload data from the previous upload failures. Don't cache any data from an upload failure. previous upload

(with EXECUTE permission) fixed database role to The following example sets the name of the management data warehouse to System stored procedures (Transact-SQL)

(with EXECUTE permission) fixed database role to The following example illustrates how to configure the data collector to use a management data warehouse instance on a remote server. In this example, the remote server is named and the database is installed on the default instance. Data collector stored procedures (Transact-SQL) syscollector_config_store (Transact-SQL)

Starts a collection set if the collector is already enabled and the collection set isn't running. If the collector isn't enabled, enable the collector by running sp_syscollector_enable_collector then use this stored procedure to start a collection set. The unique local identifier for the collection set.

The unique local identifier for the collection set.

Used to modify the properties of a user-defined collection item or to rename a user-defined The unique identifier that identifies the collection item.

Used to modify the properties of a user-defined collection set or to rename a user-defined In cases where the Windows account configured as a proxy is a non-interactive or interactive user that hasn't yet logged in, the profile directory will not exist, and the creation of the staging directory will fail. Therefore, if you're using a proxy account on a domain contro

Updates a collector type for a collection item. Given the name and GUID of a collector type, updates the collector type configuration, including the collection and upload package, the parameter schema, and the parameter formatter schema. The GUID for the collector type. is automatically created and returned as OUTPUT.

Starts an upload of collection set data if the collection set is enabled. The unique local identifier for the collection set.

Adds a policy category that can be used with Policy-Based Management. Policy categories enable you to organize policies, and to set policy scope.

Policy-Based Management stored procedures (Transact-SQL) sp_syspolicy_add_policy_category_subscription (Transact-SQL) sp_syspolicy_delete_policy_category (Transact-SQL)

Configures settings for Policy-Based Management, such as whether Policy-Based Management The name of the setting that you want to configure.

Deletes a policy category in Policy-Based Management.

@policy_category_subscription_id , you can use the following query: The following example deletes a policy category subscription with an ID of 1. Policy-Based Management stored procedures (Transact-SQL) sp_syspolicy_update_policy_category_subscription (Transact-SQL)

, and to view execution history dates, you can use the following The following behavior applies if you specify To delete all policy execution history, specify To delete all policy execution history for a specific policy, specify a policy identifier for To delete policy execution history for all policies before a specific date, specify To archive policy execution history, you can open the Policy Hi

'Deletes the policy health states in Policy-Based Management. Policy health states are visual indicators within Object Explorer (a scroll symbol with a red "X") that help you to determine which nodes have failed a policy evaluation. Represents the node in Object Explorer where you want to clear the health state. @target_tree_root_with_id , with a default of You can s'

Removes the policy evaluation history according to the history retention interval setting. This stored procedure has no parameters. To view the history retention interval, you can use the following query: If the history retention interval is set to , policy evaluation history isn't removed.

Renames an existing condition in Policy-Based Management.

Renames an existing policy in Policy-Based Management.

Renames an existing policy category in Policy-Based Management.

'Repairs policy automation in Policy-Based Management. For example, you can use this stored procedure to repair triggers and jobs that are associated with policies that are configured to use "On schedule" or "On change" evaluation modes. This stored procedure has no parameters.'

Enables or disables Policy-Based Management. Determines whether Policy-Based Management is enabled.

Specifies the number of days to keep policy evaluation history for Policy-Based Management. The number of days to retain Policy-Based Management history. in the context of the , the history isn't automatically removed. To view the current value for history retention, run the following query:

Specifies whether successful policy evaluations are logged in the Policy History log for Policy- Based Management. Determines whether successful policy evaluations are logged. one of the following values: - Successful policy evaluations aren't logged. - Successful policy evaluations are logged. in the context of the system database. , only failed policy evaluations

Adds a policy category subscription for the specified database.

The following example deletes a subscription to the policy category for the specified Policy-Based Management stored procedures (Transact-SQL) sp_syspolicy_subscribe_to_policy_category (Transact-SQL)

Updates whether a policy category is set to mandate database subscriptions. If subscription is mandated, the policy category applies to all databases.

sp_syspolicy_update_policy_category_subscription (Transact-SQL) sp_syspolicy_unsubscribe_from_policy_category (Transact-SQL)

Returns a list of table permissions (such as The table used to return catalog information. Wildcard pattern matching is supported. The table owner of the table used to return catalog information. Wildcard pattern matching is supported. If the owner isn't specified, the default table visibility rules of the underlying DBMS apply.

Returns privilege information about the specified table from the specified linked server.

If the Distribution Agent is executing , specifies whether the Distribution Agent should shut down immediately upon completion of the validation. @shutdown_agent , with a default of , the replication agent doesn't shut down. If , error 20578 is raised and the replication agent is signaled to shut down. This parameter is ignored when is executed directly by a user. The table name of the view used f

Sets option values for user-defined tables.

Returns a list of objects that can be queried in the current environment. This means any table or view, except synonym objects. Syntax for SQL Server, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System The table used to return catalog information.

Returns table information about the tables from the specified linked server.

Tests the connection to a linked server. If the test is unsuccessful, the procedure raises an exception with the reason of the failure.

Invalid parameters. Returned when the user supplied incompatible parameters. is a SQL Server stored procedure that performs many of the actions previously executed by extended stored procedures available in earlier versions of SQL Server. Use only creates a trace definition. This stored procedure can't be used to start or change a trace. Parameters of all SQL Trace stored procedures ( ) are strict

Creates a user-defined event. The event can be collected using , with no default. The ID must be in the range from inclusive. This range represents user-defined events. In SQL Trace, use to add an event with this ID to a trace to capture events with the same ID fired from this stored procedure. deprecated. All other SQL Trace related stored procedures are Arguments

Adds or removes an event or event column to a trace. on existing traces that are stopped ( ). An error is returned if this stored procedure is executed on a trace that doesn't exist or whose This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature

can be executed only on existing traces that are ). SQL Server returns an error if this stored procedure is executed on a trace that doesn't exist or whose This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Modifies the current state of the specified trace. The ID of the trace to be modified. , with no default. The user employs this value to identify, modify, and control the trace. For information about retrieving the This feature will be removed in a future version of SQL Server.

Removes one or more user-defined defaults from the current database. : SQL Server ( SQL Server 2016 (13.x) through current version Conditionally drops the default only if it already exists. Is the name of the schema to which the default belongs. Is the name of an existing default. To see a list of defaults that exist, execute must comply w

Unbinds a rule from a column or an alias data type in the current database.

Discards the execution plan created by the in a tabular data stream (TDS) packet. The following example prepares, executes, and unprepares a basic statement. Arguments for extended stored procedures must be entered in the specific order as section.

This stored procedure removes a user-defined custom stored procedure or Transact-SQL script file that was registered by executing executed at the Publisher on the publication database. The type of custom stored procedure or script being removed. be one of the following values. Registered custom stored procedure or script is executed when an Registered custom stored

Unregisters a previously registered business logic module. Business logic can be in the form of either a COM component or a Microsoft .NET Framework assembly. This stored procedure is executed on the Distributor where the custom business logic was registered. Specifies the name of the custom business logic being unregistered. @article_resolver , with no default. If

Deactivates an application role and reverts to the previous security context. Specifies the cookie that was created when the application role was activated. , with no default. The cookie is created by 0 (success) and 1 (failure) is currently documented as which is the correct maximum length. However the current implementation returns . Applications should continue t

Updates the profile used by a replication agent. This stored procedure is executed at the Distributor on the distribution database. , with no default, and can be one of these values.

Updates the settings of an existing alert.

Changes the name of a category.

Updates an existing row or inserts a new row in the management data warehouse table. This procedure is called by the data collector run-time component every time an upload package starts uploading data to the management data The GUID for the collection set.

Updates the attributes of an existing job created in the SQL Server Agent service. The identification number of the job to be updated. must be specified, but both can't be specified.

Changes the schedule settings for the specified job in the SQL Server Agent service. is provided for backward compatibility only. Job schedules can now be managed independently of jobs. To update a schedule, use permissions on this procedure, but these permissions might be overridden during a SQL Server upgrade.

Changes the setting for a step in a job that is used to perform automated activities in the SQL The identification number of the job to which the step belongs.

Updates the notification method of an alert notification.

Updates information about an operator (notification recipient) for use with alerts and jobs.

Defines attributes of a SQL Server Agent proxy account. This table is stored in the ID of the proxy account.

Changes the settings for a SQL Server Agent schedule. The identifier of the schedule to modify.

Changes the name of the specified target server group.

Updates the value of an existing extended property.

against all user-defined and internal tables in the current database. For more information about UPDATE STATISTICS . For more information about statistics, see UPDATE STATISTICS isn't specified, updates statistics by using the default sampling. The with a default value of , by specifying the keyword, on all user- d

stored procedure is invoked automatically for upgrading metadata that is specific to log shipping. This stored procedure is invoked automatically during SQL Server upgrade for upgrading metadata for log shipping. You don't need to execute this procedure explicitly, unless a problem occurs with the metadata during upgrade.

Verifies that the current host for the publishing database is capable of supporting replication. Must be run from a distribution database. This procedure is called by The name of the instance of SQL Server that originally published the database.

that allows all secondary replicas to be validated, rather than just the current primary replica. entire Always On replication topology. executed directly on the distributor by using a remote desktop session to avoid a double-hop The name of the instance of SQL Server that originally published the database.

Reports information about Windows users and groups that are mapped to SQL Server principals but no longer exist in the Windows environment. Windows security identifier (SID) of the Windows user or group.

Performs a publication-wide validation for which all subscriptions (push, pull, and anonymous) are validated once. This stored procedure is executed at the Publisher on the publication The type of validation to perform. , and can be one of the following values. Rowcount and checksum validation.

Performs a validation for the specified subscription. This stored procedure is executed at the Publisher on the publication database.

Checks for valid SQL Server identifier names. All nonbinary and nonzero data, including Unicode data that can be stored by using the accepted as valid characters for identifier names. , can't be an empty string, and can't contain a binary-zero character. Specifies whether to raise an error. causes no error messages to appear.

Verifies the database ledger and the table ledgers. For each row in the 1. Recomputes a value stored in the previous_block_hash column of the row. 2. Checks if the recomputed value matches the value currently stored in the 3. If the specified list of digests contains a digest for the ledger block the row represents, it verifies the recomputed value matches the hash in the digest. 4.

Regenerates the article-specific triggers, stored procedures, and views that are used to track and apply data changes for merge replication. Execute this procedure in the following If an object required by replication is accidentally dropped. If you apply an update, such as a hotfix, which requires modification to one or more replication objects. Execute the procedure on each node after applying t

Activated by setup when upgrading a replication server. Upgrades schema and system data as needed to support replication at the current product level. Creates new replication system objects in system and user databases. This stored procedure is executed at the machine where the replication upgrade is to occur.

This procedure is scoped to an Active Geo-Replication relationship between a primary and causes the application to wait until all committed transactions are replicated and acknowledged by the active secondary database.

Provides information about current users, sessions, and processes in an instance of the SQL Server Database Engine. The information can be filtered to return only those processes that aren't idle, that belong to a specific user, or that belong to a specific session. Used to filter the result set. that identifies processes belonging to a particular login. is a sessio

Number of characters into the currently executing batch or stored procedure at occurs.

Removes the internal representation of the XML document specified by the document handle and invalidates the document handle. A parsed document is stored in the internal cache of SQL Server. The MSXML parser ) uses one-eighth the total memory available for SQL Server. To avoid running The handle to the newly created document.

Creates a proxy credential for Specifies that the proxy credential should be deleted. Specifies the Windows account to be the proxy. is disabled by default. To enable Arguments for extended stored procedures must be entered in the specific order as section.

Binds the specified In-Memory OLTP database to the specified resource pool. Both the database and the resource pool must exist prior to executing This system procedure creates a binding between the Resource Governor pool identified by , and the database identified by the database has any memory-optimized objects at the time of binding. In the absence of memory-optimized objects, there's no memory

Marks source files used in the merge operation with the log sequence number (LSN) after which they aren't needed and can be garbage collected. Also, moves the files whose associated LSN is lower than the log truncation point to FILESTREAM garbage collection.

Enables statistics collection for natively compiled stored procedures for the instance. To enable statistics collection at the query level for natively compiled stored procedures, see sys.sp_xtp_control_query_exec_stats Determines whether procedure-level statistics collection is on ( @new_collection_value is set to zero when SQL Server or the database starts. for success. Nonzero for failure.

Enables per query statistics collection for all natively compiled stored procedures for the instance, or specific natively compiled stored procedures. Performance decreases when you enable statistics collection. If you only need to troubleshoot one, or a few natively compiled stored procedures, you can enable statistics collection for just those few natively compiled stored procedures. To enable s

2016 (13.x) and later versions SQL database in Microsoft Fabric Invokes the data flush task to move all committed rows from in-memory staging table to the The schema name for the current or temporal table.

Causes the in-memory engine to release memory related to deleted rows of in-memory data that are eligible for garbage collection, which haven't yet been released by the process. In cases where a large volume of in-memory data has been released, and where the memory isn't soon be needed for other in-memory data, this procedure can free up memory for other uses.

Merges all data and delta files in the transaction range specified. Creating and Managing Storage for Memory-Optimized Objects The name of the database on which to invoke the merge. database doesn't have in-memory tables, this procedure returns with user error. If the database is offline, it returns an error. lower bound of transactions for a data file as shown in s

This system procedure removes an existing binding between a database and a resource pool for purposes of tracking In-Memory OLTP memory usage. If there's no pool currently bound to the specified database, success is returned. When the database is unbound, the previously allocated memory for memory-optimized objects stays allocated to the previous resource pool.