9

I have a function which is used in more than a dozen databases. I want this function to be accessible in all the databases by writing it only once, so I want to write the function in master database so that my function can be accessed easily.

Is there any problem in writing the user defined function in master db?

When surfing the Internet some suggested converting the function to a system function. (Using “EXEC sp_ms_marksystemobject 'fn_db_name' ”).

Is there a necessity to convert the function created in master db to system function? If yes why?

  • If the function simply performs an operation on data passed in, and returns the result, there should be no problem. I have found problems when using a stored procedure created in master that was referencing system views such as information_schema. This was a long time ago however. Whatever you create, you need to fully test it. – datagod Dec 4 '15 at 13:58
  • 5
    I would advise against putting it in master. Create a utility database and put it there. You will need to make sure that as part of your DR plan, you have the source to the object in master (you typically do not restore the system databases) and that it is up to date. – Jonathan Fite Dec 4 '15 at 14:18
  • What exactly is this function supposed to be doing? – Solomon Rutzky Dec 4 '15 at 15:30
10

I agree with Jonathan - as long as the function isn't supposed to return local data based on the calling database context, put the function in a utility database (this is also where I put things like numbers and calendar tables, splitting functions, etc):

USE UtilityDB;
GO
CREATE FUNCTION dbo.whatever() RETURNS ...
GO

Now, in every database that needs to access the function, just create a synonym:

CREATE SYNONYM dbo.whatever() FOR UtilityDB.dbo.whatever();

This way each database can still reference the function using a simple, 2-part name - and they don't have to know or care where it actually exists. And you only have to maintain a single copy of the function.

(And in fact, you can put the synonym in the model database, so that it is created in new databases automatically.)

The reason I dislike putting user objects in master - unless they really do need to be globally available and contextual to the calling database, like your own customized version of sp_spaceused or sp_helpindex - is that people aren't looking for user objects in master, and they're less discoverable there. They also make it harder to migrate your user databases elsewhere, because you need to remember the user stuff you put in master, too. If you are absolutely dead-set against creating or using a utility database for this, I think msdb is a more practical choice for a central location.

5

While I agree with @Jonathan and @Aaron that you should be using a "utility" database (I even name mine Utility ), and I agree with Aaron about using synonyms, I would like to draw attention to the caveat that Aaron mentioned as it is important:

as long as the function isn't supposed to return local data based on the calling database context...

Meaning, if you do need the "current database" context (as opposed to the code running in the database where it actually exists, whether it be master or Utility), then using anything but master won't work.

And so if you do need the Function to run in the context of the database it is called from, then the plan to use a Function will not work anyway since having code in the master DB that can run in the current database context outside of master only works for Stored Procedures, even if you use the undocumented sp_ms_marksystemobject system Stored Procedure to mark your Function as a system object. And in fact, prefixing the name of the Function with fn_ or even sp_ does not allow it to be found outside of master without fully qualifying the object name. And then, even if you do fully qualify the name, it still only runs in the context of the master database, even after being marked as a system object.

So you have three choices:

  1. If you do not need current DB context, then follow Aaron's advice to use a Utility database for the object and use synonyms in the databases that need to use the Function to point to the Utility database.
  2. If you do need current DB context:

    1. Change the Function to be a Stored Procedure, rename to start with sp_, and run the sp_ms_marksystemobject system Stored Procedure on it to mark it as a system Stored Procedure (else it won't reflect current DB context for queries, even though it will for some built-in functions like DB_NAME() ). I am not a big fan of this approach, but it does work.
    2. Use SQLCLR to create a function that can be placed into the Utility database and have the current database name (or any database name) passed into it to be used to create the Dynamic SQL that will be the submitted query. I posted an example of doing this type of this with SQLCLR in the following answer, also on DBA.StackExchange:

      Central stored procedure to execute in calling database context

      You can also use synonym's in this approach, and then you just call the function while passing in DB_NAME() to automatically pass in the current database name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.