System Functions
SQL Server built-in system functions organized by category — aggregate, analytic, conversion, cryptographic, date/time, mathematical, metadata, ranking, security, string, and more.
function serves as a wrapper for the query function. The stored procedure is used to generate the script to create the Wrapper functions are not created automatically. There are two things you must do to create wrapper functions: 1. Run the stored procedure to generate the script to create the wrapper. 2. Execute the script to actually create the wrapper function.
2016 (13.x) and later versions Returns a table of 0, one or more rows of the available backup files for the specified database. The backup files returned are backups created by SQL Server managed backup to Microsoft The name of the database. The @database_name is NVARCHAR(512). The table has a unique clustered constraint on (database_guid, backup_start_da
2016 (13.x) and later versions Returns 0, 1 or more rows with SQL Server managed backup to Microsoft Azure configuration settings. Returns 1 row for the specified database, or returns the information for all the databases configured with SQL Server managed backup to Microsoft Azure on the instance.
The type of encryptor used: Certificate or Asymmetric Key. Is set to NULL if there is no encryptor specified.
Returns a description of the built in permissions hierarchy of the server. can only be called on SQL Server and Azure SQL Database, and it returns all permissions regardless of whether they are supported on the current platform. Most permissions apply to all platforms, but some do not. For example server level permissions cannot be granted on SQL Database.
No description available.
function serves as a wrapper for the query function. The stored procedure is used to generate the script to create the Wrapper functions are not created automatically. There are two things you must do to create wrapper functions: 1. Run the stored procedure to generate the script to create the wrapper. 2. Execute the script to actually create the wrapper function.
Returns the column ordinal of the specified column as it appears in the associated with the specified capture instance. Is the name of the capture instance in which the specified column is identified as a captured This function is used to identify the ordinal position of a captured column within the change data capture update mask. It is principally used in conjunct
Returns the maximum log sequence number (LSN) from the start_lsn column in the system table. You can use this function to return the high endpoint of the change data capture timeline for any capture instance. This function returns the maximum LSN in the start_lsn column of the table.
Returns the start_lsn column value for the specified capture instance from the cdc.change_tables system table. This value represents the low endpoint of the validity interval for the capture instance. capture_instance_name Is the name of the capture instance.
'D' - delete operation 'M' - either insert operation or update operation @update_flag_list> A bit flag that is named by appending _uflag to the column name. The flag takes on a non-NULL value only when row_filter_option and __CDC_OPERATION . It is set to 1 if the corresponding column was modified within the query window. function serves as a wrapper for the query function.
Identifies whether the specified update mask indicates that the specified column has been updated in the associated change row. capture_instance Is the name of the capture instance. capture_instance Is the captured column of the specified capture instance to report on. Is the mask identifying updated columns in any associated change row.
Returns the next log sequence number (LSN) in the sequence based upon the specified LSN. The LSN value returned by the function is always greater than the specified value, and no LSN values exist between the two values.
Indicates whether a captured column has been updated by checking whether its ordinal position is set within a provided bitmask. Is the ordinal position in the mask to check. Is the mask identifying updated columns. This function is typically used as part of a change data query to indicate whether a column has changed. In this scenario, the function sys.fn_cdc_get_co
Returns the date and time value from the system table for the specified log sequence number (LSN). You can use this function to systematically map LSN ranges to date ranges in a change table. Is the LSN value to match against. This function can be used to determine the time that a change was committed based upon the value returned in the row of change data.
Returns the log sequence number (LSN) value from the system table for the specified time. You can use this function to systematically map datetime ranges into the LSN-based range needed by the change data cdc.fn_cdc_get_all_changes_<capture_instance> cdc.fn_cdc_get_net_changes_<capture_instance> to return data changes within that range. Used to identify a distinct L
Returns a list of all signable objects and indicates whether an object is signed by a specified certificate or asymmetric key. If the object is signed by the specified certificate or asymmetric key signed, it also returns whether the object's signature is valid.
2016 (13.x) and later versions Returns Azure snapshots associated with the database files. If the specified database isn't found, or if the database files aren't stored in the Microsoft Azure Blob Storage, no rows are returned. Use this system function with the procedure to identify and delete orphaned backup snapshots.
Returns information from an audit file created by a server audit in SQL Server. For more SQL Server Audit (Database Engine) Specifies the directory or path and file name for the audit file set to be read. Type is Passing a path without a file name pattern generates an error.
2016 (13.x) and later versions Returns 1 row for each Extended Event type supported by Smart Admn. Use this function to return or review the current Extended Event settings to identify the type of events that are configurable and the current configurations. This function does not have any arguments. Admin, analytic, and operational channels of the Extende
2016 (13.x) and later versions Returns a table of 0, one or more rows of aggregated count of the errors reported by Extended Events for a specified period of time. The function is used to report health status of services under Smart Admin. Currently SQL Server managed backup to Microsoft Azure is supported under the Smart Admin umbrella. So the errors returned are related to SQL Server
2016 (13.x) and later versions Returns a table of 0, 1, or more rows of parameter and value pairs. Use this stored procedure to review all or a specific configuration settings for Smart Admin. If the parameter has never been configured, the function returns 0 rows.
Returns the text of the SQL statement for the specified SQL handle. Database ID. For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.
Used to determine if the current replica is the preferred backup replica.
2016 (13.x) and later versions Used to map a database in a distributed availability group to the database in the local Is the identifier of the distributed availability group. Is the identifier of the database in a distributed availability group. Returns the following information.
2016 (13.x) and later versions Used to map a replica in a distributed availability group to the local availability group. Is the identifier of the distributed availability group. Is the identifier of a replica in the distributed availability group. Returns the following information. Unique identifier (GUID) of the local availability group.
Used to determine if the current replica is the primary replica. Is the name of the database. is type sysname. Returns data type : 1 if the database on the current instance is the primary replica, otherwise if the database doesn't exist, or isn't part of an availability group.
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 function is used for: SQL database in Microsoft Fabric Microsoft Fabric mirrored databases = Fabric mirroring.
To list the SQL Server collations supported by your server, execute the following query. CREATE DATABASE DECLARE @local_variable sys.fn_helpcollations For Sort Order ID 80, use any of the Window collations with the code page of 1250, and binary order. For example: Albanian_BIN, Croatian_BIN, Czech_BIN, Romanian_BIN, Slovak_BIN, Slovenian_BIN.
2016 (13.x) and later versions Returns the state of the SQL Server managed backup to Microsoft Azure operations on the Use this function to get the current state of SQL Server managed backup to Microsoft Azure. 1 = SQL Server managed backup to Microsoft Azure is active, 0 = SQL Server managed backup Requires SELECT permissions on the function. SQL Server
Returns extended property values of database objects. Valid inputs are default, NULL, or a Is the user or user-defined type.
returns binary data for internal use by the .NET class. Extended Events UI in the SQL Server Management Studio (SSMS) uses this class to read event session data. To view human-readable event data, use one of the following instead of calling sys.fn_xe_file_target_read_file The specific source of event data returned by the function. is interpreted differently dependin
Returns a list of the permissions effectively granted to the principal on a securable. A related The name of the securable. If the securable is the server or a database, this value should be set is a scalar expression of type The name of the class of securable for which permissions are listed. This argument must be one of the followin
'D' - delete operation 'M' - either insert operation or update operation @update_flag_list> A bit flag that is named by appending _uflag to the column name. The flag takes on a non-NULL value only when row_filter_option and __CDC_OPERATION . It is set to 1 if the corresponding column was modified within the query window. function serves as a wrapper for the query function.
2019 (15.x) Is the 8-byte hexadecimal format of a database page resource. is used to convert the 8-byte hexadecimal representation of a database page to a rowset that contains the database ID, file ID and page ID of the page. You can obtain a valid page resource from the sys.dm_exec_requests (Transact-SQL) dynamic management view or the sys.sysprocesses s
Returns the names of shared drives used by the clustered server. If the current server is a clustered server, If the current server instance is not a clustered server, returns a list of shared drives used by this clustered server. These shared drives belong to the same cluster group as the Microsoft SQL Server resource. Further, the SQL Server resource is dependent
2016 (13.x) and later versions SQL database in Microsoft Fabric for a Transact-SQL statement under the given parameterization type (simple or forced). You can refer to queries stored in the Query Store by using their The text of the query in the Query Store that you want the handle of. The parameter type of the query.
Returns a portion of the SSIS log (sysssislog) matching the package_execution_id for the given package. The table contains one row for each logging entry that is generated at run time by packages or their tasks and containers. The local unique identifier for the execution log. The unique identifier of the logging entry.
Returns detailed statistics about the collection set or package, including the number of error rows that are logged by a package data flow task. A data flow task is an Integration Services component that processes data. This data is in relational format, so it has an input and an output dataset consisting of rows. The statistics are calculated from entries in the syscollector_execution_stats view.
This function returns all the events captured for the specified trace. The unique identifier for the primary key in the snapshots.trace_info table in the management The time that the trace started. The time that the trace ended.
Returns information about an event being traced. Is the ID of the trace. , with no default.
Returns information about the filters applied to a specified trace. Returns the following information. For more information about the columns, see sp_trace_setfilter (Transact-SQL) The ID of the column on which the filter is applied. Specifies whether the AND or OR operator is applied. Specifies the type of comparison made: This feature will be removed in a future v
Returns information about a specified trace or all existing traces. Valid inputs are the ID number of a trace, NULL, 0, or DEFAULT. NULL, 0, and DEFAULT are equivalent values in this context. Specify NULL, 0, or DEFAULT to return information for all traces in the instance of SQL Server. 1= Trace options. For more information, see @options in This feature will be r
Returns the content of one or more trace files in tabular form. Specifies the initial trace file to be read. , with no default. Specifies the number of rollover files to be read. This number includes the initial file specified in is specified as reads all rollover files until it reaches the end of the trace. returns a table with all the columns valid for the specifi
Translates the permissions bitmask returned by SQL Trace into a table of permissions names. Is the kind of securable to which the permission is applied. Is a bitmask that is returned in the permissions column. column of a SQL Trace is an integer representation of a bitmask used by SQL Server to calculate effective permissions.
Verifies the validity of the specified plan guide. The returns the first error message that is encountered when the plan guide is applied to its query. An empty rowset is returned when the plan guide is valid. Plan guides can become invalid after changes are made to the physical design of the database. For example, if a plan guide specifies a particular index and that index is subsequently dropped
Returns I/O statistics for database files, including log files. In SQL Server, this information is also , with no default. Specify NULL to return information for all databases in the instance of SQL Server. , with no default. Specify NULL to return information for all files Database timestamp at which the data was taken.
Returns a list of failover clustered instance nodes on which an instance of SQL Server can run. This information is useful in failover clustering environments. If the current server is a clustered server, clustered instance nodes on which this instance of SQL Server has been defined. If the current server instance is not a clustered server, The user must have VIEW S
Reads the event log XEL files created by the Extended Events result set represents an event. Event data is returned in XML format. XEL files can also be read by SQL Server Management Studio. For a walkthrough, see The path to the files to read. When used with files in the local file system, must include the name of an event session log file. The file name can contai