System Stored Procedures
SQL Server system stored procedures for administrative tasks, configuration, security management, database maintenance, and replication. — Page 5 of 7.
Returns Distributor properties. This stored procedure is executed at the Distributor on the The maximum number of minutes that an agent can go without logging is used with all types of replication.
Returns information on agent jobs that generate filtered data snapshots. This stored procedure is executed at the Publisher on the publication database. information on all filtered data snapshot jobs that match the specified The name of a filtered data snapshot job. , which returns all dynamic jobs for a publication with the specified . If a job name wasn't explicit
Reports the currently defined extended stored procedures and the name of the dynamic-link library (DLL) to which the procedure (function) belongs.
Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server. The logical name of any file in the current database. isn't specified, the attributes of all files in the current database are returned. Numeric identifier of the file. A value isn'
Returns the names and attributes of filegroups associated with the current database. The logical name of any filegroup in the current database. isn't specified, all filegroups in the current database are listed and only the first result set shown in the Result Sets section is displayed.
Reports information about the indexes on a table or view. The qualified or nonqualified name of a user-defined table or view. , with no default. Quotation marks are required only if a qualified table or view name is specified.
Reports information about a particular alternative language or about all languages in SQL The name of the alternative language for which to display information. is specified, information about the specified language is returned. If language isn't specified, information about all languages in the compatibility view is returned. Langua
Provides information about login mappings defined against a specific linked server used for distributed queries and remote stored procedures.
Provides information about logins and the users associated with them in each database. isn't specified, information about all logins is The first report contains information about each login specified, as shown in the following
Returns properties of the Log Reader Agent job for the publication database. This stored procedure is executed at the Publisher on the publication database. non-SQL Server Publishers only. The value of this parameter must be Security mode used by the agent when connecting to the Publisher, which can be one of the following values:
Returns a list of all servers enabled as alternate Publishers for merge publications. This stored procedure is executed at the Subscriber on the subscription database.
Returns information about an article. This stored procedure is executed at the Publisher on the publication database or at a republishing Subscriber on the subscription database.
Returns the list of columns in the specified table or view article for a merge publication. Because stored procedures don't have columns, this stored procedure returns an error if a stored procedure is specified as the article. This stored procedure is executed at the Publisher The name of a table or view that is the article to retrieve information on.
Returns the articles in the publication that have conflicts. This stored procedure is executed at the Publisher on the publication database, or at the Subscriber on the merge subscription The name of the merge publication. returns all articles in the database that have conflicts.
Returns the rows in the specified conflict table. This stored procedure is run on the computer where the conflict table is stored. specified, all conflicts qualified by the publication are returned. For example, if the table has conflict rows for the retrieves conflicts that pertain to the The name of the conflict table. , with no default.
Returns information on data rows that lost delete conflicts. This stored procedure is executed at the Publisher on the publication database or at the Subscriber on the subscription database when decentralized conflict logging is used. specified, all conflicts qualified by the publication are returned.
Returns information about merge filters. This stored procedure is executed at the Publisher on , which returns the names of all The name of the filter about which to return information.
Returns partition information for the specified merge publication. This stored procedure is executed at the Publisher on any database. value used to define a partition. Supply this parameter to limit the result set to only partitions where resolves to the supplied value. value used to define a partition.
Returns information about a merge publication. This stored procedure is executed at the Publisher on the publication database. information about all merge publications in the current database. A flag to indicate returning rows. is an OUTPUT parameter of type indicates the publication is found. indicates the publication isn't found.
Returns information about pull subscriptions that exist at a Subscriber. This stored procedure is executed at the Subscriber on the subscription database. information about all merge publications and subscriptions in the current database is returned.
Returns information about a subscription to a merge publication, both push and pull. This stored procedure is executed at the Publisher on the publication database or at a republishing Subscriber on the subscription database. must already exist and conform to the rules for merge publications and subscriptions in the current database is returned.
Reports information about Windows groups with accounts in the current database.
Returns information on all status requests received by participants in a peer-to-peer replication topology, where these requests were initiated by executing published database in the topology. This stored procedure is executed on the publication database at a Publisher participating in a peer-to-peer replication topology. For more Peer-to-Peer - Transactional Replication Transact-SQL syntax conven
Returns all responses to a specific status request received from a participant in a peer-to-peer replication topology, where the request was initiated by executing any published database in the topology. This stored procedure is executed on the publication database at a Publisher participating in a peer-to-peer replication topology.
Returns information about a publication. For a SQL Server publication, execute this stored procedure at the Publisher on the publication database. For an Oracle publication, execute this stored procedure at the Distributor on any database.
Returns information on the Snapshot agent for a given publication. Execute this stored procedure at the Publisher on the publication database. Specifies a non-SQL Server publisher. when you add an article to a SQL Server Publisher.
Drops an anonymous agent for replication monitoring at the distributor from the Publisher. This stored procedure is executed at the Publisher on any database. The global identifier for an anonymous subscription. default. This identifier can be retrieved at the Subscriber using field of the returned result set is this global identifier. The type of subscription.
Returns properties of the Queue Reader agent. This stored procedure is executed at the Distributor on the distribution database or at the Publisher on any database. Specifies whether the stored procedure is called at the Publisher or at the Distributor. means that the stored procedure is called from the Publisher. means that the stored procedure is called from the D
Reports information about remote logins for a particular remote server, or for all remote servers, defined on the local server. Specifies the remote server about which the remote login information is returned. about all remote servers defined on the local server is returned. A specific remote login on the remote server. isn't specified, information about all remote
Displays the current failover mode of a subscription. This stored procedure is executed at the Subscriber on any database. For more information about failover modes, see Subscriptions - For Transactional Replication The name of the Publisher that is participating in the update of this Subscriber. , with no default. The Publisher must already be configured for publis
Shows whether databases at the Publisher are enabled for replication. This stored procedure is executed at the Publisher on any database. Not supported for Oracle Publishers. databases at the Publisher, otherwise only information on the specified database is returned. Information isn't returned for any databases on which the user doesn't have the appropriate Restric
Shows the types of replication options enabled for a server. This stored procedure is executed at any server on any database.
Returns information about the roles in the current database.
Returns information about the direct members of a role in the current database.
Returns a report that's information about user permissions for an object, or statement permissions, in the current database. Doesn't list permissions that are always assigned to the fixed server roles or fixed database roles. Doesn't include logins or users that receive permissions based on their membership in a The name of the object in the current database, or a s
Reports information about a particular remote or replication server, or about all servers of both types. Provides the server name, the network name of the server, the replication status of the server, the identification number of the server, and the collation name. Also provides time-out values for connecting to, or queries against, linked servers.
Displays the sort order and character set for the instance of SQL Server. Returns server default collation. If an instance of SQL Server is installed with a collation specified to be compatible with an earlier installation of SQL Server, returns blank results.
Returns a list of the SQL Server fixed server roles.
Returns information about the members of a SQL Server fixed server role.
Returns statistics information about columns and indexes on the specified table. Specifies the table on which to provide statistics information. no default. A one-part or two-part name can be specified. Specifies the extent of information to provide.
Displays information about a Subscriber. This stored procedure is executed at the Publisher on , and defaults to the name of the current shouldn't be specified, except when it's an Oracle Publisher.
Lists subscription information associated with a particular publication, article, Subscriber, or set of subscriptions. This stored procedure is executed at a Publisher on the publication database.
Retrieves security information from the is executed at the Subscriber. information on all Publishers.
Returns all transactional replication errors for a given subscription. This stored procedure is executed at the Distributor on the distribution database.
Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, view, or system object such as a system stored procedure. The qualified or nonqualified name of a user-defined, schema-scoped object. , with no default.
Returns detailed latency information for specified tracer tokens, with one row being returned for each Subscriber. This stored procedure is executed at the Publisher on the publication database or at the Distributor on the distribution database.
Returns one row for each tracer token that was inserted into a publication to determine latency. This stored procedure is executed at the Publisher on the publication database or at the Distributor on the distribution database.
Returns the type or types of data manipulation language (DML) triggers defined on the specified table for the current database. can't be used with data definition language (DDL) triggers. Query the The name of the table in the current database for which to return trigger information.
Reports information about database-level principals in the current database.
Displays information on the Xactset job for an Oracle Publisher. This stored procedure is executed at the Distributor on any database.
Returns index information for the specified remote table.
Sets locking option values for user-defined clustered and nonclustered indexes or tables with The SQL Server Database Engine automatically makes choices of page-, row-, or table-level locking. You don't have to set these options manually. users who know with certainty that a particular type of lock is always appropriate. The qualified or nonqualified name of a user-
Invalidates the specified in-row text pointer, or all in-row text pointers, in the transaction.
stored procedure invokes an HTTPS REST endpoint provided as an input argument to the procedure. To mitigate the risk of unauthorized access or transfer of data, consider the following security : Ensure that only authorized users have access to sensitive data and REST API endpoints. Use the database roles and privileges. : Ensure that all REST calls are authenticate
Validates that the clustered index of the indexed view is unique, and doesn't contain any when the indexed view is going to be used to create a transactional publication. This stored procedure is executed at the Publisher on the publication database.
Closes nontransactional file handles to FileTable data.
Sets the configuration and security information used by synchronization triggers of immediate updating subscriptions when connecting to the Publisher. This stored procedure is executed at the Subscriber on the subscription database.
Returns the list of linked servers defined in the local server. (success) or a nonzero number (failure). Friendly name of the OLE DB provider managing access to the Product name of the linked server. OLE DB data source property corresponding to the specified OLE DB provider string property corresponding to the linked
Reports information about locks. A Database Engine session ID number from information about the session. If isn't specified, information about all locks is displayed. Another Database Engine session ID number from and about which the user also wants information. This feature will be removed in a future version of SQL Server. Avoid using this feature in new developme
Returns the information on a business logic handler or the class identifier (CLSID) value of a COM-based custom resolver component that is registered at the Distributor. This stored procedure is executed at the Publisher on the publication database. Specifies the name of the custom business logic being unregistered. , with no default.
commits, aborts, or forgets a specified transaction. Specifies the MSDTC transaction ID (transaction unit of work). Specifies operation to perform. Valid values are Arguments for extended stored procedures must be entered in the specific order as section.
Deletes or reassigns jobs that belong to the specified login. The action to take for the specified login. The login name of the current job owner. The login name of the new job owner.
Used for supportability of merge publications by enabling an administrator to skip selected pending schema changes, so that they aren't replicated. This stored procedure is executed at the Publisher on the publication database. Identifies a pending schema change. sp_enumeratependingschemachanges to list the pending schema changes for the publication. This stored procedure can cause schema changes
Marks the current open transaction to be a subscription-level validation transaction for the specified subscriber. This stored procedure is executed at the Publisher on the publication The name of the destination database.
Partitions a merge publication vertically. This stored procedure is executed at the Publisher on The name of the article in the publication. Identifies the columns on which to create the vertical partition. , all columns in the source table are added columns from an article, execute for each column to be removed from the specified
Should only be used in replication topologies that include servers running versions of SQL Server before SQL Server 2000 (8.x) Service Pack 1. administrators to clean up metadata in the system tables. This stored procedure is executed at the Publisher on the metadata for all publications. The publication must already exist if explicitly specified. Specifies whether
Does a dummy update on the given row, so that it sends again during the next merge. This stored procedure can be executed at the Publisher, on the publication database, or at the Subscriber, on the subscription database.
Performs a manual cleanup of metadata in the MSmerge_past_partition_mappings MSmerge_current_partition_mappings system tables. This stored procedure is executed at each Publisher and Subscriber in the topology.
Removes metadata, such as triggers and entries, in after the specified merge push subscription is removed at the Publisher. This stored procedure is run at the Subscriber on the subscription database.
Converts a database user that is mapped to a SQL Server login, to a contained database user with password. In a contained database, use this procedure to remove dependencies on the instance of SQL Server where the database is installed.
Displays statistics about SQL Server. Number of elapsed seconds since Number of seconds that the server computer's CPU has done SQL Server work. Number of seconds that SQL Server spent doing input and output operations. Number of seconds that SQL Server was idle.
fixed server role at the Distributor can execute sp_addpushsubscription_agent (Transact-SQL) sp_addsubscription (Transact-SQL)
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Internal procedure that handles data definition language events. This system stored procedure is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases Identified for informational purposes only. Not supported.
Changes the properties of a Log Reader Agent job that runs at a SQL Server 2005 (9.x) or later version Distributor. This stored procedure is used to change properties when the Publisher runs on an instance of SQL Server 2000 (8.x). This stored procedure is executed at the Distributor on The name of the publication database.
Changes the properties of a Merge Agent job that runs at a SQL Server 2005 (9.x) or later version Distributor. This stored procedure is used to change properties when the Publisher runs on an instance of SQL Server 2000 (8.x). This stored procedure is executed at the Distributor on The name of the publication database.
Changes the properties of a Snapshot Agent job that runs at a SQL Server 2005 (9.x) or later version Distributor. This stored procedure is used to change properties when the Publisher runs on an instance of SQL Server 2000 (8.x).
Lists the name and owner of all the databases to which the user has access. Execute permission is granted to the sys.sysdatabases (Transact-SQL)
Removes the current server from multiserver operations. Specifies whether to force the defection to occur if the Master SQLServerAgent has been permanently lost due to an irreversibly corrupt , which indicates that no forced defection should After you force a defection by executing server role at the Master SQLServerAgent must run the following command to complete t
Adds the current server to the list of available servers on the master server.
Lists information on the credential that the target server uses to sign in to the master server. Returns the following result set: Master server connection number.
Sets the SQL Server Agent master server account name and password on the target server.
Sends an e-mail message to an operator using Database Mail.
Destroys a created OLE object. The object token of an OLE object that was previously created by using (success) or a nonzero number (failure) that is the integer value of the HRESULT returned by the OLE Automation object. For more information about HRESULT return codes, see OLE automation return codes and error Arguments for extended stored procedures must be entere
Destroys a created OLE object. The object token of an OLE object that was previously created by using (success) or a nonzero number (failure) that is the integer value of the HRESULT returned by the OLE Automation object. For more information about HRESULT return codes, see OLE automation return codes and error Arguments for extended stored procedures must be entere
Help context ID in the Help source file. Each call to an OLE Automation stored procedure (except ) resets the error information; therefore, obtains error information only for the most recent OLE Automation stored procedure call. Because doesn't reset the error information, it can be called multiple times to get the same error information. The following table lists OLE Automation errors and their c
Gets a property value of an OLE object. The object token of an OLE object that was previously created by using The property name of the OLE object to return. The returned property value. If specified, it must be a local variable of the appropriate data Arguments for extended stored procedures must be entered in the specific order as section.
Calls a method of an OLE object. The object token of an OLE object that was previously created by using The method name of the OLE object to call. The return value of the method of the OLE object. If specified, it must be a local variable of the Arguments for extended stored procedures must be entered in the specific order as section. If the parameters are entered o
Sets a property of an OLE object to a new value. The object token of an OLE object previously created by The property name of the OLE object to set to a new value. The new value of the property, and must be a value of the appropriate data type. Arguments for extended stored procedures must be entered in the specific order as section.
Stops the server-wide OLE Automation stored procedure execution environment. (success) or a nonzero number (failure) that is the integer value of the HRESULT returned by For more information about HRESULT return codes, see OLE automation return codes and error A single execution environment is shared by all clients that use OLE Automation stored procedures. If one c
Adds or changes a password for a SQL Server login. 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.
2019 (15.x) and later versions SQL database in Microsoft Fabric Manually starts persistent version store (PVS) cleanup process, a key element of accelerated database recovery (ADR). The cleaner removes obsolete row versions from the in-row and off- , and also removes uncommitted changes in PVS from aborted transactions.
Returns primary key information for a single table in the current environment. Syntax for SQL Server, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System Specifies the table for which to return information. Wildcard pattern matching isn't supported. Specifies the table owner of the s
2016 (13.x) and later versions Adds a SQL Server instance as a compute node to a PolyBase group for scale-out computation. The SQL Server instance must have the feature installed. PolyBase enables the integration of non-SQL Server data sources, such as Hadoop and Azure Blob Storage. See also The name of the machine that hosts the SQL Server head node of t
2016 (13.x) and later versions Removes a SQL Server instance from a PolyBase group for scale-out computation. The SQL Server instance must have the feature installed. PolyBase enables the integration of non-SQL Server data sources, such as Hadoop and Azure Blob Storage. See also sp_polybase_join_group Requires CONTROL SERVER permission.
Inserts operations (rows) into the system table for target servers to download The type of operation for the posted operation.
This procedure posts a tracer token into the transaction log at the Publisher and begins the process of tracking latency statistics. when the tracer token is written to the transaction log; when the Log Reader Agent picks it up; and when the Distribution Agent applies it. This stored procedure is executed at the Publisher on the publication database. For more Measure Latency and Validate Connectio
Prepares a parameterized Transact-SQL statement and returns a statement in a tabular data stream (TDS) packet. is a required parameter with an Identifies parameterized statements. is a required OUTPUT parameter that calls for an definition of variables is substituted for parameter markers in the statement. Input a value if the stateme
Prepares and executes a parameterized Transact-SQL statement. is a required parameter with an Identifies parameterized statements. The definition of variables is substituted for parameter markers in the statement. is a required parameter that calls for an value if the statement isn't parameterized.
Prepares and executes a parameterized stored procedure call specified using a remote procedure call (RPC) identifier. The SQL Server-generated prepared handle identifier. is a required parameter with an Defines the stored procedure call using ODBC canonical syntax. Arguments for extended stored procedures must be entered in the specific order as section.
Returns the primary key columns, one row per key column, for the specified remote table.