Creating a SQL System UDF (User Defined Functions)

A system UDF is a very nice thing to have. For whatever reason, a lot of us seem to write system stored procs, to add to the arsenal of tools available to us, but system udf’s – well I haven’t seen too many floating around. However it is nice to have a function available which resideds in the master database, can be invoked from any database and runs in the context of the database from which it is invoked. So lets see how we do this.

If we examine the few system UDF’s provided to us, there are a few things we notice, for one the owner is “system_function_schema”. No – you don’t see this user through Enterprise manager but believe me its there, just look at the sysusers table in the master database and you will find it. Another thing we can see, is all system udf’s start with ‘fn_’.

So lets create a system UDF

Exec sp_configure 'Allow Updates',1
-- Allows for updating the system tables
Reconfigure with Override
-- specifies that the change will be immediate and
-- that a server restart is not needed

Use master
-- System UDF has to be in the master database

CREATE FUNCTION system_function_schema.fn_listtablesindatabase()
    RETURNS TABLE
AS
RETURN
    (
        SELECT    name
        FROM    dbo.sysobjects where xtype ='U'
    )
GO
Exec sp_configure 'Allow Updates',0
Reconfigure with Override
Now run this from any database on your sql server and it will list out the user tables for that database. Pretty cool right?
SELECT *FROM :: fn_listtablesindatabase()