Built-In User-Defined Functions

SQL Server 2000 implements some system functions as built-in user-defined functions. Many of them are not documented; Query Analyzer, Enterprise Manager, Profiler, Replication, and other client applications and system processes use some of these built-in user-defined functions internally. These functions can be used almost as any other user-defined function, but SQL Server itself implements them.

You cannot change the definition of these built-in user-defined functions. In some cases, you cannot see their definition using the sp_help or sp_helptext system stored procedures, and you cannot script them. However, their definition is stored in the syscomments system table as any other user-defined function.

In some cases, built-in user-defined functions return a single scalar value, and all of them are undocumented:

• fn_CharIsWhiteSpace(@nchar) returns 1 if the variable @nchar only contains a space, a tab character, a newline character, or carriage return character; it returns 0 otherwise.

• fn_MSSharedVersion(@len_minorversion) returns the major and minor version number of SQL Server. @len_minorversion specifies how many digits to show for the minor version.

• fn_MsGenSqeScStr(@pstrin) returns the string @pstring, converting single quotes into two single quotes so that you are able to concatenate this string with other strings to execute a dynamic statement.

• fn_IsReplMergeAgent() returns 1 if the present process is executed by the Replication Merge Agent.

• fn_GetPersistedServerNameCaseVariation(@servername) returns the server name of the server specified in @servername with exactly the same case it uses in the sysservers system table, regardless of the case used to call this function.

• fn_ReplGetBinary8LoDWord(@binary8_value) takes the lower four bytes from the @binary8_value binary variable and converts them into an integer value.

• fn_ReplPrepadBinary8(@varbinary8_value) converts the varbinary(8) value stored in @varbinary8_value into a fixed-length binary(8) value with leading zeros.

• fn_ReplMakeStringLiteral(@string) converts the value stored in the @string value into a UNICODE string, including quotes, such as N'Hello', to be used in dynamically constructed statements.

• fn_ReplQuoteName(@string) returns the value stored in @string en closed in square brackets. You can use this function in dynamic execution to select object names that contain spaces or keywords, such as [Order Details].

• fn_GenerateParameterPattern(@parameter) returns a pattern string you can use with the LIKE operator to test for strings containing any case variation of the value stored in @parameter, such as converting 'Hello' into '%[hH][eE][lL][lL][oO]%'. This is useful in case-sensitive servers, databases, or columns.

• fn_UpdateParameterWithArgument, fn_SkipParameterArgument, and fn_RemoveParameterWithArgument are internal functions, and their study is not the purpose of this book.

Listing 10.1 shows some examples of scalar built-in, user-defined functions and the partial result of some of them.

Code Listing 10.1. Using Undocumented Built-In User-Defined Functions

					
USE Northwind
GO

PRINT CHAR(10)
+ 'fn_chariswhitespace(CHAR(9))'
+ CHAR(10)

select fn_chariswhitespace(CHAR(9))
GO

PRINT CHAR(10)
+ 'fn_mssharedversion(1)'
+ CHAR(10)

select master.dbo.fn_mssharedversion(1)
GO

PRINT CHAR(10)
+ 'fn_replgetbinary8lodword(0x0304030401020102)'
+ CHAR(10)
select fn_replgetbinary8lodword(0x0304030401020102)
GO

PRINT CHAR(10)
+ 'fn_replmakestringliteral(@a)'
+ CHAR(10)

declare @a varchar(100)

set @a = 'peter is right'

select fn_replmakestringliteral(@a)
GO

PRINT CHAR(10)
+ 'fn_replprepadbinary8(123456890123)'
+ CHAR(10)

select fn_replprepadbinary8(123456890123)
GO

PRINT CHAR(10)
+ 'fn_replquotename("hello")'
+ CHAR(10)

select fn_replquotename('hello')

fn_chariswhitespace(CHAR(9))


----
1

fn_mssharedversion(1)


----------
80

fn_replgetbinary8lodword(0x0304030401020102)


-----------
16908546

fn_replmakestringliteral(@a)
----------------------------------
N'peter is right'


fn_replprepadbinary8(123456890123) 


------------------
0x0C0000010BA59ABE


fn_replquotename("hello")


----------------------------------
[hello]

In other cases, built-in, user-defined functions return a table. SQL Server documents some of them:

• fn_ListExtendedProperty produces a list of available extended properties for a given database or database objects, such as database users, user-defined data types, tables, views, stored procedures, user-defined functions, default objects, rule objects, columns of tables and views, parameters of stored procedures and user-defined functions, indexes, constraints, and triggers.

• fn_HelpCollations returns a list of the available collations.

• fn_ServerSharedDrives returns a list of the drives shared by a clustered server.

• fn_VirtualServerNodes returns the list of server nodes, defining a virtual server in a clustering server environment.

• fn_VirtualFileStats returns statistical I/O information about any file in a database, including transaction log files.

Listing 10.2 shows some examples of how to use these table-valued, built-in, user-defined functions.

Code Listing 10.2. Table-Valued, Built-In, User-Defined Functions

					
USE Northwind
GO

PRINT CHAR(10)
+ 'fn_helpcollations'
+ CHAR(10)

select *
from ::fn_helpcollations()
WHERE name LIKE 'Cyrillic%'
GO

PRINT CHAR(10)
+ 'fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL)'
+ CHAR(10)

select *
from ::fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL)
GO

PRINT CHAR(10)
+ 'fn_MSFullText()'
+ CHAR(10)

select *
from master.dbo.fn_MSFullText()
GO
fn_helpcollations

name                          description
----------------------------- --------------------------------------
Cyrillic_General_BIN          Cyrillic-General, binary sort
Cyrillic_General_CI_AI        Cyrillic-General, case-insensitive, accent-insensitive,
 kanatype-insensitive, width-insensitive
Cyrillic_General_CI_AI_WS     Cyrillic-General, case-insensitive, accent-insensitive,
 kanatype-insensitive, width-sensitive
Cyrillic_General_CI_AI_KS     Cyrillic-General, case-insensitive, accent-insensitive,
 kanatype-sensitive, width-insensitive
Cyrillic_General_CI_AI_KS_WS  Cyrillic-General, case-insensitive, accent-insensitive,
 kanatype-sensitive, width-sensitive
Cyrillic_General_CI_AS        Cyrillic-General, case-insensitive, accent-sensitive,
 kanatype-insensitive, width-insensitive
Cyrillic_General_CI_AS_WS     Cyrillic-General, case-insensitive, accent-sensitive,
 kanatype-insensitive, width-sensitive
Cyrillic_General_CI_AS_KS     Cyrillic-General, case-insensitive, accent-sensitive,
 kanatype-sensitive, width-insensitive
Cyrillic_General_CI_AS_KS_WS  Cyrillic-General, case-insensitive, accent-sensitive,
 kanatype-sensitive, width-sensitive
Cyrillic_General_CS_AI        Cyrillic-General, case-sensitive, accent-insensitive,
 kanatype-insensitive, width-insensitive
Cyrillic_General_CS_AI_WS     Cyrillic-General, case-sensitive, accent-insensitive,
 kanatype-insensitive, width-sensitive
Cyrillic_General_CS_AI_KS     Cyrillic-General, case-sensitive, accent-insensitive,
 kanatype-sensitive, width-insensitive
Cyrillic_General_CS_AI_KS_WS  Cyrillic-General, case-sensitive, accent-insensitive,
 kanatype-sensitive, width-sensitive
Cyrillic_General_CS_AS        Cyrillic-General, case-sensitive, accent-sensitive,
 kanatype-insensitive, width-insensitive
Cyrillic_General_CS_AS_WS     Cyrillic-General, case-sensitive, accent-sensitive,
 kanatype-insensitive, width-sensitive
Cyrillic_General_CS_AS_KS     Cyrillic-General, case-sensitive, accent-sensitive,
 kanatype-sensitive, width-insensitive
Cyrillic_General_CS_AS_KS_WS  Cyrillic-General, case-sensitive, accent-sensitive,
 kanatype-sensitive, width-sensitive

fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL)

objtype  objname  name  value
-------- -------- ----- -------------------------

fn_MSFullText()

LCID
-----------
2052
1028
1043
2057
1033
1036
1031
1040
1041
1042
0
1053

Other built-in user-defined functions help you to manage user-defined traces from Transact-SQL. You use system stored procedures to define traces and built-in user-defined functions to get information about them.

• fn_trace_getinfo shows information about a specific trace or all the traces defined.

• fn_trace_gettable opens a trace file from disk and shows its information in a table format.

• fn_trace_geteventinfo shows information about the events defined for an active trace.

• fn_tracegetfilterinfo shows the filters applied to a specific trace.

There is a table-valued, built-in, user-defined function—fn_dblog—that is not documented, but it can be very useful in some cases. fn_dblog reads the information contained in the transaction log. This is an alternative to the DBCC LOG statement, undocumented as well, and less flexible than fn_dblog. Listing 10.3 shows an example of this function.

Code Listing 10.3. Use fn_dblog to Look at the Transaction Log

					
USE Northwind
GO

PRINT CHAR(10)
+ 'fn_log(NULL, NULL)'
+ CHAR(10)

select TOP 10
[Current LSN], Operation
from ::fn_dblog(NULL, NULL)
ORDER BY [Current LSN] DESC
GO
fn_log(NULL, NULL)

Current LSN            Operation
---------------------- -------------------------
0000002e:00000010:0006 LOP_COMMIT_XACT
0000002e:00000010:0005 LOP_DELTA_SYSIND
0000002e:00000010:0004 LOP_MODIFY_ROW
0000002e:00000010:0003 LOP_SET_FREE_SPACE
0000002e:00000010:0002 LOP_MODIFY_ROW
0000002e:00000010:0001 LOP_MODIFY_ROW
0000002d:000001e4:0001 LOP_BEGIN_XACT
0000002d:000001c3:0011 LOP_COMMIT_XACT
0000002d:000001c3:0010 LOP_DELTA_SYSIND
0000002d:000001c3:000f LOP_MODIFY_ROW