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)
name -------------------------------------------------- fn_chariswhitespace fn_dblog fn_generateparameterpattern fn_getpersistedservernamecasevariation fn_helpcollations fn_listextendedproperty fn_removeparameterwithargument fn_replbitstringtoint fn_replcomposepublicationsnapshotfolder fn_replgenerateshorterfilenameprefix fn_replgetagentcommandlinefromjobid fn_replgetbinary8lodword fn_replinttobitstring fn_replmakestringliteral fn_replprepadbinary8 fn_replquotename fn_replrotr fn_repltrimleadingzerosinhexstr fn_repluniquename fn_serverid fn_servershareddrives fn_skipparameterargument fn_trace_geteventinfo fn_trace_getfilterinfo fn_trace_getinfo fn_trace_gettable fn_updateparameterwithargument fn_virtualfilestats fn_virtualservernodes
Because they belong to system_function_schema, they can be called across databases using one-part names.
Follow these steps to create your own system function:
Enable updates to the system tables:
sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
Create your function using the CREATE FUNCTION command and be sure to create the function in the master database and owner-qualify it with system_function_schema. Be sure the functions name begins with fn_ and consists only of lowercase letters.
Set allow updates back off, especially on a production system:
sp_configure 'allow updates', 0 RECONFIGURE WITH OVERRIDE
You can create your own system UDFs by following these steps. Note that table-valued system functions must be qualified with a double colon regardless of whether you created the function or it was created during the installation process. See Chapter 10 for some examples of user-defined system functions.