How to check if a function exists on a SQL database

Tag: , ,

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)

Apologies if this is a trivial question, but I’m having a hard time finding the answer.

2 Answers

  1. cheeseballlumpy82 on Mar 05, 2013

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

    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.

  2. johan-offer on Mar 05, 2013

    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 then change Routine_type for stored procs:

    if EXISTS (select 1 from Information_schema.Routines where Specific_schema='dbo' and specific_name = 'Foo' and Routine_Type='PROCEDURE')