System Stored Procedures
SQL Server system stored procedures for administrative tasks, configuration, security management, database maintenance, and replication. — Page 2 of 7.
Disables change data capture for the specified source table and capture instance in the current database. Change data capture isn't available in every edition of SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and supported features The name of the schema in which the source table is contained.
No description available.
Enables change data capture for the current database. This procedure must be executed for a database before any tables can be enabled for change data capture (CDC) in that database. Change data capture records insert, update, and delete activity applied to enabled tables, making the details of the changes available in an easily consumed relational format. Column information that mirrors the column
Enables change data capture for the specified source table in the current database. When a table is enabled for change data capture, a record of each data manipulation language (DML) operation applied to the table is written to the transaction log.
Generates scripts to create wrapper functions for the change data capture query functions that are available in SQL Server. The API supported in the generated wrappers enables specifying the query interval as a datetime interval.
Returns change data capture metadata information for the captured source columns tracked by the specified capture instance. Change data capture isn't available in every edition of SQL Server.
Returns the data definition language (DDL) change history associated with the specified capture instance since change data capture was enabled for that capture instance. Change data capture isn't available in every edition of SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and supported features of SQL Server 2022 Th
Returns the change data capture configuration for each table enabled for change data capture in the current database. Up to two rows can be returned for each source table, one row for each capture instance. Change data capture isn't available in every edition of SQL Server.
Reports information about all change data capture cleanup or capture jobs in the current The maximum number of transactions to process in each scan is valid only for capture jobs. The maximum number of scan cycles to execute in order to extract is valid only for capture jobs.
Executes the change data capture log scan operation. Maximum number of transactions to process in each scan cycle. Maximum number of scan cycles to execute in order to extract all rows from the log.
Starts a change data capture cleanup or capture job for the current database.
Stops a change data capture cleanup or capture job for the current database.
Verifies that a database is correctly configured for distribution on removable media and reports Specifies the database to be verified. Gives ownership of the database and all database objects to the system administrator, and drops any user-created database users and nondefault permissions. This feature will be removed in a future version of SQL Server.
Changes a parameter of a replication agent profile stored in the table. This stored procedure is executed at the Distributor where the agent is running, on any profiles, the parameters that can be changed depend on the type of agent. To find out what If a parameter is supported for a given , but isn't defined in the agent profile, an error is returned.
Changes a parameter of a replication agent profile stored in the stored procedure is executed at the Distributor on any database. The new value of the property. This table describes the profile properties that can be changed.
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Configures optional performance settings for the change feed for the current database context. This system stored procedure is used to fine tune the operational performance for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases .
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Creates a source to maintain metadata specific to each table group. A table group represents the container for all the individual tables that will be replicated. This system stored procedure is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases Transact-SQL syntax
2022 (16.x) and later versions Azure SQL Database SQL Managed Instance Azure Synapse Analytics Mirrored databases in Microsoft SQL database in Microsoft Fabric Disable the change feed at the database level, and then the metadata for all the associated This system stored procedure is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases Azure Synapse Link Transac
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Removes a table from the change feed. This system stored procedure is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases The unique identifier of the change feed table group that the table belongs to.
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Drops a table group and internal metadata objects. This system stored procedure is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases . The unique identifier of the table group. It's a GUID generated by the initial setup. This system
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric SQL database in Microsoft Fabric Microsoft Fabric mirrored databases . Indicates the maximum number of transactions to process in each scan cycle.
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Stored procedure to enable the creation of a new table to an existing table group. This system stored procedure is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases The unique identifier of the table group. This system stored proced
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Checks for a supported service level objective. This system stored procedure is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases server role membership can execute this procedure. sys.sp_help_change_feed (Transact-SQL) sys.sp_help_
Mirrored databases in Microsoft Fabric SQL database in Microsoft Fabric This system stored procedure is used for Microsoft Fabric mirrored databases Identified for informational purposes only. Not supported. Future compatibility is not Identified for informational purposes only. Not supported. Future compatibility is not This system stored procedure is used internal
Mirrored databases in Microsoft Fabric SQL database in Microsoft Fabric Reseeds the link table in the current database context. This system stored procedure is used for Microsoft Fabric mirrored databases Identified for informational purposes only. Not supported. Future compatibility is not Identified for informational purposes only. Not supported. Future compatibil
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Upgrades the change feed schema if required. This system stored procedure is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases server role membership can execute this procedure.
Changes the primary database settings.
fixed server role can run this procedure. This example illustrates using database parameters for the database About log shipping (SQL Server) System stored procedures (Transact-SQL)
Changes secondary database settings.
Updates information for pull subscriptions. This stored procedure is executed at the Subscriber The name of the Publisher database.
Maps an existing database user to a SQL Server login. Describes the action for the stored procedure to perform. default, and can have one of the following values. system catalog view in the current database to a SQL Server login of the same name. If a login with the same name doesn't exist, one is created. Examine the result from the statement, to confirm that the c
Changes the properties of an article in a transactional or snapshot publication. This stored procedure is executed at the Publisher on the publication database.
Changes the article column data type mapping for an Oracle publication. This stored procedure is executed at the Distributor on any database.
Changes the owner of the current database. The login ID of the new owner of the current database. must be an already existing SQL Server login or Windows user. become the owner of the current database if it already has access to the database through an existing user security account within the database.
Changes the properties of the distribution Publisher. This stored procedure is executed at the A property to change for the given Publisher. properties in the table listed under The value for the given property.
Changes the properties of the distribution database. This stored procedure is executed at the The name of the distribution database. The property to change for the given database. History table retention period. Maximum distribution retention period. Minimum distribution retention period.
Changes the password for a Distributor. This stored procedure is executed at the Distributor on any database. If this is a remote Distributor, then it needs to be run on all the Publisher servers that are using this Distributor. If the distribution or Publisher database is in an availability group, then it needs to be run on all the Distributor and Publisher nodes.
2025 (17.x) and later versions. Specifies whether to trust the certificate used by the Distributor for encrypted connections. The default is SQL Server 2025 (17.x) and later versions. Specifies the expected host name in the Distributor's certificate. SQL Server 2025 (17.x) and later versions. The value for the given Distributor property.
Modifies the agent job that generates the snapshot for a subscription to a publication with a parameterized row filter. This stored procedure is executed at the Publisher on the publication
Changes security properties of a Log Reader agent. This stored procedure is executed at the Publisher on the publication database. The login for the account under which the agent runs. On Azure SQL Managed Instance, use a SQL Server account. When configuring a Publisher with a remote Distributor, the values supplied for all , are sent to the Distributor as plain text.
Changes the properties of a merge article. This stored procedure is executed at the Publisher The name of the publication in which the article exists.
Changes some merge filter properties. This stored procedure is executed at the Publisher on The current name of the filter.
Changes the properties of a merge publication. This stored procedure is executed at the Publisher on the publication database. The property to change for the given publication. values listed in the table that follows. The new value for the specified property. , and can be one of the values listed in the table that follows.
Changes the properties of the merge pull subscription. This stored procedure is executed at the Subscriber on the subscription database.
Changes selected properties of a merge push subscription. This stored procedure is executed at the Publisher on the publication database.
Changes the owner of an object in the current database.
Changes the properties of a publication. This stored procedure is executed at the Publisher on The publication property to change. This table describes the properties of the publication that can be changed and restrictions on the values for those properties.
Changes properties of the Snapshot Agent for the specified publication. This stored procedure is executed at the Publisher on the publication database. When you configure a Publisher with a remote Distributor, the values supplied for all , are sent to the Distributor as plain text.
Changes security properties of a Queue Reader agent. This stored procedure is executed at the Distributor on the distribution database or at the Publisher on the publication database. The login for the Windows account under which the agent runs. The password for the Windows account under which the agent runs.
Changes stored passwords for the Windows account or SQL Server login used by replication agents when connecting to servers in a replication topology. You would normally have to change a password for each individual agent running at a server, even if they all use the same login or account. This stored procedure enables you to change the password for all instances of a given SQL Server Login or Wind
Changes the options for a Subscriber. Any distribution task for the Subscribers to this Publisher is updated. This stored procedure writes to the database. This stored procedure is executed at the Publisher on the publication database.
Changes the Distribution Agent or Merge Agent schedule for a subscriber. This stored procedure is executed at the Publisher on any database. , with no default.
Changes the properties of a snapshot or transactional push subscription or a pull subscription involved in queued updating transactional replication. To change properties of all other types sp_change_subscription_properties executed at the Publisher on the publication database.
Changes the Data Transformation Services (DTS) package properties of a subscription. This stored procedure is executed at the Subscriber on the subscription database. The job ID of the Distribution Agent for the push subscription. default. To find the Distribution Job ID, run Specifies the name of the DTS package. For example, to specify a package named Specifies
Changes the status of an existing Subscriber. This stored procedure is executed at the Publisher
Displays information on parameterized row filter properties for a publication, in particular the functions used to generate a filtered data partition for a publication and whether the publication qualifies for using precomputed partitions. This stored procedure is executed at the Publisher on the publication database. Is if the publication qualifies for using precom
Determines whether a user-defined trigger or stored procedure is being called in the context of a replication trigger, which is used for immediate updating subscriptions. This stored procedure is executed at the Publisher on the publication database or at the Subscriber on the The object ID of the table being checked for immediate updating triggers.
Verifies a join filter between two tables to determine if the join filter clause is valid. This stored procedure also returns information about the supplied join filter, including if it can be used with precomputed partitions for the given table. This stored procedure is executed at the Publisher on the publication. For more information, see Parameterized Filters - Optimize for Transact-SQL syntax
Checks a filter clause against any table to determine if the filter clause is valid for the table. This stored procedure returns information about the supplied filter, including if the filter qualifies for use with precomputed partitions. This stored procedure is executed at the Publisher on the database containing the publication. The filter clause being tested. Sp
Removes residual information on data pages. cleans all pages in only one file of a database.
Removes residual information on data pages. cleans all pages in all data files of the database.
This stored procedure cleans up history locally, and on the monitor server, based on retention The primary ID for backup or the secondary ID for copy or restore.
Returns column privilege information for a single table in the current environment. The table used to return catalog information. Wildcard pattern matching isn't supported. The owner of the table used to return catalog information. Wildcard pattern matching isn't supported. If default table visibility rules of the underlying databa
Returns column privileges for the specified table on the specified linked server.
Returns column information for the specified objects that can be queried in the current The name of the object that is used to return catalog information. view, or other object that's columns such as table-valued functions. , with no default. Wildcard pattern matching is supported.
Returns the column information, one row per column, for the specified linked server tables. returns column information for only the specific column if The name of the linked server for which to return column information.
Returns information about the current SET options. The options can come from use of the command or from the value. Session values configured with the command override the Many tools, such as Management Studio, automatically configure set options.
2022 (16.x) and later versions SQL database in Microsoft Fabric Changes the configuration for the feature. The configuration options apply to a given These options include the ability to allow a to be allowed or skipped for APC consideration, or to configure APC to apply an additional extended, time-based plan regression check to that specific query. The configuration options are Transa
Configures conflict detection for a publication that is involved in a peer-to-peer transactional replication topology. For more information, see Peer-to-Peer - Conflict Detection in Peer-to- . This stored procedure is executed at the Publisher on the publication The name of the publication for which to configure conflict detection. conflict resolution of an existing
Adds or drops a credential containing the password needed to open a database master key Specifies the name of the database associated with this credential. Can't be a system database. Specifies the password of the DMK. Arguments for extended stored procedures must be entered in the specific order as section.
system stored procedure is used to drop, enable, or disable a plan Specifies the plan guide that is being dropped, enabled, or disabled. is resolved to the current database. If not specified, The operation to perform on the plan guide specified in Drops the plan guide specified by .
Copies data from the source table to the target table after verifying that their schema is identical in terms of number of columns, column names and their data types. columns are ignored since they're system generated and this allows copying data from a regular table to a ledger table and vice versa.
Copies the snapshot folder of the specified publication to the folder listed in the @destination_folder . This stored procedure is executed at the Publisher on the publication The name of the publication whose snapshot contents are to be copied. , with no default.
Copies the snapshot folder of the specified publication to the folder listed in the . This stored procedure is executed at the Publisher on the publication database. This stored procedure is useful for copying a snapshot to removable media.
Copies a subscription database that's pull subscriptions, but no push subscriptions. Only single file databases can be copied. This stored procedure is executed at the Subscriber on the The string that specifies the complete path, including file name, to which a copy of the data file This feature will be removed in a future version of SQL Server.
Creates an event group stream for the Specifies the name of the event stream group you want to create. , with no default, and can't be Change event streaming is currently in Azure SQL Database (preview feature database scoped configuration not required). During preview, this feature is subject to change.
Creates a plan guide for associating query hints or actual query plans with queries in a database. For more information about plan guides, see , with no default, and a maximum length of 124 characters. Plan guide names are scoped to the current database. and can't start with the number sign ( A Transact-SQL statement against which to create a plan guide.
Creates one or more plan guides from a query plan in the plan cache. You can use this stored procedure to ensure the query optimizer always uses a specific query plan for a specified query. For more information about plan guides, see , with no default. Plan guide names are scoped must comply with the rules for Identifies a batch in the plan cache.
Creates a removable media database. Creates three or more files (one for the system catalog tables, one for the transaction log, and one or more for the data tables) and places the 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.
Inserts a row in the management data warehouse view. This procedure is called every time an upload package starts uploading data to the management data warehouse. The GUID for the collection set. obtain the GUID, query the dbo.syscollector_collection_sets view in the The GUID for a collector type. obtain the GUID, query the dbo.syscollector_collector_types view in t
statement to create single-column statistics on columns that aren't already the first column in a statistics object. Creating single-column statistics increases the number of histograms, which can improve cardinality estimates, query plans, and query performance. The first column of a statistics object has a histogram; other columns don't have is useful for applica
Requests positioned updates. This procedure performs operations on one or more rows within value generated by the Database Engine and returned by the A required parameter that designates what operation the cursor performs. The parameter requires one of the following values. Arguments for extended stored procedures must be entered in the specific order as section. If
A data type for variables or stored procedure OUTPUT parameters that contain a reference to a The operations that can reference variables and parameters having a @local_variable @local_variable The OPEN, FETCH, CLOSE, and DEALLOCATE cursor statements. Stored procedure output parameters. The CURSOR_STATUS function. syste
Closes and deallocates the cursor, and releases all associated resources; that is, it drops the temporary table used in support of in a tabular data stream (TDS) packet. value generated by SQL Server and returned by the An input value of applies toward all cursors on the current connection.
Creates and populates a cursor based upon the execution plan created by The Database Engine-generated cursor identifier. is a required parameter that must be supplied on all subsequent procedures that act upon the cursor, such as Arguments for extended stored procedures must be entered in the specific order as section.
Fetches a buffer of one or more rows from the database. The group of rows in this buffer is value generated by SQL Server and returned by input value. For more information, see the Specifies which cursor buffer to fetch. is an optional parameter that requires one of the following integer input values. Arguments for extended stored procedures must be entered in the s
defines the SQL statement associated with the cursor and cursor options, and then populates the cursor. is equivalent to the combination of the Transact-SQL statements . This procedure is invoked by in a tabular data stream (TDS) packet. A SQL Server-generated cursor identifier. value that must be supplied on all subsequent procedures involving the cursor, such as a
Sets cursor options or returns cursor information created by the in a tabular data stream (TDS) value generated by SQL Server, and returned by the Used to stipulate various factors of the cursor return values. The must be one of the following values: Arguments for extended stored procedures must be entered in the specific order as section.
Compiles the cursor statement or batch into an execution plan, but doesn't create the cursor. The compiled statement can later be used by . This procedure, coupled with , but is split into two phases. in a tabular data stream (TDS) packet. A SQL Server-generated prepared procedure in order to open a cursor.
Compiles a plan for the submitted cursor statement or batch, then creates and populates the This procedure is invoked by specifying in a tabular data stream (TDS) packet. parameter is a required parameter that must be supplied on all subsequent procedures that act upon this cursor, for example, Arguments for extended stored procedures
Discards the execution plan developed in the in a tabular data stream (TDS) packet. when the statement is prepared. sp_cursorprepare (Transact-SQL) System stored procedures (Transact-SQL) Arguments for extended stored procedures must be entered in the specific order as section.
Closes the current SQL Server Agent error log file and cycles the SQL Server Agent error log extension numbers just like a server restart. The new SQL Server Agent error log contains a line indicating that the new log was created.
Closes the current error log file and cycles the error log extension numbers just like a server restart. The new error log contains version and copyright information and a line indicating that Every time SQL Server is started, the current error log is renamed to you to cycle the error log files without stopping and starting the server.
Returns list of table objects that are available to be virtualized.
Returns a list of columns in external data source table.
Lists databases that either reside in an instance of the SQL Server or are accessible through a database gateway.
Returns information about the data types supported by the current environment. The code number for the specified data type. list of all data types, omit this parameter. The version of ODBC that is used.
Enables or disables support for up to 15,000 partitions for the specified database. isn't specified, the current database is used. Enables or disables support for 15,000 partitions on the specified database. isn't specified, the procedure returns to indicate support is enabled for the specified database, or to indicate support is disabled.
Enables and disables selective XML index (SXI) functionality on a SQL Server database. If called without any parameters, the stored procedure returns if SXI is enabled on a particular The name of the database on which to to enable or disable selective XML index. , the current database is assumed. Determines whether to enable or disable the index.
storage format state of a database or enables a database for storage format. In SQL Server 2008 (10.0.x) and later versions, user databases are always enabled. However, because storage format is deprecated. Enabling databases for the format is only necessary in SQL Server 2005 (9.x).