System Stored Procedures
SQL Server system stored procedures for administrative tasks, configuration, security management, database maintenance, and replication. — Page 3 of 7.
Sets certain database behaviors to be compatible with the specified version of SQL Server.
Displays the permissions of a fixed database role. information in SQL Server 2000 (8.x). The output doesn't reflect the changes to the permissions hierarchy that were implemented in SQL Server 2005 (9.x). For more information, see , which shows a list of fixed database roles and its corresponding permissions.
Creates a database mirroring monitor job that periodically updates the mirroring status for every mirrored database on the server instance. Specifies the interval between updates in minutes. , with a default of This value can be from 1 to 120 minutes. If update period is set too low, the response time might increase for clients.
Adds or changes warning threshold for a specified mirroring performance metric. Specifies the database for which to add or change the specified warning threshold. An integer value that identifies the warning to be added or changed.
Changes the value of a database mirroring monitoring parameter. Specifies the identifier of the parameter to be changed. Currently, only the following parameter is available: The number of minutes between updates to the database mirroring status table. The default Specifies the new value for the parameter that is being changed.
Drops the warning for a specified performance metric, by setting the threshold to Specifies the database for which to drop the specified warning threshold. An integer value that identifies the warning to be dropped. Specifies the number of minutes worth of transactions that can accumulate in the send queue before a warning is generated on the principal server instan
Stops and deletes the mirroring monitor job for all the databases on the server instance. fixed server role, or execute permission directly on this The following example drops database mirroring monitoring on all of the mirrored databases
Returns information about warning thresholds on one or all of several key database mirroring An integer value that identifies the warning to be returned. If this argument is omitted, all the warnings are returned, but not the retention period. To return a specific warning, specify one of the following values: Specifies the number of minutes worth of transactions t
Returns the current update period. Returns the current update period, that is, the number of minutes between updates of database mirroring status table. This value ranges from 1 to 120 minutes. fixed server role, or execute permission directly on this The following example returns the current update period.
Returns status rows for a monitored database from the status table in which database mirroring monitoring history is stored, and allows you to choose whether the procedure obtains the latest status beforehand. Specifies the database for which to return mirroring status. Specifies the quantity of rows returned. , and can be one of these values.
Updates the database mirroring monitor status table by inserting a new table row for each mirrored database, and truncates rows older than the current retention period. The default retention period is seven days (168 hours). When evaluates the performance metrics.
Removes a database and all files associated with that database.
Changes the default database for a SQL Server login. Server login or a Windows user or group. If a login for the Windows user or group doesn't exist in SQL Server, it's automatically added.
Changes the default language of for a SQL Server login. Server login, or a Windows user or group. The default language of the login. must be a valid language on the server. If server default language (defined by the Changing the server default language doesn't change the default language for existing logins. This feature will be removed in a future version of SQL Se
Removing an alert also removes any notifications associated with the alert. permissions on this procedure, but these permissions might be overridden during a SQL Server upgrade.
2016 (13.x) and later versions Deletes all snapshots and the backup file that comprise a snapshot backup set from the specified database. This system stored procedure is the only recommended method for managing snapshot backup sets. For more information, see The URL of the backup to be deleted, which deletes all snapshots comprising the specified backup s
2016 (13.x) and later versions Deletes a specified backup snapshot from the specified database. Use this system stored procedure in conjunction with the identify and delete orphaned backup snapshots. For more information, see Backups for Database Files in Azure The name of the database containing the snapshot to be deleted, provided as a Unicode string. T
Reduces the size of the backup and restore history tables by deleting the entries for backup sets older than the specified date. More rows are added to the backup and restore history tables after each backup or restore operation is performed; therefore, we recommend that you The oldest date retained in the backup and restore history tables.
Removes the specified category of jobs, alerts, or operators from the current server. , with no default, and must be one of these The name of the category to be removed.
Deletes information about the specified database from the backup and restore history tables. Specifies the name of the database involved in backup and restore operations. , with no default. must be run from the This stored procedure affects the following tables:
Deletes a job from the SQL Server Agent service. The identification number of the job to be deleted. must be specified; both can't be specified.
Deletes a schedule for a job in the SQL Server Agent service. is provided for backward compatibility only. 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. Job schedules can now be managed independently of jobs. To remove a schedule from a job, doesn't support schedules
Removes the specified target server. The identification number of the job from which the specified target server will be removed. must be specified, but both can't be specified.
Removes a job step from a job in the SQL Server Agent service. The identification number of the job from which the step will be removed. must be specified; both can't be specified.
Removes all SQL Server Agent job step logs that are specified with the arguments. Use this stored procedure to maintain the The job identification number for the job that contains the job step log to be removed. must be specified, but both can't be specified. must be specified, but both can't be specified.
Removes an alert job from the log shipping monitor server if the job exists and there are no more primary or secondary databases to be monitored. fixed server role can run this procedure.
This stored procedure removes log shipping of primary database including backup job, local and remote history. Only use this stored procedure after you remove the secondary databases The name of the log shipping primary database. Identified for informational purposes only. Not supported.
Removes the entry for a secondary database on the primary server.
fixed server role can run this procedure. About log shipping (SQL Server) System stored procedures (Transact-SQL)
removes the information about the specified primary server from the secondary server, and removes the copy job and restore job from the The name of the primary instance of the SQL Server Database Engine in the log shipping The name of the database on the primary server.
Deletes the specified maintenance plan. 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. Specifies the ID of the maintenance plan to be deleted. must be run from the This stored procedure is used with database maintenance plans.
Disassociates the specified maintenance plan from the specified database. 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. Specifies the maintenance plan ID. Specifies the database name to be deleted from the maintenance plan. This stored
Disassociates the specified maintenance plan from the specified job. 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. Specifies the ID of the maintenance plan. Specifies the ID of the job with which the maintenance plan is associated. Thi
Removes a SQL Server Agent notification definition for a specific alert and operator.
Removes an operator.
Removes the specified proxy. The proxy identification number of the proxy to remove. , with a default of The name of the proxy to remove.
The schedule identification number of the schedule to delete. must be specified, but both can't be specified.
Removes the specified server from the list of available target servers.
Deletes the specified target server group.
Removes a target server from a target server group.
Deletes rows from a conflict table or the table. This stored procedure is executed at the computer where the conflict table is stored, in any database.
Deletes history related to a publication status request, which includes the request history ) as well as the response history ( executed on the publication database at a Publisher participating in a Peer-to-Peer replication topology. For more information, see Peer-to-Peer - Transactional Replication Name of the publication for which the status request was made. Spec
Removes tracer token records from the This stored procedure is executed at the Publisher on the publication database or at the Distributor on the distribution database.
Prevents a Windows user or Windows group from connecting to an instance of SQL Server.
Displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure. References to objects outside the current database aren't reported. This feature will be removed in a future version of SQL Server. Avoid using this feature in new developmen
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
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
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
Position of this column in ORDER BY list Returns NULL if the column does not appear in the ORDER BY list or if the ORDER BY list cannot be uniquely Length of the ORDER BY list. Returns NULL if there is no ORDER BY list or if the ORDER BY list cannot be uniquely determined. Note that this value will be the same for all rows returned by sp_describe_first_result_set. If the ordinal_in_order_by_list i
2016 (13.x) and later Analyzes the specified Transact-SQL statement and its parameters, to determine which parameters correspond to database columns that are protected by using the Always Encrypted feature. Returns encryption metadata for the parameters that correspond to encrypted columns. One or more Transact-SQL statements.
For internal use. Not nullable. always returns status of zero. The most common use is when an application is given a Transact-SQL statement that might contain parameters and must process them in some way. An example is a user interface (such ) where the user provides a query with ODBC parameter syntax. The application must dynamically discover the number of parameters and prompt the user for Anoth
Detaches a database that is currently not in use from a server instance and, optionally, runs on all tables before detaching. For a replicated database to be detached, it must be unpublished. For more information, see section later in this article.
Removes an association between a schedule and a job. The job identification number of the job to remove the schedule from. must be specified, but both can't be specified.
Disables change event streaming at the database level for the current database context, including all associated tables. What is change event streaming (preview)? SQL Server 2025 (17.x) and Azure SQL Database. server role membership can execute this procedure. What is change event streaming (preview)? Configure change event streaming (preview) Change event streaming
Drops one or all parameters from a profile in the table. This stored procedure is executed at the Distributor where the agent is running, on any database. The ID of the profile for which a parameter is to be dropped. , with no default.
table. This stored procedure is executed at the The ID of the profile to be dropped. is used in all types of replication.
Drops a stream event group for the Server 2025 (17.x) and Azure SQL Database. Specifies the name of the event stream group you want to drop. , with no default, and can't be server role membership can execute this procedure. Change event streaming is currently in Azure SQL Database (preview feature database scoped configuration not required).
2017 (14.x) and later Drops an assembly from the list of trusted assemblies on the server. The SHA2_512 hash value of the assembly to drop from the list of trusted assemblies for the server. Trusted assemblies might load when CLR strict security is enabled, even if the assembly is unsigned or the database isn't marked as trustworthy. This procedure remove
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.
Removes an application role from the current database. The application role to remove. , with no default. in the current database. can only be used to remove application roles. If a role owns any securables, the role can't be dropped. Before dropping an application role that owns securables, you must first transfer ownership of the securables, or drop them.
Drops an article from a snapshot or transactional publication. An article can't be removed if one or more subscriptions to it exist. This stored procedure is executed at the Publisher on the The name of the publication that contains the article to be dropped.
Drops a database device or backup device from a SQL Server Database Engine instance, The logical name of the database device or backup device as listed in Specifies whether the physical backup device file should be deleted. , the physical backup device disk file is deleted.
Drops a distribution Publisher. This stored procedure is executed at the Distributor on any checks that the Publisher has uninstalled the server as , replication verifies that the remote Publisher has uninstalled the local server as the Distributor. If the Publisher is local, replication verifies that there are no publication or Using a custom port for the SQL Serve
Drops a distribution database. Drops the physical files used by the database if they aren't used by another database. This stored procedure is executed at the Distributor on any database. The database to drop. , with no default. Specifies whether this node was previously part of an availability group for the distribution @former_ag_seconda
Uninstalls the Distributor. This stored procedure is executed at the Distributor on any database except the distribution database. Indicates whether to check for dependent objects before dropping the Distributor. checks to make sure that all publishing and distribution objects were dropped, in addition to the Distributor.
Removes a filtered data snapshot job for a publication with parameterized row filters. This stored procedure is executed at the Publisher on the publication database. When the job is deleted, all of the related data is deleted from the The name of the publication from which the filtered data snapshot job is being removed.
Drops an extended stored procedure.
Removes an existing mapping between a login on the local server running SQL Server, and a login on the linked server.
Removes a SQL Server login, which prevents access to an instance of SQL Server under that The login to be removed. , with no default. exist in SQL Server. 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.
Removes an alternate Publisher from a merge publication. This stored procedure is executed at the Subscriber on the subscription database.
Removes an article from a merge publication. This stored procedure is executed at the Publisher on the publication database.
drops all the merge filter columns defined on the merge filter that is to be dropped. This stored procedure is executed at the Publisher on the The name of the filter to be dropped.
Removes a partition for a parameterized row filter from a publication. This stored procedure is executed at the Publisher on the publication database. This stored procedure also removes the corresponding snapshot job and snapshot files for the partition. function at the Subscriber used to define the partition. function at the Subscrib
Drops a merge publication and its associated Snapshot Agent. All subscriptions must be dropped before dropping a merge publication. The articles in the publication are dropped automatically. This stored procedure is executed at the Publisher on the publication database.
Drops a merge pull subscription. This stored procedure is executed at the Subscriber on the to remove subscriptions to all publications.
Drops a subscription to a merge publication and its associated Merge Agent. This stored procedure is executed at the Publisher on the publication database. already exist and conform to the rules for The name of the subscription database.
Drops a specified user-defined error message from an instance of the SQL Server Database Engine. User-defined messages can be viewed using the user-defined message that's a message number greater than The language of the message to drop.
Drops a publication and its associated Snapshot Agent. All subscriptions must be dropped before dropping a publication. The articles in the publication are dropped automatically. This stored procedure is executed at the Publisher on the publication database.
Drops a subscription at the current database of the Subscriber. This stored procedure is executed at the Subscriber on the pull subscription database. dropped at all the Publishers.
Removes a remote login mapped to a local login used to execute remote stored procedures against the local server running SQL Server.
Removes a database role from the current database.
Removes a security account from a SQL Server role in the current database. Syntax for SQL Server and Azure SQL Edge. Syntax for Azure Synapse Analytics and Analytics Platform System (PDW). 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 t
Removes a server from the list of known remote and linked servers on the local instance of SQL The server to be removed. , with no default. Indicates that related remote and linked server logins for must also be removed if , with a default of An error is returned if you run on a server with associated remote and linked server login entries
Removes a SQL Server login, a Windows user, or Windows group, from a fixed server role.
Removes the Subscriber designation from a registered server. This stored procedure is executed at the Publisher on the publication database.
Drops subscriptions to a particular article, publication, or set of subscriptions on the Publisher. This stored procedure is executed at the Publisher on the publication database.
Deletes an alias data type from The name of an alias data type that you own. , with no default. alias data type can't be dropped if tables or other database objects reference it. An alias data type can't be dropped if the alias data type is used within a table definition or if a rule or default is bound to it.
Removes a database user from the current database. provides compatibility with earlier versions of SQL Server.
Returns ODBC or OLE DB data source information from the Distributor associated with the current server. This stored procedure is executed at the Distributor on any database.
Enables change event streaming at the database level for the current database context. change event streaming (preview)? was introduced in SQL Server 2025 (17.x) and Azure SQL server role membership can execute this procedure. Change event streaming is currently in Azure SQL Database (preview feature database scoped configuration not required).
2019 (15.x) and later - Windows only Sends columns encryption keys, defined in the database, to the server-side secure enclave used Always Encrypted with secure enclaves only sends only the keys that are enclave-enabled and encrypt columns that use randomized encryption and have indexes. For a regular user query, a client driver provides the enclave with the keys needed for computations
Lists associations between security principals and proxies.
Lists permissions for SQL Server Agent proxies to access subsystems. The identification number of the proxy to list information for.
Returns a list of all available business logic handlers and custom resolvers registered at the Distributor. This stored procedure is executed at the Publisher on any database.
Returns a list of all defined ODBC and OLE DB data source names for a server running under a specific Windows user account. This stored procedure is executed at the Publisher on any Description of the data source.
Returns a list of all pending schema changes. This stored procedure can be used with , which enables 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. The lowest number schema change to include in the result set.
Returns the current size of the requested object and estimates the object size for the requested compression state. Compression can be evaluated for whole tables or parts of tables. This includes heaps, clustered indexes, nonclustered indexes, columnstore indexes, indexed views, and table and index partitions.
Executes a prepared Transact-SQL statement using a specified handle and optional parameter in a tabular data stream (TDS) packet. Signifies the use of extra parameters.
2016 (13.x) and later stored procedure executes a script provided as an input argument to the procedure, and is used with For Machine Learning Services, are supported languages. For Language Extensions, Java is supported but must be defined with , you must first install Machine Learning Services or Language Extensions. For more information, see Install SQL Server Machine Learning Servic
A placeholder for the values of extra parameters. Values can only be constants or variables. Values can't be more complex expressions such as functions, or expressions built by using (success) or non-zero (failure). Returns the result sets from all the SQL statements built into the SQL string. parameters must be entered in the specific order as described in the section earlier in this article. If