Edit

Delete user-defined functions

  • Article

Applies to: SQL Server Azure SQL Database

You can delete (drop) user-defined functions in SQL Server by using SQL Server Management Studio or Transact-SQL.

Limitations and restrictions

  • You won't be able to delete the function if there are Transact-SQL functions or views in the database that reference this function and were created by using SCHEMABINDING, or if there are computed columns, CHECK constraints, or DEFAULT constraints that reference the function.

  • You won't be able to delete the function if there are computed columns that reference this function and have been indexed.

Permissions

Requires ALTER permission on the schema to which the function belongs, or CONTROL permission on the function.

Use SQL Server Management Studio

  1. Select the plus sign next to the database that contains the function you wish to modify.

  2. Select the plus sign next to the Programmability folder.

  3. Select the plus sign next to the folder that contains the function you wish to modify:

    • Table-valued Function
    • Scalar-valued Function
    • Aggregate Function

  4. Right-click the function you want to delete and select Delete.

  5. In the Delete Object dialog box, select OK.

    Select Show Dependencies in the Delete Object dialog box to open the function_name Dependencies dialog box. This will show all of the objects that depend on the function and all of the objects on which the function depends.

Use Transact-SQL

  1. In Object Explorer, connect to an instance of the Database Engine.

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute.

    The following code sample creates a user-defined function:

    -- creates function called "Sales.ufn_SalesByStore"
USE AdventureWorks2022;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

    The following code sample deletes the user-defined function created in the previous example.

    USE AdventureWorks2022;
GO
-- determines if function exists in database
IF OBJECT_ID (N'Sales.fn_SalesByStore', N'IF') IS NOT NULL
-- deletes function
    DROP FUNCTION Sales.fn_SalesByStore;
GO

See also