How do I drop a function if it already exists?
This works for any object, not just functions:
IF OBJECT_ID('YourObjectName') IS NOT NULL
then just add your flavor of object, as in:
IF OBJECT_ID('YourFunction') IS NOT NULL
DROP FUNCTION YourFunction
You have two options to drop and recreate the procedure in SQL Server 2016.
Starting from SQL Server 2016 - use IF EXISTS
DROP FUNCTION [ IF EXISTS ] { [ schema_name. ] function_name } [ ,...n ] [;]
Starting from SQL Server 2016 SP1 - use OR ALTER
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
IF EXISTS (
SELECT * FROM sysobjects WHERE id = object_id(N'function_name')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION function_name
GO
If you want to avoid the sys* tables, you could instead do (from here in example A):
IF object_id(N'function_name', N'FN') IS NOT NULL
DROP FUNCTION function_name
GO
The main thing to catch is what type of function you are trying to delete (denoted in the top sql by FN, IF and TF):
- FN = Scalar Function
- IF = Inlined Table Function
- TF = Table Function
if object_id('FUNCTION_NAME') is not NULL
DROP FUNCTION <name>
You can also look the name up in sysobjects
IF EXISTS (SELECT *
FROM sysobjects
WHERE name='<function name>' and xtype='FN'
Actually, if the function could be a table function, you need to use
xtype in ('FN','TF')