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 Renaming, Modifying, and deleting 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 [SQL Tutorial] database has all the functions that we created in our User Defined Functions article.
To execute the existing functions, Please select the function 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 function query will return automatically by the SSMS
Properties of SQL User Defined Functions using SSMS
First, Right-click on the required function 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 SQL Server Instance.
- User: It shows the name of the current user
- Created Date: It displays the function created date.
- Name: It shows the name of the current Function. i.e., AverageSale
- Schema: It displays the schema that we used for this function.
- System Object: It shows Boolean value True or False, indicating whether the current function 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 function 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 Function definition by adding: smo.DEFINITION inside the Select Statement.
Rename User Defined Functions in SQL Server
To rename the SQL User Defined Functions using the Microsoft SQL Server Management Studio, right-click on the function 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 Microsoft SQL Server 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 function. 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 the function is returning the NAME instead of First Name and Last Name.
Delete User Defined Functions in SQL Server
How to delete the SQL User Defined Functions using the Microsoft SQL Server Management Studio (SSMS), and T-SQL 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 Functions, or Views to this function.
- We cannot delete, if there are any references from Computed Columns, CHECK, or Default Contrarians to this function.
To demonstrate the delete operation, we added two more scalar functions. Our task is to delete the EmployeeSale and SaleEmployees functions.
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) function
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 function.
Delete Functions using SQL Query
How to use SQL Server DROP FUNCTION to delete the User Defined Functions
DROP FUNCTION [dbo].[EmployeesSale] GO
TIP: It is good practice to check, whether the function 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 function.
From the above screenshot, see we don’t have EmployeeSale function in our Object explorer, and querying on the function is throwing an error.
Comments are closed.