Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

Creating System Functions

Similar to creating system views, you can create functions that reside in master but can be queried from any database without a database name prefix.

Here's how it works: SQL Server creates a number of system UDFs during installation (for example, fn_varbintohexstr(), fn_chariswhitespace(), and so on). The ones that are created with system_function_schema as their owner can be accessed from any database using a one-part name. You can find out which ones these are by running the following query:

USE master
GO
SELECT name
FROM sysobjects
WHERE uid=USER_ID('system_function_schema')
AND   (OBJECTPROPERTY(id, 'IsScalarFunction')=1
      OR OBJECTPROPERTY(id, 'IsTableFunction')=1
      OR OBJECTPROPERTY(id, 'IsInlineFunction')=1)

(Results) ...