fn_get_sql (Transact-SQL)

Returns the text of the SQL statement for the specified SQL handle.

Topic link iconTransact-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.

Syntax

fn_get_sql ( SqlHandle )

Arguments

  • SqlHandle
    Is the handle value. SqlHandle is varbinary(64) with no default.

Tables Returned

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.

Remarks

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.

Permissions

The user needs VIEW SERVER STATE permission on the server.

Examples

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

See Also

Reference

DBCC INPUTBUFFER (Transact-SQL)
sys.sysprocesses (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance