Seven times ALTER one DROP
It all started with the fact that I began to write the standard design T-SQL for my company. In this topic, I will focus on the construction of deleting an object before creating it.
Our team has about twenty SQL Ninja developers and everyone describes 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 most of all the likes gathered here is this 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 they told me that it was like this:
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 you do DROP and CREATE every time, then the rights to the object are deleted, and the object can also be in replication and when recreated, it will also be deleted from it.
In general, I liked this
in a procedure called dbo.antidrop.
The procedure has only two arguments, this is 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 as a result:
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!