fn_get_sql (Transact-SQL)
Returns the text of the SQL statement for the specified SQL handle.
Transact-SQL Syntax Conventions
| 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. |
| 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 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 Microsoft SQL Server 2005 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_id, 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 ::fn_get_sql(@Handle); GO
Reference
DBCC INPUTBUFFER (Transact-SQL)sys.sysprocesses (Transact-SQL)
