Export (0) Print
Expand All

Delete User-defined Functions

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

In This Topic

Limitations and Restrictions

  • You will not 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 will not be able to delete the function if there are computed columns that reference this function and have been indexed.

Security

Permissions

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

Arrow icon used with Back to Top link [Top]

To delete a user-defined function

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

  2. Click on the plus sign next to the Programmability folder.

  3. Click 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, click OK.

    Important note Important

    Click 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.

Arrow icon used with Back to Top link [Top]

To delete a user-defined function

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

  2. On the Standard bar, click New Query.

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

    -- creates function called “Sales.ufn_SalesByStore”
    USE AdventureWorks2012;
    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
    
    USE AdventureWorks2012;
    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
    

For more information, see DROP FUNCTION (Transact-SQL).

Arrow icon used with Back to Top link [Top]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft