System Stored Procedures
SQL Server system stored procedures for administrative tasks, configuration, security management, database maintenance, and replication. — Page 4 of 7.
Checks the status of all the subscriptions of every publication and drops subscriptions that are expired. This stored procedure is executed at the Publisher on any database, or at the Distributor on the distribution database for a non-SQL Server Publisher.
Forces immediate download of latest published policies for the whole instance (for every (incremental policy download). (success) or a nonzero number (failure). If there are any ongoing pull requests by the background task or by another user, the request waits until the former task is finished and starts a new pull. – This ensures that the result of calling this proc explicitly always results in a
Forces the FILESTREAM garbage collector (GC) to run, deleting any unneeded FILESTREAM files. A FILESTREAM container can't be removed until all the deleted files within it are cleaned up by the GC. The FILESTREAM GC runs automatically. However, if you need to remove a container before the GC has run, you can use Signifies the name of the database to run the GC on.
Returns logical foreign key information for the current environment. This procedure shows foreign key relationships including disabled foreign keys.
to disk to help with change tracking cleanup. Specifies the current change tracking version.
Specifies the number of rows you want to delete from syscommittab.
This stored procedure allows you to manually clean the side table ( for a table in a database for which change tracking is enabled. If the isn't passed, then this process cleans all side tables for all tables in the database where change The change tracking-enabled table to be manually cleaned up. The backlogs are left for the automa
2016 (13.x) and later versions Flushes to disk the transaction log of the current database, thereby hardening all previously committed delayed durable transactions. If you choose to use delayed transaction durability because of the performance benefits, but you also want to have a guaranteed limit on the amount of data that is lost on server crash or on a regular schedule. For example,
Returns the foreign keys that reference primary keys on the table in the linked server.
Creates and drops a full-text catalog, and starts and stops the indexing action for a catalog. Multiple full-text catalogs can be created for each database.
Specifies whether or not a particular column of a table participates in full-text indexing. A one-part or two-part table name. The table must exist in the current database. The table . The column must be either a character, , and can't be a computed column. This feature will be removed in a future version of SQL Server.
This is supported for backward compatibility only. Full-Text Engine for a given database. All user-created databases in SQL Server are always enabled for full-text indexing. , and can be one of these values. Supported for backward compatibility only.
Returns mappings between document identifiers (DocIds) and full-text key values. The DocId integer that maps to a particular full-text key value in a full- text indexed table. DocId values that satisfy a search condition are passed from the Full-Text Engine to the Database Engine, where they are mapped to full-text key values from the base table being queried. The full-text key column is a unique
Causes the server instance to parse and load the data from the thesaurus file that corresponds to the language whose LCID is specified. This stored procedure is useful after updating a causes recompilation of full-text queries that use the thesaurus of the specified LCID. Integer mapping the locale identifier (LCID) of the language for which you want to load the , w
Returns unprocessed changes, such as pending inserts, updates, and deletes, for a specified table that is using change tracking.
The Semantic Language Statistics database contains language-related statistics that are required for semantic processing of textual content. 1. Checks that the instance of SQL Server is a version that supports semantic processing. 2. Checks that the instance of SQL Server doesn't already have a Semantic Language 3. Checks that the database is a valid Semantic Language Statistics database. 4. Sets
When a Semantic Language Statistics database is unregistered, all the metadata associated 1. Checks that there are no semantic populations in progress for the current instance of SQL 2. Removes all metadata associated with the specified Semantic Language Statistics Install and Configure Semantic Search For information about the Semantic Language Statistics database installed on an instance of SQL
Changes the server properties of full-text search for SQL Server. The property to be changed or reset. , with no default. For a list of properties, their descriptions, and the values that can be set, see the table under the This argument returns the following properties: deprecation status, if applicable.
Marks or unmarks a table for full-text indexing. A one-part or two-part table name. The table must exist in the current database. , with no default, and can be one of these 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.
Generates the ledger digest, which is the hash of the last block in If the last block is open (transactions are grouped to the block but no final block hash has been generated), this stored procedure closes the block and generates the hash. Future transactions will then be assigned to the next block. The results are returned in a column called containing the followi
Creates filters on foreign key tables when a specified table is replicated. This stored procedure is executed at the Publisher on the publication database.
The following example returns all the analytical events logged for the past 30 minutes
Determines whether a Distributor is installed on a server. This stored procedure is executed at the computer where the Distributor is being looked for, on any database.
Returns a public key of the certificate used for authentication on endpoint of specified type with certificate-based trust configured. Supported types of endpoints are Database Mirroring endpoint (also used for Link feature of Azure SQL Managed Instance) and Service Broker Type of endpoint for which certificate's public key is required. Arguments for extended stored
Returns the parameterized form of a query. The results returned mimic the parameterized form of a query that results from using forced parameterization. The query for which the parameterized version is to be generated. , and must be enclosed in single quotation marks and be preceded by the , provided as indicated, to receive the Arguments for extended stored procedu
Used by replication agents to query a distributor to determine whether the original publisher The name of the instance of SQL Server that originally published the database.
Returns a list of all replication agent parameters that can be set in an agent profile for the specified agent type. This stored procedure is executed at the Distributor where the agent is The replication agent for which the parameter is being added.
Places a lock on an application resource. A string specifying a name that identifies the lock resource. If a resource string is longer than The application must ensure that the resource name is unique. The specified name is hashed internally into a value that can be stored in the SQL Server lock manager.
returns a valid token only when the stored procedure is executed inside an active transaction. Otherwise, the Database Engine returns an error message. For example: Here's the result set. is used to enlist a distributed transaction connection inside an open transaction, SQL Server returns the same token. For example: sp_getbindtoken (Transact-SQL) srv_getbindtoken (Extended Stored Procedure API) S
Returns information on the default mapping for the specified data type between SQL Server and a non-SQL Server database management system (DBMS). This stored procedure is executed at the Distributor on any database.
Returns the type of merge delete. This stored procedure is executed at the Publisher on the publication database or at the Subscriber on the subscription database.
Retrieves rows at the Subscriber that have updates pending in the queue. This stored procedure is executed at the Subscriber on the subscription database. , with no default. The table must be a part of a Allows the output to be filtered by the transaction ID. If specified, the transaction ID associated with the queued command is displayed.
Returns the name of the Data Transformation Services (DTS) package used to transform data before they are sent to a Subscriber.
Returns information about a peer-to-peer transactional replication topology. Execute sp_requestpeertopologyinfo to collect information before you execute this procedure. The ID of a topology status request. , with a default of . To obtain an ID, OUTPUT parameter from sp_requestpeertopologyinfo MSpeer_topologyrequest returns a result set that's a single XML column. T
Grants a security principal access to a proxy. The login name to grant access to. must be specified, or the stored The fixed server role to grant access to. must be specified, or the stored
Grants a proxy access to a subsystem. The proxy identification number of the proxy to grant access for. must be specified, but both can't be specified.
Adds a login to the access list of the publication. This stored procedure is executed at the Publisher on the publication database.
Adds a database user to the current database.
Creates a SQL Server login.
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Internal procedure that checks for permissions when enabling change feed publishing. This system stored procedure is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases can be one of the following values: This system stored procedure
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
Retrieves the ID of the default configuration for the agent type passed as parameter. This stored procedure is executed at Distributor on any database. The ID of the default configuration for the type of agent. parameter and returns the ID of the default configuration for the , with no default, and can be one of the following values:
Returns all the parameters of a profile from the procedure is executed at the Distributor where the agent is running, on any database. The ID of the profile from the , which returns all parameters.
Displays the profile of a specified agent. This stored procedure is executed at the Distributor on , and can be one of these values.
Reports information about the alerts defined for the server. information about all alerts is returned. The sorting order to use for producing the results. The identification number of the alert to report information about.
Provides information about the specified classes of jobs, alerts, or operators. Specifies the class about which information is requested. Provides information about a job category. Provides information about an alert category. Provides information about an operator category. The type of category for which information is requested.
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Monitors the current configuration of the change feed. This system stored procedure is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases Change event streaming (preview) introduced in SQL Server 2025 (17.x) and Azure SQL The unique
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Provides the provision or deprovision status and information of table group and table This system stored procedure is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases What is change event streaming (preview)? introduced in SQL Server 2025 (17.x) and Transact-SQL
2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Returns metadata that is used to configure change feed table groups. This system stored procedure is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases What is change event streaming (preview)? introduced in SQL Server 2025 (17.x) and Transact-SQL syntax convention
system table for the supplied job, or all rows if no job is The job identification number for which to return information. must be specified, but both can't be specified. must be specified, but both can't be specified.
Returns a list of all components (filters, word-breakers, and protocol handlers), used for all full- text catalogs in the current database.
Returns the ID, name, root directory, status, and number of full-text indexed tables for the The name of the full-text catalog. this parameter is omitted or has the value , information about all full-text catalogs associated with the current database is returned. This feature will be removed in a future version of SQL Server. Avoid using this feature in new developm
Uses a cursor to return the ID, name, root directory, status, and number of full-text indexed tables for the specified full-text catalog. is an OUTPUT parameter of type . The cursor is a read-only, scrollable, The name of the full-text catalog. this parameter is omitted or is , information about all full-text catalogs associated with the This feature will be removed
Returns the columns designated for full-text indexing. The one- or two-part table name for which full-text index information is requested. is omitted, full-text index column information is retrieved for every full-text The name of the column for which full-text index metadata is requested. information is returned for every full-text indexed column for This feature w
Uses a cursor to return the columns designated for full-text indexing. is an OUTPUT parameter of type . The resulting cursor is a read-only, The one- or two-part table name for which full-text index information is requested. This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applicatio
Returns information for the registered word-breakers, filter, and protocol handlers. also returns a list of identifiers of databases and full-text catalogs that use the specified component. Returns information for all full-text components. Specifies the type of component. must also be specified with the full path to the component DLL, or an error message is returned
Returns a list of tables that are registered for full-text indexing.
Uses a cursor to return a list of tables that are registered for full-text indexing. The cursor is a read-only, scrollable, dynamic cursor.
Returns information about jobs that are used by SQL Server Agent to perform automated The job identification number. To view a specific job, either to return information about all jobs.
Lists information about the runtime state of SQL Server Agent jobs. The job identification number. must be specified, but both can't be specified. must be specified, but both can't be specified. The session ID to report information about.
Provides the number of jobs that a schedule is attached to. The identifier of the schedule to list.
Provides information about the jobs for servers in the multiserver administration domain. The job identification number.
Returns information about the jobs that a particular schedule is attached to. The identifier of the schedule to list information for.
Returns information about the scheduling of jobs used by SQL Server Management Studio to The job identification number. must be specified, but both can't be specified. must be specified, but both can't be specified.
Returns information about the server for a given job. The job identification number for which to return information. must be specified, but both can't be specified. The job name for which to return information. must be specified, but both can't be specified. Whether the last-run execution information is part of the result set. doesn't include last-run information. i
Returns information for the steps in a job used by SQL Server Agent service to perform The job identification number for which to return job information. must be specified, but both can't be specified. must be specified, but both can't be specified. The identification number of the step in the job.
Returns metadata about a specific SQL Server Agent job step log. The job identification number for which to return job step log information. must be specified, but both can't be specified. must be specified, but both can't be specified. The identification number of the step in the job.
This stored procedure returns the job ID of the alert job from the log shipping monitor. This stored procedure returns the SQL Server Agent job ID of the log shipping alert job. If no log shipping alert job exists, it returns an empty result set. database on the monitor server. fixed server role can run this procedure.
Returns a result set containing status and other information for registered primary and secondary databases on a primary, secondary, or monitor server. Identified for informational purposes only. Not supported. Future compatibility is not Collective status of agents for the log shipping database:
Returns information regarding a primary database from the monitor tables.
Returns information regarding a secondary database from the monitor tables.
Retrieves primary database settings.
This stored procedure returns information regarding all the secondary databases for a given The name of the database on the primary server.
This stored procedure retrieves the settings for one or more secondary databases.
This stored procedure retrieves the settings for a given primary database on the secondary 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.
Returns information about the specified maintenance plan. If a plan isn't specified, this stored procedure returns information about all maintenance plans. 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.
Reports a list of alerts for a given operator or a list of operators for a given alert. The type of information to be returned. , which lists the alerts assigned to the supplied operator name, or which lists the operators responsible for the supplied alert name. , and can be one of these values.
Reports information about the operators defined for the server. isn't specified, information about all operators is returned. must be specified, but both can't be specified. The identification number of the operator for which information is requested. must be specified, but both can't be specified.
Returns information about the conflict detection settings for a publication that is involved in a peer-to-peer transactional replication topology.
Lists information for one or more proxies. The proxy identification number of the proxy to list information for.
Returns a list of all granted logins for a publication. This stored procedure is executed at the Publisher on the publication database.
Lists information about schedules. The identifier of the schedule to list.
Facilitates the keying of grid parameters for a spatial index. The qualified or nonqualified name of the table for which the spatial index was specified. Quotation marks are required only if a qualified table is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current The name of the spatial column specified.
Returns the names and values for a specified set of properties about a The result is returned in a table format. You can choose to return a core set of properties or all Spatial index stored procedures - arguments and properties role to access the procedure. Requires READ ACCESS permission on the server and the object. This example returns only the core properties
Returns the name and value for a specified set of properties about a You can choose to return a core set of properties or all properties of the index. Results are returned in an XML fragment that displays the name and value of the properties Spatial index stored procedures - arguments and properties User must be assigned a role to access the procedure. Requires READ
Facilitates the keying of bounding box and grid parameters for a spatial index. The qualified or nonqualified name of the table for which the spatial index is specified. Quotation marks are required only if a qualified table is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current The name of the spatial column specified. Th
Returns the names and values for a specified set of properties about a The result is returned in a table format. You can choose to return a core set of properties or all Spatial index stored procedures - arguments and properties role to access the procedure. Requires READ ACCESS permission on the server and the object. values aren't included in the return set.
Returns the names and values for a specified set of properties about a You can choose to return a core set of properties or all properties of the index. Results are returned in an XML fragment that displays the name and value of the properties Spatial index stored procedures - arguments and properties User must be a member of the permission on the server and Propert
Lists all target servers in the specified group. If no group is specified, SQL Server returns information about all target server groups.
Displays information about an article. This stored procedure is executed at the Publisher on the publication database. For Oracle Publishers, this stored procedure is executed at the Distributor The name of an article in the publication. isn't supplied, information on all articles for the specified publication is returned. Specifies whether the filter clause should
Returns all columns in the underlying table. This stored procedure is executed at the Publisher on the publication database. For Oracle Publishers, this stored procedure is executed at the The name of the publication that contains the article.
Used to get information on the correct custom task names to use when creating a transformation subscription using Microsoft Visual Basic. This stored procedure is executed at the Publisher on the publication database.
Returns a list of all constraint types, their user-defined or system-supplied name, the columns on which they're defined, and the expression that defines the constraint (for Specifies the table for which the constraint information is returned. , with no default. The table specified must be local to the current database. An optional p
Returns information on the defined data type mappings between SQL Server and non-SQL Server database management systems (DBMS). This stored procedure is executed at the The name of the DBMS from which the data types are mapped.
Reports information about a specified database or all databases.
Returns a list of the fixed database roles.
Reports information about SQL Server backup devices.
Returns properties of the Publishers using a Distributor. This stored procedure is executed at the Distributor on any database. Specifies the Publisher for which properties are returned. Identified for informational purposes only. Not supported. Future compatibility is not Distribution database for the specified Publisher.
Returns properties of the specified distribution database. This stored procedure is executed at the Distributor on the distribution database. The database name for which properties are returned. for all databases associated with the Distributor, and on which the user has permissions.
Lists information about the Distributor, distribution database, working directory, and SQL Server Agent user account. This stored procedure is executed at the Publisher on the publication database or any database. value that returns a result set.