Seven times ALTER one DROP

It all started when I started writing the T-SQL design standard for my company. In this topic I will focus on the construction of deleting an object before its creation.

In our team of about twenty SQL Ninja developers and all describe this construction in different ways, for example like this:

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

Or so:
')

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

And even so:

 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 .. 

And on StackOverflow, the most likes gathered such an option:

 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 went to meet me and I came across an implementation on one of the SQL sites, which at first outraged me, but then I was told that it was “like that”:

 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 fact is that if each time you do DROP and CREATE, then the rights to the object are deleted, and the object can also be replicated during the re-creation, it will also be deleted from it.

So I liked this lambda decorator method and I decided to encapsulate it
in a procedure called dbo.antidrop.

The procedure has only two arguments, the name of the object and its type. You can see the type of your object like this:

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

Here is how it will look at the end:

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

And of course, the code of the procedure itself:

 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 

Thanks for attention!