Find Out What They’re Doing with fn_get_sql

Don’t miss the movie that accompanies this article!

Not only did SQL Server 2000 introduce the concept of
user-defined functions (UDF) but also Microsoft’s development team created
several such functions as part of the database product. In addition to many
undocumented system UDFs, used internally by SQL Server, the product release
has nine documented UDFs that are part of a special schema in the master
database named system_function_schema. These system UDFs provide information
about the internal workings of SQL Server that isn’t available elsewhere. SQL
Server 2000 Service Pack 3 (SP3) includes a new system user-defined function, fn_get_sql.
The function was included in an earlier hotfix but SP3 is the best way to get
it. (See Microsoft Knowledge Base article 325607)

fn_get_sql makes it easier for DBAs, programmers, and
diagnostic tools to retrieve the text of the SQL being executed by active SQL
processes. This is a technique commonly used when diagnosing a deadlock or
other blocking problem. Diagnostic tools that monitor activity inside the
database engine can also use it.

The first step before using fn_get_sql is to get the
documentation. There is an updated Books Online for SP3 that includes the new
documentation for fn_get_sql. You can get it without installing SP3 at this
link: http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

If you’re not installing SP3, be careful as you install the
updated documentation. If you let it, the installer will overwrite your
existing Books Online (BOL). However, if you make the right selection, it can
be installed side-by-side with the old BOL, which you will want to keep around.
You will know that you have the updated documentation because the title bar of
the BOL window is updated as shown in Figure 1.

Figure 1 Updated BOL Title Bar

Prior to SP3, the way to get the SQL being used by a SQL
process was from the DBCC INPUTBUFFER command. DBCC INPUTBUFFER takes a
SPID as its argument. SPIDs are integers that uniquely identify a database
connection. SPIDs below 53 are generally used by system connections. Fifty-three
and over are used for user connections. A connection can get its own SPID with
the @@SPID built-in function.

When invoked, DBCC INPUTBUFFER returns a rowset
consisting of these columns:

Column Name

Data Type

Description

EventType

nvarchar(30)

Language Event
No Event

RPC

Parameters

int

0=text 1-n=parameters

EventInfo

nvarchar(255)

For an RPC (stored procedure) it contains the procedure
name. For a Language Event it contains the text of the SQL being executed.

This query gives you the idea of how DBCC INPUTBUFFER works
by showing you its own text:


DBCC INPUTBUFFER (@@SPID)
GO
(Results)
EventType Parameters EventInfo
————– ———- —————————
Language Event 0 DBCC INPUTBUFFER (@@SPID)
(End of results)

Andrew Novick
Andrew Novick
Andrew Novick develops applications as consultant, project manager, trainer, author, and Principal of Novick Software. His firm specializes in implementing solutions using the Microsoft tool set: SQL Server, ASP, ASP.Net, Visual Basic and VB.Net. 2003 marks his 32nd year of computer programming, starting in High School with a PDP-8 and moving on a degree in Computer Science, an MBA, and then programming mainframes, minicomputers and for the last 16 years PCs. In addition to writing articles for Database Journal, Andrew is author of the book SQL Server User Defined functions, which will be published by Wordware in the fall of 2003. He co-authored SQL Server 2000 XML Distilled, published by Curlingstone in October of 2002. He also writes the free T-SQL User-Defined Function of the Week newsletter. When not programming he enjoys coaching Little League baseball, woodworking, mowing the lawn, and the occasional movie with his wife.

Latest Articles