How to check if a function exists in a SQL database?

Question

I need to find out if a function exists in a database, so that I can drop it and create it again. It should basically be something like the following code that I use for stored procedures:

IF EXISTS (
     SELECT  *
     FROM    dbo.sysobjects
     WHERE   id = OBJECT_ID(N'[dbo].[SP_TEST]')
             AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )

Martin Smith · Accepted Answer · 2013-10-18T14:18:47.280

231

This is what SSMS uses when you script using the DROP and CREATE option

IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = OBJECT_ID(N'[dbo].[foo]')
                  AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
  DROP FUNCTION [dbo].[foo]

GO

This approach to deploying changes means that you need to recreate all permissions on the object so you might consider ALTER-ing if Exists instead.

edited Oct 18 '13 at 14:18

answered Mar 24 '11 at 12:30

Martin Smith

438,706
87
741
845

18

Makes me wonder **even more** why [there isn't a sys.functions](http://stackoverflow.com/questions/468672/sql-server-where-is-sys-functions) system catalog view..... – marc_s Mar 24 '11 at 12:33

score 73 · Answer 2 · edited Aug 27 '13 at 13:41

73

I tend to use the Information_Schema:

IF EXISTS ( SELECT  1
            FROM    Information_schema.Routines
            WHERE   Specific_schema = 'dbo'
                    AND specific_name = 'Foo'
                    AND Routine_Type = 'FUNCTION' )

for functions, and change Routine_Type for stored procedures

IF EXISTS ( SELECT  1
            FROM    Information_schema.Routines
            WHERE   Specific_schema = 'dbo'
                    AND specific_name = 'Foo'
                    AND Routine_Type = 'PROCEDURE' )

edited Aug 27 '13 at 13:41

SteveC

15,808
23
102
173

answered Sep 22 '11 at 10:51

Law Metzler

1,205
9
11

2

Cool I was looking for something like this and never found it. I believe it is better to use information_schema in general as it is not tied to a specific RDBMS. (btw the notion of being cross-platform compatible came from this answer: http://stackoverflow.com/a/14290099/420667) – user420667 Nov 04 '15 at 20:36

Kapé · Answer 3 · 2022-10-04T08:48:16.113

61

Why not just:

IF object_id('YourFunctionName', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[YourFunctionName]
END
GO

The second argument of object_id is optional, but can help to identify the correct object. There are numerous possible values for this type argument, particularly:

FN : Scalar function
IF : Inline table-valued function
TF : Table-valued-function
FS : Assembly (CLR) scalar-function
FT : Assembly (CLR) table-valued function
U : Table (user-defined)

edited Oct 04 '22 at 08:48

answered Sep 21 '15 at 13:18

Kapé

4,411
3
37
54

5

Technically this can fail as it only checks that there is an object of that name. Not that there is an object and that it is a function. E.G. If `CREATE TABLE YourFunctionName(X INT);` then running the code will fail. – Martin Smith May 06 '16 at 13:45
2

@MartinSmith: Easy to make robust. Just use `object_id('YourFunction', 'FN')` or any other designator (second argument) that makes it clear what kind of object you're referring to. – darlove Sep 20 '17 at 13:38
@darlove using 'FN' as second parameter may not work. I just learned. 'FN' means scalar function. This link tells you different parameters values you can pass http://sqlhints.com/tag/how-to-check-if-function-exists/. I keep using 'FN' to check for existing Table value function, and it does not work. I have to use 'TF' – user12345 Dec 29 '17 at 17:33

jamiedanq · Answer 4 · 2018-05-30T08:14:13.163

17

I know this thread is old but I just wanted to add this answer for those who believe it's safer to Alter than Drop and Create. The below will Alter the Function if it exists or Create it if doesn't:

  IF NOT EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[foo]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
       EXEC('CREATE FUNCTION [dbo].[foo]() RETURNS INT AS BEGIN RETURN 0 END')
  GO
  ALTER FUNCTION [dbo].[foo]
  AS
  ...

edited May 30 '18 at 08:14

answered Jun 06 '17 at 08:03

jamiedanq

967
7
12

3

I like this, but I think it should be "ALTER FUNCTION", no? – Erik Mar 29 '18 at 18:24
1

I like `ALTER OR CREATE` – AgentFire Mar 30 '20 at 09:49

score 12 · Answer 5 · answered Feb 13 '15 at 21:46

I've found you can use a very non verbose and straightforward approach to checking for the existence various SQL Server objects this way:

IF OBJECTPROPERTY (object_id('schemaname.scalarfuncname'), 'IsScalarFunction') = 1
IF OBJECTPROPERTY (object_id('schemaname.tablefuncname'), 'IsTableFunction') = 1
IF OBJECTPROPERTY (object_id('schemaname.procname'), 'IsProcedure') = 1

This is based on the OBJECTPROPERTY function which is available in SQL 2005+. The MSDN article can be found here.

The OBJECTPROPERTY function uses the following signature:

OBJECTPROPERTY ( id , property )

You pass a literal value into the property parameter, designating the type of object you are looking for. There's a massive list of values you can supply.

I think it would be easier to see the simplicity of this answer if it included a complete if/drop example. — Jonathan, May 18 '17 at 18:26

score 2 · Answer 6 · answered Oct 04 '22 at 08:55

From SQL Server 2016 SP1, SQL Server 2017 or later, you can use the syntax:

CREATE OR ALTER [object] ...

To avoid jumping through these hoops. This has an additonal benefit of metadata about the procedure being persisted (rather than every time you change it it getting a brand new objectid) which can sometimes be useful, especially if you use things like QueryStore or other tools that care about the objectid.

How to check if a function exists in a SQL database?

6 Answers6