SQL Server – Scripting Drop/Create Dependencies with Schemabinding

I've found a way to do this by using a couple of different scripts.

First, I needed to setup the "PrintString" stored procedure from Greg M. Lucas in order to print all of my statements without them being truncated.

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

Second, I created a temporary table to store all of the dependent entities so I can use it later to print my script. Included in the temporary table is an "Order" column for doing descending order on the drop statements and ascending order on the create statements.

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

Third, I fill my temporary table using a recursive CTE. In this example my parent view is called '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

Fourth, I create my drop statements and then my create statements using my temporary table and I copy out the results. Note that my version only handles inline table-valued functions, procedures, and views. You will need to update it if you need to handle more object types.

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

Finally, I cleanup the temporary table and drop the "PrintString" procedure:

DROP TABLE #DependentEntitiesInformation
GO

DROP PROCEDURE [dbo].[PrintString]
GO