SQL Server T-SQL User-Defined Function of the Week

Retrieving the Text of a System UDF

Volume 1 Number 17       March 11, 2003

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

A few weeks ago I answered a posting on DevelopersDex from a
programmer who wanted to know why the text of system user-defined
functions such as fn_trace_gettable was unavailable to SQL-DMO.  
The following is a revised version of my answer to that question 
with this week's UDF.

System UDFs get special treatment by SQL Server and you find that
in addition to SQL-DMO not being able to get the text of a
function, sp_helptext also doesn't work. That is because the 
Object_ID() built-in function returns NULL for functions owned 
by system_function_schema. It's not really a bug in SQL-DMO, 
as Microsoft likes to say it's "By Design". I don't disagree with
Microsoft that you don't need to see this code but I understand 
that it's interesting to take a look.

To illustrate try the script:

/-------- Copy from below this line ----------------------------\
use master
go

sp_helptext fn_trace_gettable
go
\-------- Stop copying above this line -------------------------/
(Results)
Server: Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 53
The object 'fn_trace_gettable' does not exist in database 'master'.
(End of results)

We can see that Object_ID() returns NULL for functions owned by
system_function_schema functions with this query:

/-------- Copy from below this line ----------------------------\
SELECT object_id('fn_trace_gettable') [ID of fn_trace_gettable]
     , object_id('dbo.fn_MSFullText') [ID of fn_MSFullText]
     , object_id('fn_hex') [ID of fn_hex]
GO
\-------- Stop copying above this line -------------------------/
(Results)
ID of fn_trace_gettable ID of fn_MSFullText ID of fn_hex
----------------------- ------------------- ------------
NULL                             1371151930 NULL

fn_trace_gettable is a documented system function. fn_MSFullText
is a function in master that's owned by dbo, not by
system_function_schema. fn_hex is an undocumented system UDF 
owned by system_function_schema.

However, the functions owned by system_function_schema are in
master..sysobjects, so it's possible to find their ID. Just use 
the query:

/-------- Copy from below this line ----------------------------\
select [ID], [name]
    from master..sysobjects 
    where name = 'fn_trace_gettable'
go
\-------- Stop copying above this line -------------------------/
(Results)
ID          name
----------- -----------------------------------
1749581271 fn_trace_gettable
(End of results)

I've pulled this information together into a UDF, 
udf_SQL_SystemUDFText that retrieves the text of system UDFS. 
Here's the CREATE FUNCTION script:

/------------- Copy From Below this line -----------------------\
CREATE FUNCTION dbo.udf_SQL_SystemUDFText (

  @FunctionName sysname -- name of the function
  )
RETURNS @Script TABLE(-- the text of the function
                         [text] nvarchar(4000) -- a line of text
                         )
/*
* Returns the text of a system UDF. This it's intended for
* retrieving the text of documented and undocumented system
* functions. Text of system UDF is stored in master and so
* this function works with tables from master.
*
* When using this function in SQL Query Analyzer, be sure to set
* the "Maximum Characters Per Column" field to 4000 or greater.
* You'll find it in the menu item Tools -> Options on the
* Results tab. If you don't your results will be truncated
* when the function contains more than 4000 characters.
*
* Common Usage:
select [text] from udf_SQL_SystemUDFText('fn_trace_gettable')
*
* © Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published in the T-SQL UDF of the Week Newsletter Vol 1 #17
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
***************************************************************/

AS BEGIN

    DECLARE @ObjectID int -- the object id

    SELECT @ObjectID = id
        FROM master..sysobjects
        WHERE name = @FunctionName

    INSERT INTO @Script
        SELECT [text]
            FROM master..syscomments
            WHERE id = @ObjectID

    RETURN

END
GO

-- The GRANT Statement has been omitted from this script.
-- I don't have any reason to extend this functionality to the
-- PUBLIC group.
-- GO
\------------ Stop copying above this line --------------------/

To retrieve the text of fn_trace_gettable, run the query:

/-------- Copy from below this line ----------------------------\
select [text] from udf_SQL_SystemUDFText('fn_trace_gettable')
go
\------------ Stop copying above this line ---------------------/
(Results)
text
-----------------------------------------------------------------
create function system_function_schema.fn_trace_gettable
(@filename nvarchar(256),
  @numfiles int = -1)

returns table as
return select * from OpenRowset(TrcTable, @filename, @numfiles)

(End of results)

The syntax in the OpenRowset clause inside fn_trace_gettable 
only works when the function is a system function. It's not 
something that can be used from User databases.

There's another way to see the text of the system function.  SQL
Server 2000's setup leaves the scripts used to create the 
system functions on your disk in the subdirectory \MSSQL\Install
in directory where you installed SQL Server.  The two files
with the CREATE FUNCTION scripts are procsyst.sql and 
replsys.sql.  

The create function scripts for the system functions are a pretty
academic topic.  They give you an idea of how SQL Server does 
what it does under the hood.

+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+