System-Wide Table Valued Functions

In addition to the built-in scalar functions, SQL Server 2000 now provides a set of system-wide table-valued functions that can be invoked from any database. Usually, when you invoke a user -defined table-valued function that is not local to the current database context, you have to fully qualify the function name with the database name. This is not required for system-wide table-valued functions. A special syntax is used to invoke system-wide table-valued funcions. You must precede the function name with two colons (::), as shown in the following example:

 SELECT * FROM ::fn_virtualfilestats(5, 1)  go DbId FileId TimeStamp NumberReads NumberWrites BytesRead BytesWritten IoStallMS ---- ------ --------- ----------- ------------ --------- ------------ ---------    5      1  88725180          36            2    294912        16384       460 

The fn_virtualfilestats function returns IO statistics for a database file. It is passed the database ID and a file ID as parameters. The previous example returned the IO stats for the datafile of the pubs database.

SQL Server 2000 provides a number of documented and undocumented table-valued functions. The following is a list of the documented functions:

• fn_helpcollations() ” Lists all collations supported by SQL Server 2000.

• fn_listextendedproperty( propertyname,level0objecttype, level0objectname, level1objecttype, level1objectname, level2objecttype, level2objectname ) ” Lists extended property values for a database or objects stored in a database.

• fn_servershareddrives() ” Returns the names of shared drives used by a clustered server.

• fn_trace_geteventinfo( traceID ) ” Returns information about the events being traced for the trace specified.

• fn_trace_getfilterinfo( traceID ) ” Returns information about the filters applied to the trace specified.

• fn_trace_getinfo( traceID ) ” Returns information about the specified trace.

• fn_trace_gettable( filename , numfiles ) ” Returns trace file information from the specified file in table format.

• fn_virtualfilestats( dbid , fileid ) ” Returns file I/O information for the file of the specified database.

• fn_virtualservernodes() ” Returns a list of nodes on which a virtual server can run. This information is useful in failover clustering environments.

The trace- related functions perform actions that previously could only be performed via extended stored procedures. These functions will be discussed in more detail in Chapter 7, "Using the SQL Server Profiler."

The majority of the undocumented system-wide table-valued functions are used within SQL Server replication and are not intended for end-user execution.