Secrets are always intriguing, and programming back doors—secret entrances into application code—are no different. I'm covering T-SQL back doors lately for several reasons. First, programmers frequently use undocumented features, which you need to be familiar with in case you need to maintain code written by others. Second, some of the undocumented features are useful, and SQL Server might fully support them someday. And third, of course, the subject is exciting. In "T-SQL Back Doors" (February 2004, InstantDoc ID 41044), I started the discussion by looking at back doors related to stored procedures. This month, I continue the subject by delving into back doors involving user-defined functions (UDFs). I must stress again that these undocumented features are unsupported, so I don't recommend using them. Microsoft might change the functionality of undocumented features in future releases of SQL Server or even in a service pack. Let's start with the first UDF back door—creating your own special UDFs.
Special UDFs
Stored procedures whose names are prefixed with sp_ have a supported special functionality: They exist only in the master database, but you can invoke them from any database without prefixing their names with master. Microsoft introduced UDFs in SQL Server 2000 but didn't provide UDF support for this special functionality. Programmers certainly need such functionality, especially for functions that don't access objects in a specific database but instead implement some generic algorithm. One example of a useful generic scalar UDF (a UDF that returns a single value) is a function that removes all occurrences of a certain pattern from a character string. An example of a useful generic table-valued UDF (a UDF that returns a table) is a function that returns a table containing a requested number of consecutive integers. (This UDF is an alternative for a permanent auxiliary table of numbers, which I've used in many previous articles.)
The SQL Server installation program creates several UDFs that feature the special functionality. For example, the scalar function fn_serverid() returns from the sysservers system table the ID for a server name you provide to the function as an argument:
SELECT fn_serverid(server_name)
Besides being able to invoke this system-supplied function from any database without prefixing its name with master, you don't even need to specify the owner, unlike with regular scalar UDFs. For example, you use the following code to execute the table-valued function fn_helpcollations(), which returns a table containing all collation names and their descriptions:
SELECT * FROM ::fn_helpcollations()
Note that to invoke a special table-valued UDF, you must prefix its name with double colons.
Prev. page
[1]
2
3
next page 
