How to check if a function exists on a SQL database

Question

I need to find out if a function exists on 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

User · Accepted Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How to check if a function exists on a SQL database

Examples related to sql

Examples related to sql-server

Examples related to sql-function