Published on: 25 September, 2017

How to check if a function exists in Sql Server?

Tumblr Digg Google Plus

When creating a function dynamically, it is a best practice to perform a check if a particular function you want to create has already existed in a database. To perform a check of an existing function you can use the following EXISTS keyword. If the function has already existed then you have to drop the function first and then perform a creation of the function.

IF EXISTS (SELECT * FROM   sys.objects WHERE  object_id = OBJECT_ID(N'[dbo].[my_function_name]') AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
    DROP FUNCTION [dbo].[my_function_name]

CREATE FUNCTION [dbo].[my_function_name](
       @ID NUMERIC(18,0)
            DECLARE @NoOfRecords INT = 0
            SELECT @NoOfRecords = COUNT(*)
            FROM [dbo].ExampleTable
            WHERE ID = @ID

            IF @NoOfRecords > 0
                     RETURN 1

            RETURN 0


If you need to perform an update of a function, the way to check is pretty much the same as checking if a function already exists. You can re-drop the existing function and recreate it again..


Wayne Bortner
06 March, 2018
Have you seen this code working? I paste the first code into SQL Server Management Studio and get the result SQL Error(156): Wrong Syntax in the near of keyword FUNCTION
08 March, 2018
Hi Wayne, Thanks for correcting the issue. It has to be done into a separated batch process. I have updated the article. It will perform a checking if the function has already existed, it will drop it first and then perform another action to create it.
Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.