Can I create create a global function in SQL Server?

Question

Is it possible to create a function in SQL Server which I could use in any database on the server, without adding the database prefix?

For example, with this function:

CREATE FUNCTION getDays (@date date)
RETURNS INT
AS
BEGIN

RETURN CASE WHEN MONTH(@date) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
            WHEN MONTH(@date) IN (4, 6, 9, 11) THEN 30
            ELSE CASE WHEN (YEAR(@date) % 4    = 0 AND
                            YEAR(@date) % 100 != 0) OR
                           (YEAR(@date) % 400  = 0)
                      THEN 29
                      ELSE 28
                 END
       END

END

Aaron Bertrand · Accepted Answer · 2012-05-23T21:33:31.317

19

You can create the function in master (or some other permanent database), and then create a synonym in the model database:

USE model;
GO
CREATE SYNONYM dbo.getDays FOR master.dbo.getDays;

This will create a synonym to the function in any new databases, but for existing databases (or databases attached or restored in the future) you'll need to copy the synonym there. This will allow you to reference the object with a two-part name in any database, while only having to store one copy of the code.

As an aside, your code could be much more concise:

  RETURN (SELECT DATEPART(DAY, DATEADD(DAY, -1, 
     DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(@date), @date)))));

So from the top:

USE [master];
GO
DROP FUNCTION dbo.getDays;
GO
CREATE FUNCTION dbo.getDays
(
    @date DATE
)
RETURNS INT
AS
BEGIN
    RETURN (SELECT DATEPART(DAY, DATEADD(DAY, -1, 
         DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(@date), @date)))));
END
GO

Now to create a synonym for this in each database:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10) 
+ 'USE ' + QUOTENAME(name) + ';

IF OBJECT_ID(''dbo.getDays'', ''FN'') IS NOT NULL
  DROP FUNCTION dbo.getDays;

IF OBJECT_ID(''dbo.getDays'', ''SN'') IS NOT NULL
  DROP SYNONYM dbo.getDays

CREATE SYNONYM dbo.getDays FOR master.dbo.getDays;'
 FROM sys.databases WHERE name <> 'master';

PRINT @sql;

EXEC sp_executesql @sql;

edited May 23 '12 at 21:33

answered May 23 '12 at 21:21

Aaron Bertrand

272,866
37
466
490

'getDays' is not a recognized built-in function name. – Артём Царионов May 23 '12 at 21:23
You need to create the function in master, then create the synonym in existing databases (not just model). Creating it in model only creates the function in *new* databases that are created after the synonym has been created in model. And you should always, always, ALWAYS reference functions or synonyms with the schema prefix, so it should be `dbo.getDays`, not `getDays`. – Aaron Bertrand May 23 '12 at 21:25
1

It gets the same results... working from the inside out, subtract (1-`@date`'s date) from `@date` to get the first of that month. Add a month, that's the first of the next month. Then subtract a day. That's the last day of @date's month. DATEPART(DAY) extracts the day (e.g. 31 for @date = '2005-05-anything')... – Aaron Bertrand May 23 '12 at 21:36
I have read that creating custom functions inside master db is not safe in the sense that a future update may remove this function. Is that correct? – variable Dec 28 '21 at 13:40

Gordon Linoff · Answer 2 · 2012-05-23T21:40:23.120

5

Although creating stored procedures in master makes them globally available, this does not work for functions. You need to use the three-part naming convention:

select <dbname>.<schema>.getDays(...)

Why would Microsoft make functions different from stored procedures?

edited May 23 '12 at 21:40

answered May 23 '12 at 21:18

Gordon Linoff

1,242,037
58
646
786

1

Creating the function in master does not make it available to other databases. This behavior is true for stored procedures, and only when the `sp_configure` option `allow updates` is set to 1, or the procedure is named `sp_`. – Aaron Bertrand May 23 '12 at 21:19
I've created it in master but my script doesn't see it – Артём Царионов May 23 '12 at 21:21
The functionality that makes stored procedures globally available was never really meant for customers to use, it was meant for system stored procedures like sp_help to exist in one place but to be able to run in the context of any database. This feature was never needed internally for tables, views, functions etc. so it simply never existed. I don't think the use of this "feature" is officially supported, and it may not work in some future version. – Aaron Bertrand May 23 '12 at 21:42
2

Aaron, that's a fine answer, but why wouldn't they do the same thing for functions? Surely, there are system functions that also benefit from being globally available. – Gordon Linoff May 23 '12 at 21:46
1

Not in 2000 there weren't. This whole architecture was replaced in 2005, where dynamic management objects (both functions and views) *are* globally available, but not through the same mechanism. The system procedures still work this way for backward compatibility reasons, not because it's the right way, and I wouldn't expect any of the sp_ procedures (or how they work internally) to change in the future, other than the discontinuation of the 'allow updates' or mark system object hacks. – Aaron Bertrand May 23 '12 at 21:51

Can I create create a global function in SQL Server?

2 Answers2

Linked