In our previous article, we explained to you about creating User Defined Functions. Let us see how to alter User Defined Functions in SQL Server, such as Rename, modify, and delete the existing UDFs.
How to see User Defined Functions in SQL?
To view the existing functions in SQL Server, Please select the Database that has UDFs. From the below screenshot, you can observe that our database has all the functions that we created in our User Defined Functions article.
To execute the existing functions, Please select the one that you want to execute (AverageSale). Next, right-click on it and select the Script Function as -> SELECT To -> New Query Window Editor
Once you select the New Query Window Editor option, Select from query will return automatically by the SSMS
Properties of SQL User Defined Functions using SSMS
First, Right-click on the required name and select the SQL Server properties option.
Once you click on the Properties option, a new window called Function Properties opened. The following are the list of SQL Server Function Properties, and they are:
- Database: It shows the name of the database that contains the specified function. Here, it is AverageSale
- Server: The name of the current Instance.
- User: It shows the name of the current user
- Created Date: It displays the created date.
- Name: It shows the name of the current one. i.e., AverageSale
- Schema: It displays the schema that we used for it.
- System Object: It shows Boolean value True or False, indicating whether the current one is a System Object or Not.
- ANSI NULLs: Displays Boolean value True or False, indicating whether the object was created with ANSI NULLs or Not.
- Function type: It show’s whether the function is SCALAR or Table-Valued Functions
Properties of SQL User Defined Functions using Query
How to find the definition of a SQL User Defined Functions using the OBJECT_DEFINITION.
-- It Returns the AverageSale function Definition SELECT OBJECT_DEFINITION (OBJECT_ID('dbo.AverageSale')) AS ObjectDefinition; GO
The following query returns the remaining properties of SQL UDFs.
-- It Returns the AverageSale Name & it's Properties SELECT smo.OBJECT_ID, OBJECT_NAME(smo.OBJECT_ID) AS OBJECT_NAME, Obj.TYPE, Obj.TYPE_DESC, obj.create_date, smo.uses_ansi_nulls, smo.uses_quoted_identifier, smo.is_schema_bound, smo.execute_as_principal_id FROM sys.sql_modules AS smo JOIN sys.objects AS Obj ON smo.OBJECT_ID = Obj.OBJECT_ID WHERE smo.OBJECT_ID = OBJECT_ID('dbo.AverageSale') ORDER BY Obj.TYPE GO
TIP: You can also return the definition by adding: smo.DEFINITION inside the Select Statement.
Rename User Defined Functions in SQL Server
To rename the User Defined Functions using the Microsoft SQL Server Management Studio, right-click on the one that you want to modify (AverageSale) and select the Rename option
Once you click on the Rename option, SSMS allows us to rename as per our requirements.
Modify User Defined Functions in SQL Server
The following examples will help you understand modifying or altering the SQL User Defined Functions using the Management Studio (SSMS) and query.
Alter UDFs using SSMS
To modify the UDFs using the SQL Server Management Studio, right-click on the function (CustomerbyDepartmnet) that you want to change, and select the Modify option
Once you choose the modify option, a new query window will open with the following query. You can edit as per your requirement.
Alter SQL User Defined Functions using Query
Let me use the ALTER FUNCTION to modify the existing. For demonstration purpose, we are concatenating the First Name and Last Name as NAME
ALTER FUNCTION [dbo].[CustomerbyDepartment] (@profession VARCHAR(50)) RETURNS TABLE AS RETURN ( SELECT [FirstName] + ' ' + [LastName] AS Name ,[Occupation] ,[Education] ,dept.DepartmentName AS Department ,[YearlyIncome] AS Income ,[Sales] FROM [MyEmployees Table] INNER JOIN Department AS dept ON Dept.[id] = [MyEmployees Table].DeptID WHERE [Occupation] = @profession )
Let us see the Output
SELECT * FROM [dbo].[CustomerbyDepartment] ('Management') GO
From the below screenshot, you can see it is returning the NAME instead of First Name and Last Name.
Delete User Defined Functions in SQL Server
How to delete the User Defined Functions using the Microsoft SQL Server Management Studio (SSMS), and transact Query. Before we get into the examples, remember the limitations:
- SQL Server will not allow you to delete the function if there are any references from it or Views to it.
- We cannot delete, if there are any references from Computed Columns, CHECK, or Default Contrarians to it.
To demonstrate the delete operation, we added two more scalar functions. Our task is to delete the EmployeeSale and SaleEmployees.
Delete UDFs using SQL Server Management Studio
To delete the UDFs using the SQL Server Management Studio, right-click on the function name and click the Delete option. Here, we want to delete SaleEmployees (scalar.
Choosing the delete option opens a Delete Object window. Click on the Show Dependencies button to check the dependencies, and then click OK to delete that.
Delete Functions using SQL Query
How to use SQL Server DROP FUNCTION to delete the User Defined.
DROP FUNCTION [dbo].[EmployeesSale] GO
TIP: It is good practice to check, whether it exists in the database or not using IF OBJECT_ID (N’EmployeeSale’, N’IF’) IS NOT NULL
Let us see what happens when we call the deleted one.
From the above screenshot, see we don’t have EmployeeSale in our Object explorer, and querying on it is throwing an error.