How to check if a function exists in 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 NULLBEGIN    DROP FUNCTION [dbo].[YourFunctionName]ENDGO

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