Updated: June 10, 2016
Returns the text of the SQL statement for the specified SQL handle.
sys.fn_get_sql ( SqlHandle )
Is the handle value. SqlHandle is varbinary(64) with no default.
|Column name||Data type||Description|
|dbid||smallint||Database ID. For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.|
|objectid||int||ID of the database object. Is NULL for ad hoc SQL statements.|
|number||smallint||Indicates the number of the group, if the procedures are grouped.|
0 = Entries are not procedures.
NULL = Ad hoc SQL statements.
|encrypted||bit||Indicates whether the object is encrypted.|
0 = Not encrypted
1 = Encrypted
|text||text||Is the text of the SQL statement. Is NULL for encrypted objects.|
You can obtain a valid SQL handle from the sql_handle column of the sys.dm_exec_requests (Transact-SQL) dynamic management view.
If you pass a handle that no longer exists in cache, fn_get_sql returns an empty result set. If you pass a handle that is not valid, the batch stops, and an error message is returned.
The SQL Server Database Engine cannot cache some Transact-SQL statements, such as bulk copy statements and statements with string literals that are larger than 8 KB. Handles to those statements cannot be retrieved by using fn_get_sql.
The text column of the result set is filtered for text that may contain passwords. For more information about security related stored procedures that are not monitored, see Filter a Trace.
The fn_get_sql function returns information that is similar to the DBCC INPUTBUFFER command. The following are examples of when the fn_get_sql function can be used because DBCC INPUTBUFFER cannot be:
When events have more than 255 characters.
When you have to return the highest current nesting level of a stored procedure. For example, there are two stored procedures that are named sp_1 and sp_2. If sp_1 calls sp_2 and you obtain the handle from the sys.dm_exec_requests dynamic management view while sp_2 is running, the fn_get_sql function returns information about sp_2. Additionally, the fn_get_sql function returns the complete text of the stored procedure at the highest current nesting level.
The user needs VIEW SERVER STATE permission on the server.
Database administrators can use the fn_get_sql function, as shown in the following example, to help diagnose problem processes. After an administrator identifies a problem session ID, the administrator can retrieve the SQL handle for that session, call fn_get_sql with the handle, and then use the start and end offsets to determine the SQL text of the problem session ID.
DECLARE @Handle varbinary(64); SELECT @Handle = sql_handle FROM sys.dm_exec_requests WHERE session_id = 52 and request_id = 0; SELECT * FROM sys.fn_get_sql(@Handle); GO