لطفا منتظر باشید ...
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)
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.
