Creating System Functions

for RuBoard

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:

  1. Enable updates to the system tables:

     sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE 
  2. 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 .

  3. 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.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net