How to drop functions with dependencies

Question

We need to drop all functions for a specific database ( by code ). There are a lot of questions for dropping functions but all anwsers fail to drop functions with dependency. One popular anwser is following:

/* Drop all functions */ 
DECLARE @name VARCHAR(128) 
DECLARE @SQL VARCHAR(254) 
DECLARE @schema VARCHAR(128)

SELECT @name = (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]) 
SELECT @schema = (SELECT TOP 1 schema_name(schema_id) FROM sys.objects WHERE [name] = @name)

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

How can i adjust it to ensure it is deleted in correct order ( and doesnt fail because of Msg 3729 )

Answer 1

You can use sys.dm_sql_referencing_entities to find references to a function.

So you just retrieve all functions which have no references, drop them, then loop again.

DECLARE @maxRuns int = 10;

WHILE (@maxRuns > 0)
BEGIN
    DECLARE @sql nvarchar(max) = (
        SELECT STRING_AGG(
            'DROP FUNCTION ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ';', '
'
          )
        FROM sys.objects o
        JOIN sys.schemas s ON s.schema_id = o.schema_id
        WHERE o.type IN (N'FN', N'IF', N'TF', N'FS', N'FT')
          AND o.is_ms_shipped = 0
          AND NOT EXISTS (SELECT 1
            FROM sys.dm_sql_referencing_entities(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT')
          )
    );

    IF (@sql IS NULL)
        BREAK;

    PRINT @sql; -- for testing
    EXEC sp_executesql @sql;
    SET @maxRuns -= 1;
END;

db<>fiddle