Семь раз ALTER один DROP

в 10:01, , рубрики: alter, alter function, alter proc, alter procedure, alter view, create, create function, create proc, create procedure, create view, Drop, drop function, drop proc, drop procedure, drop view, if exists, Microsoft SQL Server, object_id, sql
image

Все началось с того, что я начал писать стандарт оформления T-SQL для своей компании. В этой теме я остановлюсь на конструкции удаления объекта перед его созданием.

В нашей команде порядка двадцати SQL Ninja разработчиков и все описывают данную конструкцию по разному, например вот так:

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


Или так:

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

И даже так:

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

А на StackOverflow больше всего лайков собрал вот такой вариант:

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

Звезды пошли ко мне на встречу и наткнулся я на одном из SQL-сайтов на реализацию, которая по началу возмутила меня, но потом мне подсказали что с ней «так»:

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

Дело в том что если каждый раз делать DROP и CREATE, то удаляются права на объект, а еще объект может быть в репликации и при пересоздании, из неё он удалится тоже.

Вобщем мне понравился этот лямбда декоратор способ и я решил его инкапсулировать
в процедуру под названием dbo.antidrop.

У процедуры всего два аргумента, это имя объекта и его тип. Посмотреть тип своего объекта можно вот так:

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

Вот как это будет выглядеть по итогу:

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

Ну и конечно же код самой процедуры:

IF OBJECT_ID('dbo.antidrop', 'P') IS NOT NULL
    DROP PROC dbo.antidrop;
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 BEGIN SELECT 1 END'');
        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 ('IF', 'TF')
    BEGIN
        EXEC(@if_tf);
    END

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

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

END
GO

Спасибо за внимание!

Автор: lestvt

Источник

Поделиться

* - обязательные к заполнению поля