Better ALTER than DROP

In this article, I am going to provide a construction for deleting an object before creating it.

In our team, there are about twenty SQL Ninja developers. All of them describe this construction in different ways.

Our team consists of about twenty SQL Ninjas and all of them use the following statement in a different way:

IF OBJECT_ID('dbo.Function', 'TF') IS NOT NULL
	DROP FUNCTION dbo.Function;
GO
CREATE FUNCTION dbo.Function ..

Or:

IF EXISTS (
    SELECT * 
    FROM sys.objects 
    WHERE name = 'Procedure'
        AND type = 'P' 
)
    DROP PROCEDURE dbo.Procedure;
GO
CREATE PROCEDURE dbo.Procedure ..

Or:

IF EXISTS (
    SELECT 1
    FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'dbo.Function')
        AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')
)
    DROP FUNCTION dbo.Function;
GO
CREATE FUNCTION dbo.Function ..

On StackOverflow, users liked this version:

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'function_name') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION function_name
GO

The stars are aligned and I found an appropriate implementation in one of SQL sites. At first, I was shocked, but then people helped to see why it works well.

IF OBJECT_ID('dbo.Function', 'TF') IS NULL
    EXEC('CREATE FUNCTION dbo.Function() RETURNS @t TABLE(i INT) BEGIN RETURN END');
GO
ALTER FUNCTION dbo.Function ..

The point is that if you use the DROP and CREATE statements each time, then you delete object permissions. In addition, the object can be in the replication and it will be deleted as well once re-created.

So, I liked this version and decided to wrap it up into the dbo.antidrop procedure.

The procedure takes only two arguments: object name and its type. To check the object type, execute the following statement:

SELECT type 
FROM sys.objects 
WHERE name = 'Name'

Here is how it will look like:

EXEC dbo.antidrop('dbo.Name', 'FN');
GO
ALTER FUNCTION dbo.Name ..

Finally, the code of the procedure is as follows:

IF OBJECT_ID('dbo.antidrop', 'P') IS NULL
    EXEC('CREATE PROC dbo.antidrop AS');
GO
CREATE PROC dbo.antidrop @name SYSNAME, @type SYSNAME
AS
BEGIN

    DECLARE @if_tf NVARCHAR(512) = '
        IF OBJECT_ID(''' + @name + ''', ''' + @type + ''') IS NULL
            EXEC(''CREATE FUNCTION ' + @name + '() RETURNS @t TABLE(i INT) BEGIN RETURN END'');
        GO
    ';
    DECLARE @fn NVARCHAR(512) = '
        IF OBJECT_ID(''' + @name + ''', ''' + @type + ''') IS NULL
            EXEC(''CREATE FUNCTION ' + @name + '(@i INT) RETURNS INT AS BEGIN RETURN @i + 1 END'');
        GO
    ';
    DECLARE @p NVARCHAR(512) = '
        IF OBJECT_ID(''' + @name + ''', ''' + @type + ''') IS NULL
            EXEC(''CREATE PROC ' + @name + 'AS'');
        GO
    ';
    DECLARE @v NVARCHAR(512) = '
        IF OBJECT_ID(''' + @name + ''', ''' + @type + ''') IS NULL
            EXEC(''CREATE VIEW ' + @name + ' AS SELECT 1 AS i'');
        GO
    ';

    IF @type in (N'IF', N'TF')
    BEGIN
        EXEC(@if_tf);
    END

    ELSE IF @type = N'FN'
    BEGIN
        EXEC(@fn);
    END
    
    ELSE IF @type = N'P'
    BEGIN
        EXEC(@p);
    END

    ELSE IF @type = N'V'
    BEGIN
        EXEC(@v);
    END

END
GO

Thank you for your attention!