How to check if a function exists on a SQL database

sql sql-server sql-function
Dr. Greenthumb picture Dr. Greenthumb · Mar 24, 2011 · Viewed 148.3k times · Source

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 )

Answer

Martin Smith picture Martin Smith · Mar 24, 2011

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.

Related questions

Function vs. Stored Procedure in SQL Server

I've been learning Functions and Stored Procedure for quite a while but I don't know why and when I should use a function or a stored procedure. They look same to me, maybe because I am kinda newbie about that. …

Read More
How to Replace Multiple Characters in SQL?

This is based on a similar question How to Replace Multiple Characters in Access SQL? I wrote this since sql server 2005 seems to have a limit on replace() function to 19 replacements inside a where clause. I have the following task: …

Read More
SQL Query - Concatenating Results into One String

I have a sql function that includes this code: DECLARE @CodeNameString varchar(100) SELECT CodeName FROM AccountCodes ORDER BY Sort I need to concatenate all results from the select query into CodeNameString. Obviously a FOREACH loop in C# code would do …

Read More