How to check if a function exists on a SQL database

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.

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

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