1

I need to create a function that can be used across all databases in my SQL Instance. Can I add a User Defined Function at the server level in SQL Server 2008 r2?

Juan Tarquino
  • 967
  • 7
  • 13

3 Answers3

11

I would create a small database for yourself called "Tools" or something like that to store these kinds of functions. Then reference them with a fully qualified name where you need them: Tools.dbo.MyUDF.

Avoid the temptation to store things like this in the Master database. That database belongs to SQL Server and should not be used for such purposes.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Why shouldn't the master database be used for this? – Martin Smith Jan 27 '11 at 14:42
  • @Martin: Microsoft recommends against it [here](http://msdn.microsoft.com/en-us/library/ms187837.aspx) and I agree that it's a best practice to avoid doing so. – Joe Stefanelli Jan 27 '11 at 14:49
  • 1
    @Martin – I wouldn't expect non-standard objects to be present in the master DB and if those objects were named similarly to the standard objects, I wouldn't be able to easily tell which were which. – Kenny Evitt Jan 27 '11 at 14:50
  • @Joe - The only reason they give there is backup frequency. I guess it depends on the purpose of the function. The details escape me at the moment but having it in `master` with an `sp_` prefix and marked as a system object can be beneficial if it needs to query system tables. – Martin Smith Jan 27 '11 at 14:52
  • 2
    @Martin: It's definitely more of a best practice rather than an absolute. For me, it reminds me of when my son decided to put all of his Lego blocks in the fish tank. You don't store unexpected things in unexpected places. – Joe Stefanelli Jan 27 '11 at 15:04
  • @Joe - Better than putting the fish in the lego box I suppose! (Nice Analogy +1) – Martin Smith Jan 27 '11 at 15:07
1

Only by adding it to the master database. You might want to give it the sp_ prefix so that it is looked for in the master database automatically. Additionally dependent upon the purpose of the function you might need to mark it as a system object.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    While this would certainly solve the problem of 'scoping' a UDF for use in any DB, appropriate permissions would still need to be setup (and maintained) for users connected to other DBs to execute that UDF. – Kenny Evitt Jan 27 '11 at 14:52
  • 2
    And it would most definitely be confusing to name a UDF with a `sp_` prefix! – Kenny Evitt Jan 27 '11 at 14:52
0

No, a UDF must be created in a particular DB.

A UDF in any DB can be used in any other DB as long as the SQL Server login for the relevant executing user(s) have sufficient permission to do so. One reason to create such UDFs in a separate, meaningfully-named DB would be to clearly document the purpose of the UDF (and any other objects you might create in that DB) for other users, administrators, support techs, etc.

Kenny Evitt
  • 9,291
  • 5
  • 65
  • 93