How to delete a SQL Server function with a script?
How to delete a user-defined SQL Server function with a T-SQL script, and avoid errors? This SQL commands allows to get rid of a SQL Server function. It also check if the function exists. This tutorial shows how to do this with simple examples. And the last example provides a script to drop all the functions at once from a database.
Table of Contents
1. SQL Server DROP FUNCTION script example
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; -- or another variation using the schema DROP FUNCTION dbo.ufnDisplayCustomers; -- or another one using the database name and the schema name DROP FUNCTION [database_name].[dbo].[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. T-SQL script to delete all user 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.
To use this script, make sure you double check that the functions ar not needed anymore and make sure to have a backup of the code. Replace 'your_filter_here%' with the necessary filter or wildcard. The script will then delete all user-defined functions that match the specified filter.
DECLARE @schema_name NVARCHAR(128)
DECLARE @function_name NVARCHAR(128)
DECLARE @drop_statement NVARCHAR(MAX)
DECLARE @filter NVARCHAR(128) = 'your_filter_here%' -- Update this with your filter or wildcard
-- Create a cursor to loop through all user-defined functions matching the given filter
DECLARE function_cursor CURSOR FOR
SELECT s.name AS schema_name, o.name AS function_name
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type IN ('FN', 'IF', 'TF', 'FS', 'FT') AND o.name LIKE @filter
ORDER BY s.name, o.name
-- Open the cursor
OPEN function_cursor
-- Fetch the first row from the cursor
FETCH NEXT FROM function_cursor INTO @schema_name, @function_name
-- Loop through all user-defined functions
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build the DROP FUNCTION statement
SET @drop_statement = 'DROP FUNCTION [' + @schema_name + '].[' + @function_name + ']'
-- Execute the DROP FUNCTION statement
EXEC sp_executesql @drop_statement
-- Fetch the next row from the cursor
FETCH NEXT FROM function_cursor INTO @schema_name, @function_name
END
-- Close and deallocate the cursor
CLOSE function_cursor
DEALLOCATE function_cursor
Step by step description of the script
The script above is relatively complex, it performs these 9 steps:
- First declare the T-SQL variables to store schema name, function name, the full DROP statement, and the filter or wildcard for selecting specific user-defined functions.
- Define the cursor named function_cursor to loop through all user-defined functions that match the given filter. The cursor selects the schema name and function name from the sys.objects and sys.schemas system tables, filtering by the object types that represent user-defined functions and applying the filter on the function name.
- Open the cursor named function_cursor to start iterating through the rows that represent the user-defined functions.
- Fetch the first row from the cursor and store the schema name and function name in the corresponding variables.
- Loop through user-defined functions with a WHILE loop to iterate through all the rows fetched by the cursor. The loop continues as long as there are rows to process.
- Build the DROP statement. Inside the loop, construct the DROP FUNCTION statement using the schema name and function name variables. This statement will be used to delete the user-defined function.
- Execute the DROP statement using the sp_executesql stored procedure to execute the dynamically constructed DROP FUNCTION statement, effectively deleting the user-defined function.
- Fetch the next row from the cursor and store the schema name and function name in the corresponding T-SQL variables.
- Finally close and deallocate the cursor. Once all rows have been processed, close the cursor to release the resources and deallocate it to remove the cursor’s definition.
Conclusion on how to drop functions using T-SQL code
In this T-SQL tutorial, we explored different methods to delete a SQL Server function using a script. We began by examining the basic DROP FUNCTION syntax and how to use it in various scenarios. Next, we learned to check if a function exists before attempting to delete it, using both the classic and the IF EXISTS DROP FUNCTION approaches.
Lastly, we covered a more complex script that can delete all T-SQL user-defined functions at once by utilizing a cursor and a loop, with the ability to filter by name or wildcard. This tutorial provided step-by-step explanations and examples to help you efficiently manage SQL Server functions using T-SQL scripts.
