使用模式绑定更改视图时脚本删除/创建依赖项

我找到了一种通过使用几个不同的脚本来执行此操作的方法。

首先，我需要设置来自 Greg M. Lucas 的“PrintString”存储过程，以便打印我的所有语句而不截断它们。

CREATE PROCEDURE [dbo].[PrintString]
(
  @InputString     nvarchar(max)  = NULL
, @MaxPrintLength  int            = 4000
)
AS
BEGIN
    SET NOCOUNT ON
    --! CONSTANTS (keep it SQL2005 compatible)
    DECLARE @LF          char ( 1 ); SET @LF         = CHAR(10);
    DECLARE @CR          char ( 1 ); SET @CR         = CHAR(13);
    DECLARE @CRLF        char ( 2 ); SET @CRLF       = CHAR(13) + CHAR(10);
    DECLARE @LINE_BREAK  char ( 3 ); SET @LINE_BREAK = '%' + @LF + '%';
    --! Working Values
    DECLARE @WorkingLength    bigint
    DECLARE @WorkingString    nvarchar  (  max )
    DECLARE @SubString        nvarchar  (  max )
    DECLARE @SubStringLength  bigint
    --! Validate/correct inputs
    SET @MaxPrintLength = COALESCE(NULLIF(@MaxPrintLength, 0), 4000)
    IF @MaxPrintLength > 4000
        BEGIN
            RAISERROR('The @MaxPrintLength value of %d is greater than the maximum length supported by PRINT for nvarchar strings (4000)', 17, 1, @MaxPrintLength);
            RETURN(60000);
        END
    --! Working variables
    DECLARE @InputLength bigint = LEN(@InputString)
    IF @InputLength = 0
        GOTO OnComplete;
    --!
    --! Our input string may contain either carriage returns, line feeds or both
    --! to separate printing lines so we need to standardise on one of these (LF)
    --!
    SET @WorkingString = REPLACE(REPLACE(@InputString, @CRLF, @LF), @CR, @LF);
    --!
    --! If there are line feeds we use those to break down the text
    --! into individual printed lines, otherwise we print it in
    --! bite-size chunks suitable for consumption by PRINT
    --!
    IF PATINDEX(@LINE_BREAK, @InputString) > 0
        BEGIN --[BREAK_BY_LINE_FEED]
            --! Add a line feed on the end so the final iteration works as expected
            SET @WorkingString  = @WorkingString + @LF;
            SET @WorkingLength  = LEN(@WorkingString);
            DECLARE @LineFeedPos    bigint    = 0
            WHILE @WorkingLength > 0
                BEGIN
                    --!
                    --! Get the position of the next line feed
                    --!
                    SET @LineFeedPos = PATINDEX(@LINE_BREAK, @WorkingString);
                    IF @LineFeedPos > 0
                        BEGIN
                            SET @SubString        = SUBSTRING(@WorkingString, 1, @LineFeedPos - 1);
                            SET @SubStringLength  = LEN(@SubString);
                            --!
                            --! If this string is too long for a single PRINT, we pass it back
                            --! to PrintString which will process the string in suitably sized chunks
                            --!
                            IF LEN(@SubString) > @MaxPrintLength
                                EXEC [PrintString] @InputString = @SubString
                            ELSE
                                PRINT @SubString;
                            --! Remove the text we've just processed
                            SET @WorkingLength    = @WorkingLength - @LineFeedPos;
                            SET @WorkingString    = SUBSTRING(@WorkingString, @LineFeedPos + 1, @WorkingLength);
                        END
                END
        END --[BREAK_BY_LINE_FEED]
    ELSE
        BEGIN --[BREAK_BY_LENGTH]
            --!
            --! If there are no line feeds we may have to break it down
            --! into smaller bit size chunks suitable for PRINT
            --!
            IF @InputLength > @MaxPrintLength
                BEGIN
                    SET @WorkingString    = @InputString;
                    SET @WorkingLength    = LEN(@WorkingString);
                    SET @SubStringLength  = @MaxPrintLength;
                    WHILE @WorkingLength > 0
                        BEGIN
                            SET @SubString        = SUBSTRING(@WorkingString, 1, @SubStringLength);
                            SET @SubStringLength  = LEN(@SubString)
                            --!
                            --! If we still have text to process, set working values
                            --!
                            IF (@WorkingLength - @SubStringLength + 1) > 0
                                BEGIN
                                    PRINT @SubString;
                                    --! Remove the text we've just processed
                                    SET @WorkingString    = SUBSTRING(@WorkingString, @SubStringLength + 1, @WorkingLength);
                                    SET @WorkingLength    = LEN(@WorkingString);
                                END
                        END
                END
            ELSE
                PRINT @InputString;
        END --[BREAK_BY_LENGTH]
--/////////////////////////////////////////////////////////////////////////////////////////////////
OnComplete:
--/////////////////////////////////////////////////////////////////////////////////////////////////
    SET NOCOUNT OFF
    RETURN
END
GO

其次，我创建了一个临时表来存储所有相关实体，以便稍后可以使用它来打印我的脚本。临时表中包含一个“顺序”列，用于对删除语句进行降序排列，对创建语句进行升序排列。

CREATE TABLE #DependentEntitiesInformation
(
    ObjectId INT,
    SchemaName NVARCHAR(MAX),
    EntityName NVARCHAR(MAX),
    ObjectDefinition NVARCHAR(MAX),
    [Type] NVARCHAR(MAX),
    [Order] NVARCHAR(100)
)
GO

第三，我使用递归 CTE 填充我的临时表。在此示例中，我的父视图称为“dbo.Project_Expanded”。

SET NOCOUNT ON;

DECLARE @ObjectSchemaAndEntity NVARCHAR(MAX) = 'dbo.Project_Expanded';

WITH DependentEntities (ObjectId, SchemaName, EntityName, ObjectDefinition, [Type],[Order])
AS
(
    SELECT 
        e.referencing_id,
        e.referencing_schema_name, 
        e.referencing_entity_name,
        OBJECT_DEFINITION (e.referencing_id), 
        ao.[type],
        CAST(ROW_NUMBER() OVER(ORDER BY e.referencing_id) AS NVARCHAR(100))
    FROM 
        sys.dm_sql_referencing_entities (@ObjectSchemaAndEntity, 'OBJECT') e INNER JOIN
        sys.all_objects ao ON ao.[object_id] = e.referencing_id
    UNION ALL
    SELECT 
        e.referencing_id,
        e.referencing_schema_name, 
        e.referencing_entity_name,
        OBJECT_DEFINITION (e.referencing_id), 
        ao.[type],
        CAST(de.[Order] + '.' + CAST(ROW_NUMBER() OVER(ORDER BY e.referencing_id) AS NVARCHAR(100)) AS NVARCHAR(100))
    FROM 
        DependentEntities de OUTER APPLY
        sys.dm_sql_referencing_entities (de.SchemaName + '.' + de.EntityName, 'OBJECT') e INNER JOIN
        sys.all_objects ao ON ao.[object_id] = e.referencing_id 
)
INSERT INTO 
    #DependentEntitiesInformation
SELECT 
    ObjectId,
    SchemaName, 
    EntityName,
    ObjectDefinition, 
    [Type],
    [Order]
FROM 
    DependentEntities
GO

第四，我创建了 drop 语句，然后使用临时表创建了 create 语句，然后复制了结果。请注意，我的版本仅处理内联表值函数、过程和视图。如果您需要处理更多对象类型，则需要对其进行更新。

DECLARE @StatementEnd NVARCHAR(4000) = CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);

DECLARE @DropStatements NVARCHAR(MAX) = 
    (
        SELECT
            STRING_AGG(
                'DROP ' + 
                CASE
                    WHEN [Type] = 'IF' THEN 'FUNCTION'
                    WHEN [Type] = 'P' THEN 'PROCEDURE'
                    WHEN [Type] = 'V' THEN 'VIEW'
                END + 
                ' [' + SchemaName + '].[' + EntityName + ']', 
                @StatementEnd
            ) WITHIN GROUP (ORDER BY [Order] DESC)
        FROM 
            #DependentEntitiesInformation
    ) + @StatementEnd;

EXEC @DropStatements = [dbo].[PrintString] @DropStatements, 4000

DECLARE @CreateStatements NVARCHAR(MAX) = 
    (
        SELECT 
            STRING_AGG(ObjectDefinition, @StatementEnd) WITHIN GROUP (ORDER BY [Order])
        FROM 
            #DependentEntitiesInformation
    );

EXEC @CreateStatements = [dbo].[PrintString] @CreateStatements, 4000

DECLARE @Statement NVARCHAR(MAX) = @DropStatements + @CreateStatements;

EXEC [dbo].[PrintString] @Statement, 4000
GO

最后，我清理临时表并删除“PrintString”过程：

DROP TABLE #DependentEntitiesInformation
GO

DROP PROCEDURE [dbo].[PrintString]
GO