IMG-LOGO

How to delete all Functions in TSQL?

andy - 05 Jan, 2021 2903 Views 0 Comment

To delete If you need to delete all non-system functions in TSQL. We need to look against the sysobjects and looking for the type value under 'FN', 'IF', 'TF', 'FS', and 'FT'.

Here is the full snippet TSQL query to delete user functions in the SQL Server.


DECLARE @functionName VARCHAR(128)
DECLARE @SQL VARCHAR(500)

SELECT @functionName = (
	SELECT TOP 1 [name] 
	FROM sysobjects 
	WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') 
	AND category = 0 
	ORDER BY [name]
)

WHILE @functionName IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@functionName) +']'
    EXEC (@SQL)
    PRINT 'Deleted Function: ' + @functionName
    SELECT @functionName = (
		SELECT TOP 1 [name] 
		FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') 
		AND category = 0 AND [name] > @functionName 
		ORDER BY [name]
	)
END
GO

The way above script works by finding the first top function name and set it to a variable. Then it performs a while loop by checking against the variable name value. Inside the looping, it will perform a deleting against the function name set in the variable and perform another finding of the first top function record. if it finds it, it will then set it back to the variable again. The while loop will keep looping until the function name is no longer found.

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.

Related Articles

How to restore database using SQL query in TSQL?

If you have a database backup bak file extension and want to restore it using SQL query You can use the built in RESTORE DATABASE function Remember in order to be able to restore a database successfully You need to ...

How to get all table sizes in TSQL?

To get the information about how much space or size used by tables You can retrieve the size information by linking multiple tables in sys tables There are two tables that hold this information The first one is the sys ...