Delete a SQL Server function with a script

How to delete a SQL Server function with a script?

How to delete, or drop a user-defined SQL Server function with a script, and avoid errors? This SQL command allows to get rid of a SQL Server function. However, it does not check if the function exists. This article shows how to do this with a simple example.

1. How to delete a SQL function with a script ?

Use this T-SQL code to drop a SQL Server function from the database, the result of the command will be permanent. Make sure you have backups of the code and the databases. To avoid errors in case the object is not anymore in the database, use an existence check before call the delete command. Let’s consider that we created the function named ufnDisplayCustomers from this article on how to create a SQL function.

DROP FUNCTION ufnDisplayCustomers;

This basic example shows the syntax of the DROP FUNCTION command. To go further on the functions, check the SQL Server functions category with more articles on how to manage them. Check also the Microsoft documentation on functions and their different types and usage, for instance the Aggregate functions, Scalar functions or Analytic functions.

2. Check if the function exists before deleting the function

Also, to avoid errors during the script execution, it is a best practice to use a check before the deletion. Use this script example to check in the database the existence, using the system table named sys.objects and the object_id function, to get the technical id of the function.

IF EXISTS (
  SELECT * FROM sys.objects
  WHERE object_id = object_id(N'[dbo].[ufnDisplayCustomers]')
    AND type in (N'FN') 
)
BEGIN
  DROP FUNCTION [dbo].[ufnDisplayCustomers]
END;

Since SQL Server 2016, no need to use this long script, simply use the IF EXISTS DROP FUNCTION COMBINED statement. This statement does not execute any script is the function is not available in the target database.

 IF EXISTS DROP FUNCTION [dbo].[ufnDisplayCustomers];

3. Build a T-SQL script to delete all use defined functions at once

To go further and delete all use defined functions at once, simply include this DROP Function script inside a stored procedure with a loop. The loop need to take the name of all use defined functions in a variable and execute the drop statement. We’ve seen multiple ways to delete a SQL Server function with a T-SQL script. Make sure to use the most appropriate to your business case.