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

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 )

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

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

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

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.