fn_get_sql (Transact-SQL)
Returns the text of the SQL statement for the specified SQL handle.
 Important |
|---|
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_exec_sql_text instead. For more information, see sys.dm_exec_sql_text (Transact-SQL). |
Column name | Data type | Description |
|---|---|---|
dbid | smallint | Database ID. Is NULL for ad hoc SQL statements. |
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 Filtering 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.
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
