CODEWITHSUNDEEP
sqlsql-serversql-function
Dr. Greenthumb
Dr. Greenthumb

Reputation: 2316

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

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 )

Upvotes: 161

Views: 198101

Answers (6)

TZHX
TZHX

Reputation: 5377

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.

Upvotes: 2

Kapé
Kapé

Reputation: 4781

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)

Upvotes: 63

jamiedanq
jamiedanq

Reputation: 967

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
  ...

Upvotes: 17

Jeremy
Jeremy

Reputation: 46340

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.

Upvotes: 12

Martin Smith
Martin Smith

Reputation: 453037

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.

Upvotes: 234

Law Metzler
Law Metzler

Reputation: 1235

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' )

Upvotes: 76

Related Questions