Template Script: System Proc\sp_script.sql

USE MASTER
GO
IF OBJECT_ID('sp_script') IS NULL
    EXEC ('CREATE PROCEDURE sp_script AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE sp_script
    @ObjectName SYSNAME = NULL, /* NULL for all, supports like */
    @SchemaName SYSNAME = NULL, /* NULL for all, supports like */
    @ObjectList XML = NULL, /* Will overwrite @ObjectName */
    @NewSchemaName SYSNAME = NULL, /* NULL to scripts objects in original schema, any value overwrites */
    @NewTableName SYSNAME = NULL, /* This can be used if only one table is scripted, it will change the table name */
    @NewDatabaseName SYSNAME = NULL, /* To change the database context */
    @NewFileGroup SYSNAME = NULL, /* A new filegroup for all objects created can be given or a full partition schema with column) */
    @NewPartition SYSNAME = NULL, /* Full partition clause with the parameter like 'psDatasupplierIdTrans(DataSupplierId)' */
    @NewDataCompression SYSNAME = NULL, /* A blank value will not script datacompression options, any value will create all objects with the new data compression */
    @AddNamePrefix SYSNAME = NULL, /* Adds a prefix to table and constraint names being created */
    @ReplaceReferenceSchemaFrom SYSNAME = NULL, /* ** For foreign key constrainst you can replace a referenced schema name @ReplaceReferenceSchemaFrom as @ReplaceReferenceSchemaTo */
    @ReplaceReferenceSchemaTo SYSNAME = NULL,   /* ** Used in conjunction with @ReplaceReferenceSchemaFrom*/
    @DropForeignKey BIT = 0,
    @DropTable BIT = 0,
    @DropDefaultConstraints BIT = 0, /* This will drop default constraints */
    @DropClusteredIndex BIT = 0,
    @DropNonClusteredIndex BIT = 0,
    @DropPrimaryKey BIT = 0,
    @DropUniqueKey BIT = 0,
    @DropIndex BIT = 0,
    @DropCheckConstraint BIT = 0,
    @DropFunction BIT = 0,
    @DropProcedure BIT = 0,
    @DropTrigger BIT = 0,
    @DropView BIT = 0,
    @CreateTable BIT = 0, /* Table will always be created with it's defaults */
    @CreateTableInOneStep BIT = 0,
    @IncludeSchemaBoundObjects BIT = 0,
    @CreateDefaultConstraints BIT = 0, /* This will create default constraints */
    @EnableNonClusteredIndex BIT = 0,
    @EnableUniqueKey BIT = 0,
    @EnableIndex BIT = 0,
    @DisableNonClusteredIndex BIT = 0,
    @DisableUniqueKey BIT = 0,
    @DisableIndex BIT = 0,
    @CreateClusteredIndex BIT = 0,
    @CreateNonClusteredIndex BIT = 0,
    @CreatePrimaryKey BIT = 0,
    @CreateUniqueKey BIT = 0,
    @CreateIndex BIT = 0,
    @CreateForeignKey BIT = 0,
    @CreateForeignKeyInTwoSteps BIT = 0,
    @CreateCheckConstraint BIT = 0,
    @CreateCheckConstraintInTwoSteps BIT = 0,
    @CreateFunction BIT = 0,
    @CreateProcedure BIT = 0,
    @CreateTrigger BIT = 0,
    @CreateView BIT = 0,
    @AlterProcedures BIT = 0,
    @CreateSETANSI BIT = 0,
    @IgnoreDisabledForeignKey BIT = 0,
    @IgnoreDisabledCheckConstraint BIT = 0,
    @SortInTempDb BIT = 1,
    @Fillfactor TINYINT = NULL,
    @Online BIT = 0,
    @AllowRowLocks BIT = NULL,
    @AllowPageLocks BIT = NULL,
    @PadIndex BIT = NULL,
    @MAXDOP TINYINT = 0,
    @SortTableColumns BIT = 0,
    @GenerateIfNotExists BIT = 0,
    @UseGOBatchTerminator BIT = 0,
    @IncludeMSShipped BIT = 0,
    @ExcludeSchemaList VARCHAR(MAX) = 'cdc,sys,Metadata',
    @ExcludeSchemaDelimiter VARCHAR(10) = ',',
    @SQL VARCHAR(MAX) = NULL OUTPUT, /* This will append to the passed value */
    @PrintSQL BIT = 0,
    @ReturnRecordset BIT = 0,
    @ExecuteSQL BIT = 0, /* Executes the generated sql statement */
    @UseTransaction BIT = 0, /* SQL script will be executed within a transaction */
    @RtrimLines BIT = 1,
    @UseObjectIdForDrop BIT = 1,
    @recipients NVARCHAR(1000) = NULL,
    @subject NVARCHAR(1000) = NULL,
    @FilePath VARCHAR(4000) = NULL /* Will write the generated sql to the path */
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ANSI_WARNINGS OFF

--#region SET BACK NULLS TO DEFAULTS
SELECT  @DropForeignKey = ISNULL(@DropForeignKey, 0),
        @DropTable = ISNULL(@DropTable, 0),
        @DropDefaultConstraints = ISNULL(@DropDefaultConstraints, 0),
        @DropClusteredIndex = ISNULL(@DropClusteredIndex, 0),
        @DropNonClusteredIndex = ISNULL(@DropNonClusteredIndex, 0),
        @DropPrimaryKey = ISNULL(@DropPrimaryKey, 0),
        @DropUniqueKey = ISNULL(@DropUniqueKey, 0),
        @DropIndex = ISNULL(@DropIndex, 0),
        @DropCheckConstraint = ISNULL(@DropCheckConstraint, 0),
        @DropFunction = ISNULL(@DropFunction, 0),
        @DropProcedure = ISNULL(@DropProcedure, 0),
        @DropTrigger = ISNULL(@DropTrigger, 0),
        @DropView = ISNULL(@DropView, 0),
        @CreateTable = ISNULL(@CreateTable, 0),
        @CreateTableInOneStep = ISNULL(@CreateTableInOneStep, 0),
        @IncludeSchemaBoundObjects = ISNULL(@IncludeSchemaBoundObjects, 0),
        @CreateDefaultConstraints = ISNULL(@CreateDefaultConstraints, 0),
        @EnableNonClusteredIndex = ISNULL(@EnableNonClusteredIndex, 0),
        @EnableUniqueKey = ISNULL(@EnableUniqueKey, 0),
        @EnableIndex = ISNULL(@EnableIndex, 0),
        @DisableNonClusteredIndex = ISNULL(@DisableNonClusteredIndex, 0),
        @DisableUniqueKey = ISNULL(@DisableUniqueKey, 0),
        @DisableIndex = ISNULL(@DisableIndex, 0),
        @CreateClusteredIndex = ISNULL(@CreateClusteredIndex, 0),
        @CreateNonClusteredIndex = ISNULL(@CreateNonClusteredIndex, 0),
        @CreatePrimaryKey = ISNULL(@CreatePrimaryKey, 0),
        @CreateUniqueKey = ISNULL(@CreateUniqueKey, 0),
        @CreateIndex = ISNULL(@CreateIndex, 0),
        @CreateForeignKey = ISNULL(@CreateForeignKey, 0),
        @CreateForeignKeyInTwoSteps = ISNULL(@CreateForeignKeyInTwoSteps, 0),
        @CreateCheckConstraint = ISNULL(@CreateCheckConstraint, 0),
        @CreateCheckConstraintInTwoSteps = ISNULL(@CreateCheckConstraintInTwoSteps, 0),
        @CreateFunction = ISNULL(@CreateFunction, 0),
        @CreateProcedure = ISNULL(@CreateProcedure, 0),
        @CreateTrigger = ISNULL(@CreateTrigger, 0),
        @CreateView = ISNULL(@CreateView, 0),
        @AlterProcedures = ISNULL(@AlterProcedures, 0),
        @CreateSETANSI = ISNULL(@CreateSETANSI, 0),
        @IgnoreDisabledForeignKey = ISNULL(@IgnoreDisabledForeignKey, 0),
        @IgnoreDisabledCheckConstraint = ISNULL(@IgnoreDisabledCheckConstraint, 0),
        @SortInTempDb = ISNULL(@SortInTempDb, 1),
        @Online = ISNULL(@Online, 0),
        @MAXDOP = ISNULL(@MAXDOP, 0),
        @SortTableColumns = ISNULL(@SortTableColumns, 0),
        @GenerateIfNotExists = ISNULL(@GenerateIfNotExists, 0),
        @UseGOBatchTerminator = ISNULL(@UseGOBatchTerminator, 0),
        @IncludeMSShipped = ISNULL(@IncludeMSShipped, 0),
        @ExcludeSchemaList = ISNULL(@ExcludeSchemaList, 'cdc,sys'),
        @ExcludeSchemaDelimiter = ISNULL(NULLIF(Util.dbo.TrimBothEnds(@ExcludeSchemaDelimiter), ''), ','),
        @PrintSQL = ISNULL(@PrintSQL, 0),
        @ReturnRecordset = ISNULL(@ReturnRecordset, 0),
        @ExecuteSQL = ISNULL(@ExecuteSQL, 0),
        @UseTransaction = ISNULL(@UseTransaction, 0),
        @RtrimLines = ISNULL(@RtrimLines, 1),
        @UseObjectIdForDrop = ISNULL(@UseObjectIdForDrop, 1)
--#endregion


DECLARE @IndexEnabledError VARCHAR(MAX) = SUBSTRING(CASE WHEN (@EnableNonClusteredIndex = 1
                                                               AND @DisableNonClusteredIndex = 1)
                                                         THEN ',@EnableNonClusteredIndex AND @DisableNonClusteredIndex SET'
                                                         ELSE ''
                                                    END + CASE WHEN (@EnableUniqueKey = 1
                                                                     AND @DisableUniqueKey = 1) THEN ',@EnableUniqueKey AND @DisableUniqueKey SET'
                                                               ELSE ''
                                                          END + CASE WHEN (@EnableIndex = 1
                                                                           AND @DisableIndex = 1) THEN ',@EnableIndex AND @DisableIndex SET'
                                                                     ELSE ''
                                                                END, 2, 1000),
    @IgnoreListCount INT = 0
IF @IndexEnabledError <> ''
    BEGIN
        RAISERROR(@IndexEnabledError, 16, 1)
        RETURN 30
    END

IF OBJECT_ID('TEMPDB..#IgnoreList') IS NULL
    CREATE TABLE #IgnoreList (OBJECT_ID INT NOT NULL
                                            PRIMARY KEY CLUSTERED)
ELSE
    SELECT  @IgnoreListCount = COUNT(*)
    FROM    #IgnoreList

DECLARE @SilentOperation BIT = CASE WHEN OBJECT_ID('tempdb..#sp_script') IS NOT NULL THEN 1
                                    ELSE 0
                               END
SELECT  @ObjectName = Util.dbo.TrimBothEnds(@ObjectName),
        @CreateTableInOneStep = CASE WHEN @CreateTable = 1 THEN @CreateTableInOneStep
                                     ELSE 0
                                END
IF @SilentOperation = 0
    CREATE TABLE #sp_script (OrderId INT NOT NULL
                                         PRIMARY KEY CLUSTERED,
                             SchemaName SYSNAME NOT NULL,
                             TableName SYSNAME NOT NULL,
                             ObjectName VARCHAR(256) NULL,
                             TYPE VARCHAR(2) NOT NULL,
                             CreateScript BIT NULL,
                             IndexId INT NOT NULL,
                             Script VARCHAR(MAX) NULL)

/* @ObjectList XML Format
-- SELECT s.name AS schemaname, t.name FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name = 'ttrans' FOR XML RAW('Tables')


















*/
IF ISNULL(@DropForeignKey, 0) = 0
    AND ISNULL(@DropTable, 0) = 0
    AND ISNULL(@CreateTable, 0) = 0
    AND ISNULL(@EnableNonClusteredIndex, 0) = 0
    AND ISNULL(@EnableUniqueKey, 0) = 0
    AND ISNULL(@EnableIndex, 0) = 0
    AND ISNULL(@DisableNonClusteredIndex, 0) = 0
    AND ISNULL(@DisableUniqueKey, 0) = 0
    AND ISNULL(@DisableIndex, 0) = 0
    AND ISNULL(@DropIndex, 0) = 0
    AND ISNULL(@DropClusteredIndex, 0) = 0
    AND ISNULL(@DropNonClusteredIndex, 0) = 0
    AND ISNULL(@DropPrimaryKey, 0) = 0
    AND ISNULL(@DropUniqueKey, 0) = 0
    AND ISNULL(@DropDefaultConstraints, 0) = 0
    AND ISNULL(@CreateDefaultConstraints, 0) = 0
    AND ISNULL(@CreatePrimaryKey, 0) = 0
    AND ISNULL(@CreateUniqueKey, 0) = 0
    AND ISNULL(@CreateIndex, 0) = 0
    AND ISNULL(@CreateClusteredIndex, 0) = 0
    AND ISNULL(@CreateNonClusteredIndex, 0) = 0
    AND ISNULL(@CreateForeignKey, 0) = 0
    AND ISNULL(@DropCheckConstraint, 0) = 0
    AND ISNULL(@DropTrigger, 0) = 0
    AND ISNULL(@CreateCheckConstraint, 0) = 0
    AND ISNULL(@CreateTrigger, 0) = 0
    AND ISNULL(@DropFunction, 0) = 0
    AND ISNULL(@DropProcedure, 0) = 0
    AND ISNULL(@DropTrigger, 0) = 0
    AND ISNULL(@DropView, 0) = 0
    AND ISNULL(@CreateFunction, 0) = 0
    AND ISNULL(@CreateProcedure, 0) = 0
    AND ISNULL(@CreateTrigger, 0) = 0
    AND ISNULL(@CreateView, 0) = 0
    BEGIN
        PRINT '/* ****************** DESCRIPTION OF PARAMETERS ******************
    @ObjectName sysname = NULL, -- Fully qualified name or NULL for all, supports like
    @SchemaName sysname = NULL    -- NULL for all, supports like
    @ObjectList XML = NULL --  
    @NewSchemaName sysname = NULL    -- NULL to scripts objects in original schema, any value overwrites
    @NewTableName sysname = NULL    -- This can be used if only one table is scripted, it will change the table name
    @NewDatabaseName sysname = NULL    -- To change the database context
    @NewFileGroup sysname = NULL    -- A new filegroup for all objects created can be given or a full partition schema with column)
    @NewPartition sysname = NULL    -- Full partition clause with the parameter like ''psDatasupplierIdTrans(DataSupplierId)''
    @NewDataCompression sysname = NULL    -- A blank value will not script datacompression options, any value will create all objects with the new data compression
    @AddNamePrefix sysname = NULL    -- Adds a prefix to table and constraint names being created
    @ReplaceReferenceSchemaFrom sysname = NULL    -- ** For foreign key constrainst you can replace a referenced schema name @ReplaceReferenceSchemaFrom as @ReplaceReferenceSchemaTo
    @ReplaceReferenceSchemaTo sysname = NULL    -- Used in conjunction with @ReplaceReferenceSchemaFrom

****************** DESCRIPTION OF PARAMETERS ****************** */

-- Using #IgnoreList and #ObjectList Temp tables the input object list can be further customized

/*
CREATE TABLE #IgnoreList(
    object_id int not null,
    PRIMARY KEY CLUSTERED(object_id))

CREATE TABLE #ObjectList(
    SchemaName varchar(256) NULL,
    Name varchar(256) NULL
    UNIQUE CLUSTERED (SchemaName, Name))

INSERT #ObjectList (SchemaName, Name)
VALUES (''a'',''bb'')
*/

-- If #sp_script exists the script will be stored in that table
/*
IF OBJECT_ID(''TEMPDB..#sp_script'') IS NOT NULL DROP TABLE #sp_script

CREATE TABLE #sp_script (OrderId INT NOT NULL PRIMARY KEY CLUSTERED,
                         SchemaName SYSNAME NOT NULL,
                         TableName SYSNAME NOT NULL,
                         ObjectName VARCHAR(256) NULL,
                         TYPE VARCHAR(2) NOT NULL,
                         CreateScript BIT NULL,
                         IndexId int NOT NULL,
                         Script VARCHAR(MAX) NULL)
*/
'
        EXEC master.dbo.sp_ExecTemplate
            @ObjectName = 'sp_script',
            @PrintDefault = 0,
            @DeclareReturn = 0,
            @PrintReturnLine = 0,
            @DoNotDeclareVar = 1
        RETURN 2
    END
DECLARE @BatchTerminator VARCHAR(10) = CASE WHEN (@UseGOBatchTerminator = 1
                                                  AND ISNULL(@ExecuteSQL, 0) = 0)
                                                 OR (ISNULL(@ReturnRecordset, 0) = 0
                                                     AND (@CreateFunction = 1
                                                          OR @CreateProcedure = 1
                                                          OR @CreateTrigger = 1
                                                          OR @CreateView = 1)) THEN 'GO
'                                           ELSE ''
                                       END

DECLARE @ObjectTypes TABLE (TYPE VARCHAR(10) COLLATE Latin1_General_CI_AS_KS_WS
                                             NOT NULL
                                             PRIMARY KEY CLUSTERED,
                            TypeName VARCHAR(30) NOT NULL,
                            OnlySchemaBound BIT NOT NULL)
INSERT  @ObjectTypes
        (TYPE,
         TypeName,
         OnlySchemaBound)
        SELECT  'FN' AS TYPE,
                'FUNCTION' AS TypeName,
                0 AS OnlySchemaBound
        WHERE   (@DropFunction = 1
                 OR @CreateFunction = 1)
        UNION ALL
        SELECT  'IF' AS TYPE,
                'FUNCTION' AS TypeName,
                CASE WHEN @DropFunction = 1
                          OR @CreateFunction = 1 THEN 0
                     ELSE 1
                END AS OnlySchemaBound
        WHERE   (@DropFunction = 1
                 OR @CreateFunction = 1)
                OR (@CreateTable = 1
                    AND @IncludeSchemaBoundObjects = 1)
        UNION ALL
        SELECT  'TF' AS TYPE,
                'FUNCTION' AS TypeName,
                0 AS OnlySchemaBound
        WHERE   (@DropFunction = 1
                 OR @CreateFunction = 1)
        UNION ALL
        SELECT  'P' AS TYPE,
                'PROCEDURE' AS TypeName,
                0 AS OnlySchemaBound
        WHERE   (@CreateProcedure = 1
                 OR @DropProcedure = 1)
        UNION ALL
        SELECT  'V' AS TYPE,
                'VIEW' AS TypeName,
                CASE WHEN @DropView = 1
                          OR @CreateView = 1 THEN 0
                     ELSE 1
                END AS OnlySchemaBound
        WHERE   (@DropView = 1
                 OR @CreateView = 1
                 OR (@CreateTable = 1
                     AND @IncludeSchemaBoundObjects = 1))
        UNION ALL
        SELECT  'TR' AS TYPE,
                'TRIGGER' AS TypeName,
                CASE WHEN @DropTrigger = 1
                          OR @CreateTrigger = 1 THEN 0
                     ELSE 1
                END AS OnlySchemaBound
        WHERE   (@DropTrigger = 1
                 OR @CreateTrigger = 1)
                OR (@CreateTable = 1
                    AND @IncludeSchemaBoundObjects = 1)
        UNION ALL
        --SELECT  'D' AS TYPE,
        --        'DEFAULT' AS TypeName
        --UNION ALL
        SELECT  'U' AS TYPE,
                'TABLE' AS TypeName,
                0 AS OnlySchemaBound
        WHERE   (@DropTable = 1
                 OR @CreateTable = 1
                 OR @EnableNonClusteredIndex = 1
                 OR @EnableUniqueKey = 1
                 OR @EnableIndex = 1
                 OR @DisableNonClusteredIndex = 1
                 OR @DisableUniqueKey = 1
                 OR @DisableIndex = 1
                 OR @DropForeignKey = 1
                 OR @DropDefaultConstraints = 1
                 OR @DropIndex = 1
                 OR @DropClusteredIndex = 1
                 OR @DropNonClusteredIndex = 1
                 OR @DropPrimaryKey = 1
                 OR @DropUniqueKey = 1
                 OR @DropCheckConstraint = 1
                 OR @CreateDefaultConstraints = 1
                 OR @CreateClusteredIndex = 1
                 OR @CreateNonClusteredIndex = 1
                 OR @CreatePrimaryKey = 1
                 OR @CreateUniqueKey = 1
                 OR @CreateIndex = 1
                 OR @CreateForeignKey = 1
                 OR @CreateCheckConstraint = 1)

IF OBJECT_ID('tempdb..#Objects') IS NOT NULL
    DROP TABLE #Objects
CREATE TABLE #Objects (OBJECT_ID INT NOT NULL
                                     PRIMARY KEY CLUSTERED,
                       SchemaName SYSNAME NOT NULL,
                       ObjectName SYSNAME NOT NULL,
                       TYPE CHAR(2) NOT NULL,
                       TypeName VARCHAR(30) NOT NULL,
                       lock_escalation_desc VARCHAR(100) NULL,
                       uses_ansi_nulls BIT NULL)

IF @ObjectList IS NOT NULL
    OR OBJECT_ID('tempdb..#ObjectList') IS NOT NULL
    BEGIN
        DECLARE @ObjectIncludeList TABLE (SchemaName SYSNAME NULL,
                                          ObjectName SYSNAME NULL)
        DECLARE @ObjectExludeList TABLE (SchemaName SYSNAME NULL,
                                         ObjectName SYSNAME NULL)
        IF @ObjectList IS NOT NULL
            INSERT  @ObjectIncludeList
                    (SchemaName,
                     ObjectName)
                    SELECT DISTINCT
                            t.item.value('@schemaname', 'sysname') AS SchemaName,
                            t.item.value('@name', 'sysname') AS ObjectName
                    FROM    @ObjectList.nodes('/Tables') AS t (item)
        ELSE
            INSERT  @ObjectIncludeList
                    (SchemaName,
                     ObjectName)
                    SELECT DISTINCT
                            SchemaName,
                            Name
                    FROM    #ObjectList ;
        WITH    s1
                  AS (SELECT    tb.OBJECT_ID,
                                ISNULL(@NewSchemaName, sc.name) AS SchemaName,
                                ISNULL(@AddNamePrefix, '') + ISNULL(@NewTableName, tb.name) AS ObjectName,
                                tb.type COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS TYPE,
                                ot.TypeName,
                                ISNULL(tt.lock_escalation_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, '') AS lock_escalation_desc,
                                tt.uses_ansi_nulls
                      FROM      sys.schemas AS sc WITH (NOLOCK)
                      INNER JOIN sys.objects AS tb WITH (NOLOCK) ON sc.SCHEMA_ID = tb.SCHEMA_ID
                      INNER JOIN @ObjectTypes ot ON ot.TYPE = tb.Type
                      LEFT OUTER JOIN sys.tables tt WITH (NOLOCK) ON tt.OBJECT_ID = tb.OBJECT_ID
                      WHERE     ot.OnlySchemaBound = 0
                                AND EXISTS ( SELECT *
                                             FROM   @ObjectIncludeList tl
                                             WHERE  (tl.SchemaName IS NULL
                                                     OR tl.SchemaName = sc.name)
                                                    AND (tl.ObjectName IS NULL
                                                         OR tl.ObjectName = tb.name) )
                                AND (@IgnoreListCount = 0
                                     OR (@IgnoreListCount > 0
                                         AND NOT EXISTS ( SELECT    *
                                                          FROM      #IgnoreList il
                                                          WHERE     il.OBJECT_ID = TB.OBJECT_ID ))))
            INSERT  #Objects WITH (TABLOCK)
                    (OBJECT_ID,
                     SchemaName,
                     ObjectName,
                     TYPE,
                     TypeName,
                     lock_escalation_desc,
                     uses_ansi_nulls)
                    SELECT  OBJECT_ID,
                            SchemaName,
                            ObjectName,
                            TYPE,
                            TypeName,
                            lock_escalation_desc,
                            uses_ansi_nulls
                    FROM    (SELECT OBJECT_ID,
                                    ROW_NUMBER() OVER (PARTITION BY OBJECT_ID ORDER BY (SELECT 0)) AS ROW,
                                    SchemaName,
                                    ObjectName,
                                    TYPE,
                                    TypeName,
                                    lock_escalation_desc,
                                    uses_ansi_nulls
                             FROM   (SELECT OBJECT_ID,
                                            SchemaName,
                                            ObjectName,
                                            TYPE,
                                            TypeName,
                                            lock_escalation_desc,
                                            uses_ansi_nulls
                                     FROM   s1
                                     UNION ALL
                                     SELECT o.OBJECT_ID,
                                            ISNULL(@NewSchemaName, s.name) AS SchemaName,
                                            ISNULL(@AddNamePrefix, '') + ISNULL(@NewTableName, o.name) AS ObjectName,
                                            o.type COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS TYPE,
                                            ot.TypeName,
                                            '' AS lock_escalation_desc,
                                            sm.uses_ansi_nulls
                                     FROM   sys.sql_expression_dependencies ed (NOLOCK)
                                     INNER  JOIN sys.objects o (NOLOCK) ON ed.referencing_id = o.OBJECT_ID
                                     INNER JOIN sys.sql_modules sm (NOLOCK) ON sm.OBJECT_ID = o.OBJECT_ID
                                     INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
                                     INNER JOIN s1 ON ed.referenced_id = s1.OBJECT_ID
                                     INNER JOIN @ObjectTypes ot ON ot.TYPE = o.Type
                                                                   AND ot.OnlySchemaBound = 1
                                     WHERE  ed.is_schema_bound_reference = 1
                                            AND ed.referencing_class = 1
                                            AND ed.referenced_minor_id = 0
                                            AND o.type IN ('V', 'FN', 'IF', 'TF', 'P')
                                            AND @IncludeSchemaBoundObjects = 1
                                            AND @CreateTable = 1) k) K
                    WHERE   ROW = 1
            OPTION  (RECOMPILE)
    END
ELSE
    IF (@ObjectName LIKE '#%'
        AND OBJECT_ID(@ObjectName) IS NOT NULL)
        OR (PARSENAME(@ObjectName, 1) <> ''
            AND (PARSENAME(@ObjectName, 2) <> ''
                 OR (PARSENAME(@ObjectName, 3) <> ''
                     AND @ObjectName LIKE '%..%')))
        BEGIN
        ;
            WITH    s1
                      AS (SELECT    tb.OBJECT_ID,
                                    ISNULL(@NewSchemaName, sc.name) AS SchemaName,
                                    ISNULL(@AddNamePrefix, '') + ISNULL(@NewTableName, tb.name) AS ObjectName,
                                    tb.type COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS TYPE,
                                    ot.TypeName,
                                    ISNULL(tt.lock_escalation_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, '') AS lock_escalation_desc,
                                    tt.uses_ansi_nulls
                          FROM      sys.schemas AS sc WITH (NOLOCK)
                          INNER JOIN sys.objects AS tb WITH (NOLOCK) ON sc.SCHEMA_ID = tb.SCHEMA_ID
                          INNER JOIN @ObjectTypes ot ON ot.TYPE = tb.Type
                                                        AND ot.OnlySchemaBound = 0
                          LEFT OUTER JOIN sys.tables tt WITH (NOLOCK) ON tt.OBJECT_ID = tb.OBJECT_ID
                          WHERE     tb.OBJECT_ID = OBJECT_ID(@ObjectName)
                                    AND (@IgnoreListCount = 0
                                         OR (@IgnoreListCount > 0
                                             AND NOT EXISTS ( SELECT    *
                                                              FROM      #IgnoreList il
                                                              WHERE     il.OBJECT_ID = TB.OBJECT_ID ))))
                INSERT  #Objects WITH (TABLOCK)
                        (OBJECT_ID,
                         SchemaName,
                         ObjectName,
                         TYPE,
                         TypeName,
                         lock_escalation_desc,
                         tt.uses_ansi_nulls)
                        SELECT  OBJECT_ID,
                                SchemaName,
                                ObjectName,
                                TYPE,
                                TypeName,
                                lock_escalation_desc,
                                uses_ansi_nulls
                        FROM    (SELECT OBJECT_ID,
                                        ROW_NUMBER() OVER (PARTITION BY OBJECT_ID ORDER BY (SELECT 0)) AS ROW,
                                        SchemaName,
                                        ObjectName,
                                        TYPE,
                                        TypeName,
                                        lock_escalation_desc,
                                        uses_ansi_nulls
                                 FROM   (SELECT OBJECT_ID,
                                                SchemaName,
                                                ObjectName,
                                                TYPE,
                                                TypeName,
                                                lock_escalation_desc,
                                                uses_ansi_nulls
                                         FROM   s1
                                         UNION ALL
                                         SELECT o.OBJECT_ID,
                                                ISNULL(@NewSchemaName, s.name) AS SchemaName,
                                                ISNULL(@AddNamePrefix, '') + ISNULL(@NewTableName, o.name) AS ObjectName,
                                                o.type COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS TYPE,
                                                ot.TypeName,
                                                '' AS lock_escalation_desc,
                                                sm.uses_ansi_nulls
                                         FROM   sys.sql_expression_dependencies ed (NOLOCK)
                                         INNER  JOIN sys.objects o (NOLOCK) ON ed.referencing_id = o.OBJECT_ID
                                         INNER JOIN sys.sql_modules sm (NOLOCK) ON sm.OBJECT_ID = o.OBJECT_ID
                                         INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
                                         INNER JOIN s1 ON ed.referenced_id = s1.OBJECT_ID
                                         INNER JOIN @ObjectTypes ot ON ot.TYPE = o.Type
                                                                       AND ot.OnlySchemaBound = 1
                                         WHERE  ed.is_schema_bound_reference = 1
                                                AND ed.referencing_class = 1
                                                AND ed.referenced_minor_id = 0
                                                AND o.type IN ('V', 'FN', 'IF', 'TF', 'P')
                                                AND @IncludeSchemaBoundObjects = 1
                                                AND @CreateTable = 1) k) K
                        WHERE   ROW = 1
                OPTION  (RECOMPILE)
        END
    ELSE
        BEGIN
        ;
            WITH    s1
                      AS (SELECT    tb.OBJECT_ID,
                                    ISNULL(@NewSchemaName, sc.name) AS SchemaName,
                                    ISNULL(@AddNamePrefix, '') + ISNULL(@NewTableName, tb.name) AS ObjectName,
                                    tb.type COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS TYPE,
                                    ot.TypeName,
                                    ISNULL(tt.lock_escalation_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, '') AS lock_escalation_desc,
                                    tt.uses_ansi_nulls
                          FROM      sys.schemas AS sc WITH (NOLOCK)
                          INNER JOIN sys.objects AS tb WITH (NOLOCK) ON sc.SCHEMA_ID = tb.SCHEMA_ID
                          INNER JOIN @ObjectTypes ot ON ot.TYPE = tb.Type
                                                        AND ot.OnlySchemaBound = 0
                          LEFT OUTER JOIN sys.tables tt WITH (NOLOCK) ON tt.OBJECT_ID = tb.OBJECT_ID
                          WHERE     (@SchemaName IS NULL
                                     OR sc.name LIKE Util.dbo.TrimBothEnds(@SchemaName))
                                    AND (@ObjectName IS NULL
                                         OR tb.name LIKE @ObjectName)
                                    AND (@IncludeMSShipped = 1
                                         OR tb.is_ms_shipped = 0)
                                    AND NOT EXISTS ( SELECT *
                                                     FROM   Util.dbo.ParseDelimited(@ExcludeSchemaList, @ExcludeSchemaDelimiter) pp
                                                     WHERE  pp.Field = sc.name )
                                    AND (@IgnoreListCount = 0
                                         OR (@IgnoreListCount > 0
                                             AND NOT EXISTS ( SELECT    *
                                                              FROM      #IgnoreList il
                                                              WHERE     il.OBJECT_ID = TB.OBJECT_ID ))))
                INSERT  #Objects WITH (TABLOCK)
                        (OBJECT_ID,
                         SchemaName,
                         ObjectName,
                         TYPE,
                         TypeName,
                         lock_escalation_desc,
                         uses_ansi_nulls)
                        SELECT  OBJECT_ID,
                                SchemaName,
                                ObjectName,
                                TYPE,
                                TypeName,
                                lock_escalation_desc,
                                uses_ansi_nulls
                        FROM    (SELECT OBJECT_ID,
                                        ROW_NUMBER() OVER (PARTITION BY OBJECT_ID ORDER BY (SELECT 0)) AS ROW,
                                        SchemaName,
                                        ObjectName,
                                        TYPE,
                                        TypeName,
                                        lock_escalation_desc,
                                        uses_ansi_nulls
                                 FROM   (SELECT OBJECT_ID,
                                                SchemaName,
                                                ObjectName,
                                                TYPE,
                                                TypeName,
                                                lock_escalation_desc,
                                                uses_ansi_nulls
                                         FROM   s1
                                         UNION ALL
                                         SELECT o.OBJECT_ID,
                                                ISNULL(@NewSchemaName, s.name) AS SchemaName,
                                                ISNULL(@AddNamePrefix, '') + ISNULL(@NewTableName, o.name) AS ObjectName,
                                                o.type COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS TYPE,
                                                ot.TypeName,
                                                '' AS lock_escalation_desc,
                                                sm.uses_ansi_nulls
                                         FROM   sys.sql_expression_dependencies ed (NOLOCK)
                                         INNER  JOIN sys.objects o (NOLOCK) ON ed.referencing_id = o.OBJECT_ID
                                         INNER JOIN sys.sql_modules sm (NOLOCK) ON sm.OBJECT_ID = o.OBJECT_ID
                                         INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
                                         INNER JOIN s1 ON ed.referenced_id = s1.OBJECT_ID
                                         INNER JOIN @ObjectTypes ot ON ot.TYPE = o.Type
                                                                       AND ot.OnlySchemaBound = 1
                                         WHERE  ed.is_schema_bound_reference = 1
                                                AND ed.referencing_class = 1
                                                AND ed.referenced_minor_id = 0
                                                AND o.type IN ('V', 'FN', 'IF', 'TF', 'P')
                                                AND @IncludeSchemaBoundObjects = 1
                                                AND @CreateTable = 1) k) k
                        WHERE   ROW = 1
                OPTION  (RECOMPILE)
        END
IF @IncludeSchemaBoundObjects = 1
    AND @CreateTable = 1
    SELECT  @CreateTrigger = ISNULL(Trg, @CreateTrigger),
            @CreateFunction = ISNULL(Fnc, @CreateFunction),
            @CreateProcedure = ISNULL(prc, @CreateProcedure),
            @CreateView = ISNULL(vw, @CreateView)
    FROM    (SELECT MAX(CASE WHEN TypeName = 'TRIGGER' THEN 1
                        END) AS Trg,
                    MAX(CASE WHEN TypeName = 'FUNCTION' THEN 1
                        END) AS Fnc,
                    MAX(CASE WHEN TypeName = 'PROCEDURE' THEN 1
                        END) AS Prc,
                    MAX(CASE WHEN TypeName = 'VIEW' THEN 1
                        END) vw
             FROM   #Objects) k ;
WITH    TableInfo
          AS (SELECT    tl.OBJECT_ID,
                        SchemaName,
                        ObjectName,
                        tl.lock_escalation_desc,
                        CASE WHEN @NewPartition <> '' THEN 'ON ' + @NewPartition
                             WHEN @NewFileGroup = '' THEN ''
                             ELSE ISNULL('ON ' + QUOTENAME(@NewFileGroup), CASE ds.type
                                                                             WHEN 'FG' THEN 'ON ' + QUOTENAME(ds.NAME)
                                                                             WHEN 'PS' THEN 'ON ' + QUOTENAME(ds.NAME) + '(' + QUOTENAME(pc.NAME) + ')'
                                                                             WHEN 'FD' THEN ''
                                                                             ELSE ''
                                                                           END)
                        END AS FileGroup,
                        CASE WHEN @CreateTableInOneStep = 1 THEN ''
                             ELSE CASE WHEN @NewDataCompression = '' THEN ''
                                       ELSE ISNULL('WITH (DATA_COMPRESSION = ' + @NewDataCompression + ')', CASE pr.data_compression
                                                                                                              WHEN 0 THEN ''
                                                                                                              WHEN 1 THEN ' WITH (DATA_COMPRESSION = ROW)'
                                                                                                              WHEN 2 THEN ' WITH (DATA_COMPRESSION = PAGE)'
                                                                                                              ELSE ''
                                                                                                            END)
                                  END
                        END AS DataCompression
              FROM      #Objects AS tl
              INNER JOIN sys.indexes AS ix WITH (NOLOCK) ON tl.OBJECT_ID = ix.OBJECT_ID
                                                            AND ix.index_id <= 1
              INNER JOIN sys.data_spaces AS ds WITH (NOLOCK) ON ds.data_space_id = ix.data_space_id
              LEFT OUTER JOIN sys.partitions AS pr WITH (NOLOCK) ON pr.OBJECT_ID = ix.OBJECT_ID
                                                                    AND pr.index_id = ix.index_id
                                                                    AND pr.partition_number = 1
              LEFT OUTER JOIN sys.index_columns AS ic WITH (NOLOCK) ON ic.OBJECT_ID = ix.OBJECT_ID
                                                                       AND ic.index_id = ix.index_id
                                                                       AND ic.partition_ordinal = 1
              LEFT OUTER JOIN sys.columns AS pc WITH (NOLOCK) ON pc.OBJECT_ID = ic.OBJECT_ID
                                                                 AND pc.column_id = ic.column_id
              WHERE     tl.type = 'U'
                        AND @CreateTable = 1),
        ForeignKeyNames
          AS (SELECT    tl.OBJECT_ID,
                        tl.SchemaName,
                        tl.ObjectName AS TableName,
                        ISNULL(@AddNamePrefix, '') + fk.name AS KeyName
              FROM      #Objects AS tl
              INNER JOIN sys.foreign_keys AS fk WITH (NOLOCK) ON fk.parent_object_id = tl.OBJECT_ID
              WHERE     @DropForeignKey = 1
                        AND tl.TYPE = 'U'
                        AND (@IgnoreListCount = 0
                             OR (@IgnoreListCount > 0
                                 AND NOT EXISTS ( SELECT    *
                                                  FROM      #IgnoreList il
                                                  WHERE     il.OBJECT_ID = fk.OBJECT_ID )))
                        AND ((@IgnoreDisabledForeignKey = 1
                              AND fk.is_disabled = 0)
                             OR ISNULL(@IgnoreDisabledForeignKey, 0) = 0)),
        Procedures
          AS (SELECT    tl.OBJECT_ID,
                        tl.SchemaName,
                        tl.ObjectName,
                        tl.TypeName,
                        tl.type COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS TYPE,
                        CASE WHEN @CreateSETANSI = 1 THEN 'SET ANSI_NULLS ' + CASE WHEN sm.uses_ansi_nulls = 1 THEN 'ON'
                                                                                   ELSE 'OFF'
                                                                              END + '
GO
SET QUOTED_IDENTIFIER ' + CASE WHEN sm.uses_quoted_identifier = 1 THEN 'ON'
                               ELSE 'OFF'
                          END + '
GO
'                            ELSE ''
                        END + CASE WHEN @AlterProcedures = 1 THEN Util.dbo.GenerateAlterScript(sm.definition)
                                   ELSE Util.dbo.TrimMultiLine(sm.definition)
                              END AS ObjectDefinition
              FROM      #Objects tl
              INNER JOIN sys.sql_modules AS sm (NOLOCK) ON sm.OBJECT_ID = tl.OBJECT_ID
              WHERE     (@DropFunction = 1
                         OR @CreateFunction = 1
                         OR @DropView = 1
                         OR @CreateView = 1
                         OR @DropProcedure = 1
                         OR @CreateProcedure = 1)
                        AND tl.TYPE IN ('FN', 'IF', 'TF', 'P', 'V')),
        Triggers
          AS (SELECT    tl.OBJECT_ID,
                        s.name AS SchemaName,
                        tl.ObjectName,
                        tr.NAME AS TriggerName,
                        CASE WHEN @CreateSETANSI = 1 THEN 'SET ANSI_NULLS ' + CASE WHEN sm.uses_ansi_nulls = 1 THEN 'ON'
                                                                                   ELSE 'OFF'
                                                                              END + '
GO
SET QUOTED_IDENTIFIER ' + CASE WHEN sm.uses_quoted_identifier = 1 THEN 'ON'
                               ELSE 'OFF'
                          END + '
GO
'                            ELSE ''
                        END + CASE WHEN @AlterProcedures = 1 THEN Util.dbo.GenerateAlterScript(sm.definition)
                                   ELSE Util.dbo.TrimMultiLine(sm.definition)
                              END AS TriggerDefinition
              FROM      #Objects tl
              INNER JOIN sys.triggers tr (NOLOCK) ON tr.parent_id = tl.OBJECT_ID
              INNER JOIN sys.objects o (NOLOCK) ON o.OBJECT_ID = tr.OBJECT_ID
              INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
              INNER JOIN sys.sql_modules AS sm (NOLOCK) ON sm.OBJECT_ID = tr.OBJECT_ID
              WHERE     (@DropTrigger = 1
                         OR @CreateTrigger = 1)
                        AND (@IgnoreListCount = 0
                             OR (@IgnoreListCount > 0
                                 AND NOT EXISTS ( SELECT    *
                                                  FROM      #IgnoreList il
                                                  WHERE     il.OBJECT_ID = tr.OBJECT_ID )))),
        DefaultConstraintNames
          AS (SELECT    tl.OBJECT_ID,
                        tl.SchemaName,
                        tl.ObjectName AS TableName,
                        ISNULL(@AddNamePrefix, '') + dc.name AS ConstraintName,
                        c.name AS ColumnName,
                        definition
              FROM      sys.default_constraints AS dc WITH (NOLOCK)
              INNER JOIN #Objects AS tl ON tl.OBJECT_ID = dc.parent_object_id
              INNER JOIN sys.columns AS c WITH (NOLOCK) ON c.OBJECT_ID = dc.parent_object_id
                                                           AND dc.parent_column_id = c.column_id
              WHERE     (@DropDefaultConstraints = 1
                         OR (ISNULL(@CreateTable, 0) = 0
                             AND @CreateDefaultConstraints = 1))
                        AND (@IgnoreListCount = 0
                             OR (@IgnoreListCount > 0
                                 AND NOT EXISTS ( SELECT    *
                                                  FROM      #IgnoreList il
                                                  WHERE     il.OBJECT_ID = dc.OBJECT_ID )))),
        ForeignKeyColumns
          AS (SELECT    tl.OBJECT_ID,
                        tl.SchemaName,
                        tl.ObjectName AS TableName,
                        ISNULL(@AddNamePrefix, '') + fk.name AS KeyName,
                        ParentColumns,
                        CASE WHEN rs.name = @ReplaceReferenceSchemaFrom
                                  AND @ReplaceReferenceSchemaTo <> '' THEN @ReplaceReferenceSchemaTo
                             ELSE rs.name
                        END AS rSchemaName,
                        rt.name AS rTableName,
                        ReferencedColumns,
                        fk.delete_referential_action,
                        fk.delete_referential_action_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS delete_referential_action_desc,
                        fk.update_referential_action,
                        fk.update_referential_action_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS update_referential_action_desc,
                        fk.is_not_for_replication,
                        fk.is_disabled
              FROM      #Objects AS tl
              INNER JOIN sys.foreign_keys AS fk WITH (NOLOCK) ON fk.parent_object_id = tl.OBJECT_ID
              INNER JOIN sys.tables AS rt WITH (NOLOCK) ON rt.OBJECT_ID = fk.referenced_object_id
              INNER JOIN sys.schemas AS rs WITH (NOLOCK) ON rs.SCHEMA_ID = rt.SCHEMA_ID
              CROSS APPLY (SELECT   Util.dbo.StringConcat(QUOTENAME(ParentColumn), ', ') AS ParentColumns,
                                    Util.dbo.StringConcat(QUOTENAME(ReferencedColumn), ', ') AS ReferencedColumns
                           FROM     (SELECT TOP 999999
                                            pc.name AS ParentColumn,
                                            rc.name AS ReferencedColumn
                                     FROM   sys.foreign_key_columns fc (NOLOCK)
                                     INNER JOIN sys.columns pc (NOLOCK) ON pc.OBJECT_ID = fk.parent_object_id
                                                                           AND fc.parent_column_id = pc.column_id
                                     INNER JOIN sys.columns rc (NOLOCK) ON rc.OBJECT_ID = fk.referenced_object_id
                                                                           AND fc.referenced_column_id = rc.column_id
                                     WHERE  fc.constraint_object_id = fk.OBJECT_ID
                                     ORDER BY fc.constraint_column_id) k) fc
              WHERE     tl.Type = 'U'
                        AND (@IgnoreListCount = 0
                             OR (@IgnoreListCount > 0
                                 AND NOT EXISTS ( SELECT    *
                                                  FROM      #IgnoreList il
                                                  WHERE     il.OBJECT_ID = fk.OBJECT_ID )))
                        AND @CreateForeignKey = 1
                        AND ((@IgnoreDisabledForeignKey = 1
                              AND fk.is_disabled = 0)
                             OR ISNULL(@IgnoreDisabledForeignKey, 0) = 0)),
        IndexNames
          AS (SELECT    tl.OBJECT_ID,
                        tl.SchemaName,
                        tl.ObjectName,
                        CASE WHEN ix.is_primary_key = 1
                                  OR ix.is_unique_constraint = 1 THEN ISNULL(@AddNamePrefix, '') + ix.name
                             ELSE ix.name
                        END AS IndexName,
                        ix.is_primary_key,
                        ix.is_unique_constraint,
                        ix.index_id,
                        DropIndex
              FROM      #Objects AS tl
              INNER JOIN sys.indexes AS ix WITH (NOLOCK) ON tl.OBJECT_ID = ix.OBJECT_ID
              CROSS APPLY (SELECT   CASE WHEN (ISNULL(@DropPrimaryKey, 0) = 1
                                               AND ix.is_primary_key = 1)
                                              OR (ISNULL(@DropClusteredIndex, 0) = 1
                                                  AND ix.index_id = 1)
                                              OR (ISNULL(@DropNonClusteredIndex, 0) = 1
                                                  AND ix.index_id > 1)
                                              OR (ISNULL(@DropUniqueKey, 0) = 1
                                                  AND ix.is_unique_constraint = 1)
                                              OR (ISNULL(@DropIndex, 0) = 1
                                                  AND ix.is_unique_constraint = 0
                                                  AND ix.is_primary_key = 0) THEN 1
                                         ELSE 0
                                    END AS DropIndex) di
              WHERE     tl.Type IN ('U', 'V')
                        AND ix.index_id > 0
                        AND DropIndex = 1),
        IndexColumns
          AS (SELECT    tl.OBJECT_ID,
                        tl.SchemaName,
                        tl.ObjectName,
                        CASE WHEN ix.is_primary_key = 1
                                  OR ix.is_unique_constraint = 1 THEN ISNULL(@AddNamePrefix, '') + ix.name
                             ELSE ix.name
                        END AS IndexName,
                        ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
                        ds.type AS DataspaceType,
                        ds.name AS DataspaceName,
                        ix.index_id,
                        ix.is_unique,
                        ix.is_primary_key,
                        ix.is_unique_constraint,
                        ix.is_disabled,
                        ix.filter_definition,
                        ix.has_filter,
                        tl.uses_ansi_nulls,
                        dc.DataCompression,
                        ISNULL(' WITH (' + NULLIF(LTRIM(RTRIM(SUBSTRING(ISNULL(', ' + dc.DataCompression, '')
                                                                        + ISNULL(CASE WHEN @SortInTempDb = 1
                                                                                           AND DisableNonPermanent = 0 THEN ', SORT_IN_TEMPDB = ON'
                                                                                 END, '') + ISNULL(CASE WHEN ix.IGNORE_DUP_KEY = 1 THEN ', IGNORE_DUP_KEY = ON'
                                                                                                   END, '')
                                                                        + ISNULL(CASE WHEN @Online = 1
                                                                                           AND DisableNonPermanent = 0 THEN ', ONLINE = ON'
                                                                                 END, '')
                                                                        + ISNULL(CASE WHEN ISNULL(@AllowRowLocks, ix.ALLOW_ROW_LOCKS) = 0
                                                                                      THEN ', ALLOW_ROW_LOCKS = OFF'
                                                                                 END, '')
                                                                        + ISNULL(CASE WHEN ISNULL(@AllowPageLocks, ix.ALLOW_PAGE_LOCKS) = 0
                                                                                      THEN ', ALLOW_PAGE_LOCKS = OFF'
                                                                                 END, '') + ISNULL(CASE WHEN @MAXDOP > 0
                                                                                                             AND DisableNonPermanent = 0
                                                                                                        THEN ', MAXDOP = ' + CAST(@MAXDOP AS VARCHAR)
                                                                                                   END, '')
                                                                        + ISNULL(CASE WHEN ISNULL(@Fillfactor, ix.fill_factor) > 0
                                                                                      THEN ', FILLFACTOR  = '
                                                                                           + CAST(ISNULL(@Fillfactor, ix.fill_factor) AS VARCHAR)
                                                                                 END, '')
                                                                        + ISNULL(CASE WHEN ISNULL(@PadIndex, ix.is_padded) > 0 THEN ', PAD_INDEX  = ON'
                                                                                 END, ''), 3, 8000))), '') + ')', '') AS IndexOptions,
                        ic.KeyColumns,
                        ic.IncludeColumns,
                        ic.PartitionedColumn,
                        CreateIndex,
                        CASE WHEN IGNORE_DUP_KEY = 1 THEN 0
                             ELSE EnableIndex
                        END AS EnableIndex,
                        CASE WHEN IGNORE_DUP_KEY = 1 THEN 0
                             ELSE DisableIndex
                        END AS DisableIndex
              FROM      #Objects AS tl
              INNER JOIN sys.indexes AS ix WITH (NOLOCK) ON tl.OBJECT_ID = ix.OBJECT_ID
                                                            AND ix.index_id > 0
              LEFT OUTER JOIN sys.data_spaces AS ds WITH (NOLOCK) ON ds.data_space_id = ix.data_space_id
              LEFT OUTER JOIN sys.partitions AS pr WITH (NOLOCK) ON pr.OBJECT_ID = ix.OBJECT_ID
                                                                    AND pr.index_id = ix.index_id
                                                                    AND pr.partition_number = 1
              CROSS APPLY (SELECT   CASE WHEN @CreateTableInOneStep = 1
                                              AND (ix.is_primary_key = 1
                                                   OR ix.is_unique_constraint = 1) THEN 1
                                         ELSE 0
                                    END AS DisableNonPermanent) dp
              CROSS APPLY (SELECT   CASE ISNULL(@NewDataCompression, pr.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS)
                                      WHEN 'ROW' THEN 'DATA_COMPRESSION = ROW'
                                      WHEN 'PAGE' THEN 'DATA_COMPRESSION = PAGE'
                                    END AS DataCompression) AS dc
              CROSS APPLY (SELECT   Util.dbo.StringConcat(CASE WHEN key_ordinal > 0 THEN QUOTENAME(ColumnName) + CASE WHEN is_descending_key = 1 THEN ' DESC'
                                                                                                                      ELSE ''
                                                                                                                 END
                                                          END, ', ') AS KeyColumns,
                                    Util.dbo.StringConcat(CASE WHEN is_included_column = 1 THEN QUOTENAME(ColumnName)
                                                          END, ', ') AS IncludeColumns,
                                    MAX(CASE WHEN partition_ordinal = 1 THEN QUOTENAME(ColumnName)
                                        END) AS PartitionedColumn
                           FROM     (SELECT TOP 99999999
                                            cl.name AS ColumnName,
                                            ic.key_ordinal,
                                            ic.is_included_column,
                                            ic.partition_ordinal,
                                            ic.is_descending_key
                                     FROM   sys.index_columns AS ic WITH (NOLOCK)
                                     INNER JOIN sys.columns AS cl WITH (NOLOCK) ON cl.OBJECT_ID = ic.OBJECT_ID
                                                                                   AND cl.column_id = ic.column_id
                                     WHERE  ic.OBJECT_ID = ix.OBJECT_ID
                                            AND ic.index_id = ix.index_id
                                     ORDER BY ic.key_ordinal,
                                            ic.index_column_id) k) ic
              CROSS APPLY (SELECT   CASE WHEN (ISNULL(@CreatePrimaryKey, 0) = 1
                                               AND ix.is_primary_key = 1)
                                              OR (ISNULL(@CreateClusteredIndex, 0) = 1
                                                  AND ix.index_id = 1)
                                              OR (ISNULL(@CreateNonClusteredIndex, 0) = 1
                                                  AND ix.index_id > 1)
                                              OR (ISNULL(@CreateUniqueKey, 0) = 1
                                                  AND ix.is_unique_constraint = 1)
                                              OR (ISNULL(@CreateIndex, 0) = 1
                                                  AND ix.is_unique_constraint = 0
                                                  AND ix.is_primary_key = 0) THEN 1
                                         ELSE 0
                                    END AS CreateIndex,
                                    CASE WHEN (ISNULL(@EnableNonClusteredIndex, 0) = 1
                                               AND ix.index_id > 1)
                                              OR (ISNULL(@EnableUniqueKey, 0) = 1
                                                  AND ix.is_unique_constraint = 1)
                                              OR (ISNULL(@EnableIndex, 0) = 1
                                                  AND ix.is_unique_constraint = 0
                                                  AND ix.is_primary_key = 0) THEN 1
                                         ELSE 0
                                    END AS EnableIndex,
                                    CASE WHEN (ISNULL(@DisableNonClusteredIndex, 0) = 1
                                               AND ix.index_id > 1)
                                              OR (ISNULL(@DisableUniqueKey, 0) = 1
                                                  AND ix.is_unique_constraint = 1)
                                              OR (ISNULL(@DisableIndex, 0) = 1
                                                  AND ix.is_unique_constraint = 0
                                                  AND ix.is_primary_key = 0) THEN 1
                                         ELSE 0
                                    END AS DisableIndex) CI
              WHERE     tl.Type IN ('U', 'V')
                        AND (CreateIndex = 1
                             OR IGNORE_DUP_KEY = 0
                             AND (EnableIndex = 1
                                  OR DisableIndex = 1))),
        CheckConstraints
          AS (SELECT    tl.OBJECT_ID,
                        tl.SchemaName,
                        tl.ObjectName AS TableName,
                        ISNULL(@AddNamePrefix, '') + ck.name AS ConstraintName,
                        ck.definition,
                        ck.is_disabled
              FROM      #Objects AS tl
              INNER JOIN sys.check_constraints AS ck WITH (NOLOCK) ON ck.parent_object_id = tl.OBJECT_ID
              WHERE     tl.Type = 'U'
                        AND (@IgnoreListCount = 0
                             OR (@IgnoreListCount > 0
                                 AND NOT EXISTS ( SELECT    *
                                                  FROM      #IgnoreList il
                                                  WHERE     il.OBJECT_ID = ck.OBJECT_ID )))
                        AND ((@IgnoreDisabledCheckConstraint = 1
                              AND ck.is_disabled = 0)
                             OR ISNULL(@IgnoreDisabledCheckConstraint, 0) = 0)),
        AllScript
          AS (SELECT    SchemaName,
                        TableName,
                        KeyName AS ObjectName,
                        'F' AS TYPE,
                        CAST(0 AS BIT) AS CreateScript,
                        CAST(0 AS INT) AS IndexId,
                        NULL AS SingleStepScript,
                        CASE WHEN @UseObjectIdForDrop = 1 THEN 'IF OBJECT_ID(''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(KeyName) + ''') IS NOT NULL'
                             ELSE 'IF EXISTS (SELECT * FROM sys.foreign_keys (NOLOCK) WHERE object_id = OBJECT_ID(N''' + QUOTENAME(SchemaName) + '.'
                                  + QUOTENAME(KeyName) + ''') AND parent_object_id = OBJECT_ID(N''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
                                  + '''))'
                        END + '
    ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' DROP CONSTRAINT ' + QUOTENAME(KeyName) + '
' AS Script
              FROM      ForeignKeyNames fk
              WHERE     @DropForeignKey = 1
              UNION ALL
              SELECT    SchemaName,
                        TableName,
                        ConstraintName AS ObjectName,
                        'D' AS TYPE,
                        CAST(0 AS BIT) AS CreateScript,
                        CAST(0 AS INT) AS IndexId,
                        NULL AS SingleStepScript,
                        CASE WHEN @UseObjectIdForDrop = 1 THEN 'IF OBJECT_ID(''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ConstraintName) + ''') IS NOT NULL'
                             ELSE 'IF EXISTS (SELECT * FROM sys.schemas s (NOLOCK) INNER JOIN sys.objects o (NOLOCK) ON o.schema_id = s.schema_id WHERE s.name = '''
                                  + SchemaName + ''' AND o.name = ''' + ConstraintName + ''')'
                        END + '
    ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' DROP CONSTRAINT ' + QUOTENAME(ConstraintName) + '
' AS Script
              FROM      DefaultConstraintNames dc
              WHERE     @DropDefaultConstraints = 1
              UNION ALL
              SELECT    SchemaName,
                        Objectname AS TableName,
                        Objectname,
                        'U' AS TYPE,
                        CAST(0 AS BIT) AS CreateScript,
                        CAST(0 AS INT) AS IndexId,
                        NULL AS SingleStepScript,
                        CASE WHEN Objectname LIKE '#%' THEN 'IF OBJECT_ID(''tempdb..' + Objectname + ''') IS NOT NULL
DROP ' + TypeName + ' ' + Objectname + '
'                            ELSE CASE WHEN @UseObjectIdForDrop = 1
                                       THEN 'IF OBJECT_ID(''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(Objectname) + ''') IS NOT NULL'
                                       ELSE 'IF EXISTS (SELECT * FROM sys.schemas s (NOLOCK) INNER JOIN sys.objects o (NOLOCK) ON o.schema_id = s.schema_id WHERE s.name = '''
                                            + SchemaName + ''' AND o.name = ''' + Objectname + ''')'
                                  END + '
    DROP ' + TypeName + ' ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(Objectname) + '
'
                        END AS Script
              FROM      #Objects
              WHERE     @DropTable = 1
              UNION ALL
              SELECT    SchemaName,
                        Objectname AS TableName,
                        ObjectName,
                        'U' AS TYPE,
                        CAST(1 AS BIT) AS CreateScript,
                        CAST(0 AS INT) AS IndexId,
                        AfterScript AS SingleStepScript,
                        'CREATE TABLE ' + QUOTENAME(SchemaName) + '.' + CASE WHEN ObjectName LIKE '#%' THEN ObjectName
                                                                             ELSE QUOTENAME(Objectname)
                                                                        END + ' (
    ' + ColumnDef AS Script
              FROM      TableInfo ti
              CROSS APPLY (SELECT   ')
' + CASE WHEN @CreateTableInOneStep = 0 THEN FileGroup + ISNULL(DataCompression, '')
         ELSE FileGroup
    END + CASE WHEN lock_escalation_desc <> 'TABLE' THEN '
ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(Objectname) + ' SET (LOCK_ESCALATION = ' + lock_escalation_desc + ')
'              ELSE '
'
          END AS AfterScript) ss
              CROSS APPLY (SELECT   Util.dbo.StringConcat(ColumnDef, ',
    ') AS ColumnDef
                           FROM     (SELECT TOP 99999999
                                            QUOTENAME(co.NAME) + ' ' + CASE WHEN cc.column_id IS NOT NULL
                                                                            THEN 'AS ' + cc.definition + CASE WHEN cc.is_persisted = 1
                                                                                                              THEN ' PERSISTED'
                                                                                                                   + CASE WHEN co.is_nullable = 0
                                                                                                                          THEN ' NOT NULL'
                                                                                                                          ELSE ' NULL'
                                                                                                                     END
                                                                                                              ELSE ''
                                                                                                         END
                                                                            ELSE CASE WHEN ts.name IN ('char', 'varchar')
                                                                                      THEN ts.name + '(' + CASE WHEN co.max_length = -1 THEN 'MAX'
                                                                                                                ELSE CAST(co.max_length AS VARCHAR)
                                                                                                           END + ')'
                                                                                           + CASE WHEN co.collation_name <> db.collation_name
                                                                                                  THEN ' COLLATE ' + co.collation_name
                                                                                                  ELSE ''
                                                                                             END
                                                                                      WHEN ts.name IN ('nchar', 'nvarchar')
                                                                                      THEN ts.name + '(' + CASE WHEN co.max_length = -1 THEN 'MAX'
                                                                                                                ELSE CAST(co.max_length / 2 AS VARCHAR)
                                                                                                           END + ')'
                                                                                           + CASE WHEN co.collation_name <> db.collation_name
                                                                                                  THEN ' COLLATE ' + co.collation_name
                                                                                                  ELSE ''
                                                                                             END
                                                                                      WHEN ts.name IN ('binary', 'varbinary')
                                                                                      THEN ts.name + '(' + CASE WHEN co.max_length = -1 THEN 'MAX'
                                                                                                                ELSE CAST(co.max_length AS VARCHAR)
                                                                                                           END + ')'
                                                                                      WHEN ts.name IN ('bigint', 'int', 'smallint', 'tinyint') THEN ts.name
                                                                                      WHEN ts.name IN ('datetime2', 'time', 'datetimeoffset')
                                                                                      THEN ts.name + '(' + CAST(co.scale AS VARCHAR) + ')'
                                                                                      WHEN ts.name IN ('numeric', 'decimal')
                                                                                      THEN ts.name + '(' + CAST(co.PRECISION AS VARCHAR) + ', '
                                                                                           + CAST(co.scale AS VARCHAR) + ')'
                                                                                      ELSE ts.name
                                                                                 END + CASE WHEN co.is_identity = 1 THEN ' IDENTITY'
                                                                                            ELSE ''
                                                                                       END + ' ' + CASE WHEN co.is_nullable = 1 THEN 'NULL'
                                                                                                        ELSE 'NOT NULL'
                                                                                                   END
                                                                                 + CASE WHEN @CreateDefaultConstraints = 1
                                                                                        THEN CASE dc.is_system_named
                                                                                               WHEN 1 THEN ' DEFAULT ' + dc.definition
                                                                                               WHEN 0
                                                                                               THEN ' CONSTRAINT ' + QUOTENAME(ISNULL(@AddNamePrefix, '')
                                                                                                                               + dc.NAME) + ' DEFAULT '
                                                                                                    + dc.definition
                                                                                               ELSE ''
                                                                                             END
                                                                                        ELSE ''
                                                                                   END
                                                                       END AS ColumnDef
                                     FROM   sys.columns AS co WITH (NOLOCK)
                                     INNER JOIN sys.types AS ts WITH (NOLOCK) ON ts.user_type_id = co.user_type_id
                                     INNER JOIN sys.databases AS db WITH (NOLOCK) ON db.database_id = DB_ID()
                                     LEFT OUTER JOIN sys.indexes ix (NOLOCK) ON ix.OBJECT_ID = co.OBJECT_ID
                                                                                AND ix.is_primary_key = 1
                                     LEFT OUTER JOIN sys.index_columns ic (NOLOCK) ON ic.OBJECT_ID = ix.OBJECT_ID
                                                                                      AND ic.index_id = ix.index_id
                                                                                      AND co.column_id = ic.column_id
                                     LEFT OUTER JOIN sys.computed_columns AS cc (NOLOCK) ON co.is_computed = 1
                                                                                            AND co.OBJECT_ID = cc.OBJECT_ID
                                                                                            AND co.column_id = cc.column_id
                                     LEFT OUTER JOIN sys.default_constraints AS dc WITH (NOLOCK) ON dc.parent_object_id = co.OBJECT_ID
                                                                                                    AND dc.parent_column_id = co.column_id
                                                                                                    AND (@IgnoreListCount = 0
                                                                                                         OR (@IgnoreListCount > 0
                                                                                                             AND NOT EXISTS ( SELECT    *
                                                                                                                              FROM      #IgnoreList il
                                                                                                                              WHERE     il.OBJECT_ID = dc.OBJECT_ID )))
                                     WHERE  co.OBJECT_ID = ti.OBJECT_ID
                                     ORDER BY CASE WHEN @SortTableColumns = 1 THEN ISNULL(ic.key_ordinal, 255)
                                              END,
                                            CASE WHEN @SortTableColumns = 1 THEN co.NAME
                                            END,
                                            co.column_id) k) tc
              UNION ALL
              SELECT    SchemaName,
                        TableName,
                        ConstraintName AS ObjectName,
                        'D' AS TYPE,
                        CAST(1 AS BIT) AS CreateScript,
                        CAST(0 AS INT) AS IndexId,
                        NULL AS SingleStepScript,
                        'IF NOT EXISTS(SELECT * FROM sys.schemas s (NOLOCK) INNER JOIN sys.objects o (NOLOCK) ON o.schema_id = s.schema_id WHERE s.name = '''
                        + SchemaName + ''' AND o.name = ''' + ConstraintName + ''')
    ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' ADD CONSTRAINT ' + QUOTENAME(ConstraintName) + ' DEFAULT ' + definition + ' FOR '
                        + QUOTENAME(ColumnName) + '
' AS Script
              FROM      DefaultConstraintNames dc
              WHERE     (@CreateDefaultConstraints = 1
                         AND ISNULL(@CreateTable, 0) = 0)
              UNION ALL
              SELECT    SchemaName,
                        ObjectName,
                        IndexName AS ObjectName,
                        CASE WHEN is_primary_key = 1 THEN 'PK'
                             WHEN is_unique_constraint = 1 THEN 'UQ'
                             ELSE 'I'
                        END AS TYPE,
                        CAST(0 AS BIT) AS CreateScript,
                        index_id AS IndexId,
                        NULL AS SingleStepScript,
                        CASE WHEN @UseObjectIdForDrop = 1 THEN 'IF OBJECT_ID(''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(IndexName) + ''') IS NOT NULL'
                             ELSE 'IF EXISTS (SELECT * FROM sys.schemas s (NOLOCK) INNER JOIN sys.objects o (NOLOCK) ON o.schema_id = s.schema_id INNER JOIN sys.indexes i (NOLOCK) ON i.object_id = o.object_id WHERE s.name = '''
                                  + SchemaName + ''' AND o.name = ''' + ObjectName + ''' AND i.name = ''' + IndexName + ''')'
                        END + '
' + CASE WHEN is_primary_key = 0
              AND is_unique_constraint = 0 THEN 'DROP INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + '
'        ELSE '    ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' DROP CONSTRAINT ' + QUOTENAME(IndexName) + '
'
    END AS Script
              FROM      IndexNames i
              UNION ALL
              SELECT    SchemaName,
                        ObjectName,
                        IndexName AS ObjectName,
                        CASE WHEN is_primary_key = 1 THEN 'PK'
                             WHEN is_unique_constraint = 1 THEN 'UQ'
                             ELSE 'I'
                        END AS TYPE,
                        CAST(1 AS BIT) AS CreateScript,
                        index_id AS IndexId,
                        CASE WHEN @CreateTableInOneStep = 1
                                  AND (is_primary_key = 1
                                       OR is_unique_constraint = 1) THEN ConstraintScript
                        END AS SingleStepScript,
                        CASE WHEN CreateIndex = 1
                             THEN CASE WHEN @GenerateIfNotExists = 1
                                       THEN 'IF NOT EXISTS (SELECT * FROM sys.schemas s (NOLOCK) INNER JOIN sys.objects o (NOLOCK) ON o.schema_id = s.schema_id INNER JOIN sys.indexes i (NOLOCK) ON i.object_id = o.object_id WHERE s.name = '''
                                            + SchemaName + ''' AND o.name = ''' + ObjectName + ''' AND i.name = ''' + IndexName + ''')
'                                      ELSE ''
                                  END + CASE WHEN has_filter = 1 THEN 'SET ANSI_NULLS ' + CASE WHEN uses_ansi_nulls = 1 THEN 'ON'
                                                                                               ELSE 'OFF'
                                                                                          END + '
'                                            ELSE ''
                                        END + CASE WHEN is_primary_key = 1
                                                        OR is_unique_constraint = 1
                                                   THEN 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' ADD ' + ConstraintScript
                                                   ELSE 'CREATE ' + CASE WHEN is_unique = 1 THEN 'UNIQUE '
                                                                         ELSE ''
                                                                    END + IndexType + ' INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(SchemaName) + '.'
                                                        + QUOTENAME(ObjectName) + ' (' + KeyColumns + ')' + ISNULL(' INCLUDE (' + IncludeColumns + ')', '')
                                                        + ISNULL(' WHERE ' + filter_definition, '') + IndexOptions + ' '
                                                        + CASE WHEN @NewPartition <> '' THEN 'ON ' + @NewPartition
                                                               WHEN @NewFileGroup = '' THEN ''
                                                               ELSE ISNULL('ON ' + QUOTENAME(@NewFileGroup) + '',
                                                                           CASE DataspaceType
                                                                             WHEN 'FG' THEN 'ON ' + QUOTENAME(DataspaceName) + ''
                                                                             WHEN 'PS' THEN 'ON ' + QUOTENAME(DataspaceName) + '(' + PartitionedColumn + ')'
                                                                             WHEN 'FD' THEN ''
                                                                             ELSE ''
                                                                           END)
                                                          END
                                              END + '
'                            ELSE ''
                        END + CASE WHEN CreateIndex = 0
                                        AND EnableIndex = 1
                                   THEN 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' REBUILD '
                                        + IndexOptions + '
'                                  ELSE ''
                              END + CASE WHEN DisableIndex = 1
                                              OR (CreateIndex = 1
                                                  AND EnableIndex = 0
                                                  AND is_disabled = 1)
                                         THEN 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' DISABLE
'                                        ELSE ''
                                    END AS Script
              FROM      IndexColumns ic
              CROSS APPLY (SELECT   'CONSTRAINT ' + QUOTENAME(IndexName) + ' ' + CASE WHEN is_primary_key = 1 THEN 'PRIMARY KEY '
                                                                                      ELSE 'UNIQUE '
                                                                                 END + IndexType + ' (' + KeyColumns + ')' + IndexOptions + ' '
                                    + CASE WHEN @NewPartition <> '' THEN 'ON ' + @NewPartition
                                           WHEN @NewFileGroup = '' THEN ''
                                           ELSE ISNULL('ON ' + QUOTENAME(@NewFileGroup),
                                                       CASE DataspaceType
                                                         WHEN 'FG' THEN 'ON ' + QUOTENAME(DataspaceName)
                                                         WHEN 'PS' THEN 'ON ' + QUOTENAME(DataspaceName) + '(' + PartitionedColumn + ')'
                                                         WHEN 'FD' THEN ''
                                                         ELSE ''
                                                       END)
                                      END AS ConstraintScript) cs
              UNION ALL
              SELECT    SchemaName AS SchemaName,
                        TableName AS TableName,
                        KeyName AS ObjectName,
                        'F' AS TYPE,
                        CAST(1 AS BIT) AS CreateScript,
                        CAST(0 AS INT) AS IndexId,
                        f2.Script AS SingleStepScript,
                        CASE WHEN @GenerateIfNotExists = 1
                             THEN 'IF NOT EXISTS (SELECT * FROM sys.foreign_keys (NOLOCK) WHERE object_id = OBJECT_ID(N''' + QUOTENAME(SchemaName) + '.'
                                  + QUOTENAME(KeyName) + ''') AND parent_object_id = OBJECT_ID(N''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + '''))
'                            ELSE ''
                        END + 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' '
                        + CASE WHEN is_disabled = 1
                                    OR @CreateForeignKeyInTwoSteps = 1 THEN 'WITH NOCHECK '
                               ELSE ''
                          END + 'ADD ' + f2.Script + '
' + CASE WHEN is_disabled = 1 THEN 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' NOCHECK CONSTRAINT ' + QUOTENAME(KeyName) + '
'        ELSE ''
    END + CASE WHEN is_disabled = 0
                    AND @CreateForeignKeyInTwoSteps = 1
               THEN 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(KeyName) + '
'              ELSE ''
          END AS Script
              FROM      ForeignKeyColumns fk
              CROSS APPLY (SELECT   'CONSTRAINT ' + QUOTENAME(KeyName) + ' FOREIGN KEY (' + ParentColumns + ') REFERENCES ' + QUOTENAME(rSchemaName) + '.'
                                    + QUOTENAME(rTableName) + ' (' + ReferencedColumns + ')' + CASE WHEN delete_referential_action > 0
                                                                                                    THEN ' ON DELETE ' + delete_referential_action_desc
                                                                                                    ELSE ''
                                                                                               END + CASE WHEN update_referential_action > 0
                                                                                                          THEN ' ON UPDATE ' + update_referential_action_desc
                                                                                                          ELSE ''
                                                                                                     END
                                    + CASE WHEN is_not_for_replication = 1 THEN ' NOT FOR REPLICATION'
                                           ELSE ''
                                      END AS Script) f2
              UNION ALL
              SELECT    SchemaName,
                        TableName,
                        ConstraintName AS ObjectName,
                        'C' AS TYPE,
                        CAST(0 AS BIT) AS CreateScript,
                        CAST(0 AS INT) AS IndexId,
                        NULL AS SingleStepScript,
                        CASE WHEN @UseObjectIdForDrop = 1 THEN 'IF OBJECT_ID(''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ConstraintName) + ''') IS NOT NULL'
                             ELSE 'IF EXISTS (SELECT * FROM sys.check_constraints (NOLOCK) WHERE object_id = OBJECT_ID(N''' + QUOTENAME(SchemaName) + '.'
                                  + QUOTENAME(ConstraintName) + ''') AND parent_object_id = OBJECT_ID(N''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
                                  + '''))'
                        END + '
    ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' DROP CONSTRAINT ' + QUOTENAME(ConstraintName) + '
' AS Script
              FROM      CheckConstraints ck
              WHERE     @DropCheckConstraint = 1
              UNION ALL
              SELECT    SchemaName,
                        TableName,
                        ConstraintName AS ObjectName,
                        'C' AS TYPE,
                        CreateScript,
                        CAST(0 AS INT) AS IndexId,
                        cs.Script AS SingleStepScript,
                        kk.Script
              FROM      CheckConstraints ck
              CROSS APPLY (SELECT 'CONSTRAINT ' + QUOTENAME (ConstraintName) + ' CHECK ' + definition   AS Script) cs
              CROSS APPLY (SELECT   CAST(1 AS BIT) AS CreateScript,
                                    CASE WHEN @GenerateIfNotExists = 1
                                         THEN 'IF NOT EXISTS (SELECT * FROM sys.check_constraints (NOLOCK) WHERE object_id = OBJECT_ID(N'''
                                              + QUOTENAME(SchemaName) + '.' + QUOTENAME(ConstraintName) + ''') AND parent_object_id = OBJECT_ID(N'''
                                              + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + '''))
'                                        ELSE ''
                                    END + 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' '
                                    + CASE WHEN is_disabled = 1
                                                OR @CreateCheckConstraintInTwoSteps = 1 THEN 'WITH NOCHECK '
                                           ELSE ''
                                      END + 'ADD ' + Script + '
' + CASE WHEN is_disabled = 1 THEN 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' NOCHECK CONSTRAINT ' + QUOTENAME(ConstraintName) + '
'        ELSE ''
    END + CASE WHEN is_disabled = 0
                    AND @CreateCheckConstraintInTwoSteps = 1
               THEN 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(ConstraintName) + '
'              ELSE ''
          END AS Script) kk
              WHERE     @CreateCheckConstraint = 1
              UNION ALL
              SELECT    SchemaName,
                        ObjectName,
                        ObjectName,
                        TYPE,
                        CreateScript,
                        CAST(0 AS INT) AS IndexId,
                        NULL AS SingleStepScript,
                        Script
              FROM      Procedures tr
              CROSS APPLY (SELECT   CAST(0 AS BIT) AS CreateScript,
                                    CASE WHEN @UseObjectIdForDrop = 1
                                         THEN 'IF OBJECT_ID(''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(Objectname) + ''') IS NOT NULL'
                                         ELSE 'IF EXISTS (SELECT * FROM sys.objects (NOLOCK) WHERE object_id = OBJECT_ID(N''' + QUOTENAME(SchemaName) + '.'
                                              + QUOTENAME(ObjectName) + '''))'
                                    END + '
DROP ' + TypeName + ' ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(Objectname) + '
' AS Script
                           WHERE    ((@DropProcedure = 1
                                      AND TYPE = 'P')
                                     OR (@DropFunction = 1
                                         AND TYPE IN ('FN', 'IF', 'TF'))
                                     OR (@DropView = 1
                                         AND TYPE = 'V'))
                           UNION ALL
                           SELECT   CAST(1 AS BIT) AS CreateScript,
                                    ObjectDefinition + '
' AS Script
                           WHERE    ((@CreateProcedure = 1
                                      AND TYPE = 'P')
                                     OR (@CreateFunction = 1
                                         AND TYPE IN ('FN', 'IF', 'TF'))
                                     OR (@CreateView = 1
                                         AND TYPE = 'V'))) ss
              UNION ALL
              SELECT    SchemaName,
                        ObjectName,
                        TriggerName AS ObjectName,
                        'TR' AS TYPE,
                        CreateScript,
                        CAST(0 AS INT) AS IndexId,
                        NULL AS SingleStepScript,
                        SCRIPT
              FROM      Triggers tr
              CROSS APPLY (SELECT   CAST(0 AS BIT) AS CreateScript,
                                    CASE WHEN @UseObjectIdForDrop = 1
                                         THEN 'IF OBJECT_ID(''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TriggerName) + ''') IS NOT NULL'
                                         ELSE 'IF EXISTS (SELECT * FROM sys.triggers (NOLOCK) WHERE object_id = OBJECT_ID(N''' + QUOTENAME(SchemaName) + '.'
                                              + QUOTENAME(TriggerName) + '''))'
                                    END + '
DROP TRIGGER ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TriggerName) + '
' AS SCRIPT
                           WHERE    @DropTrigger = 1
                           UNION ALL
                           SELECT   CAST(1 AS BIT) AS CreateScript,
                                    TriggerDefinition + '
' AS SCRIPT
                           WHERE    @CreateTrigger = 1) kk)
    INSERT  #sp_script WITH (TABLOCK)
            (OrderId,
             SchemaName,
             TableName,
             ObjectName,
             TYPE,
             CreateScript,
             IndexId,
             Script)
            SELECT TOP 999999999
                    ISNULL((SELECT MAX (OrderId) FROM #sp_script), 0)
                    + ROW_NUMBER() OVER (ORDER BY CASE WHEN a.CreateScript = 0 THEN -1 * ISNULL(s.OrderID, 100)
                                                       ELSE ISNULL(s.OrderID, 100)
                                                  END, a.CreateScript, a.SchemaName, a.TableName, CASE WHEN CreateScript = 1 THEN a.IndexId
                                                                                                       ELSE (-1 * a.IndexId)
                                                                                                  END, a.ObjectName, a.Type) AS OrderID,
                    a.SchemaName,
                    a.TableName,
                    a.ObjectName,
                    a.Type,
                    a.CreateScript,
                    a.IndexId,
                    CASE WHEN @CreateTableInOneStep = 1
                              AND a.Type = 'U'
                              AND a.CreateScript = 1 THEN a.Script + ISNULL(',
' + b.TableAddScript, '') + a.SingleStepScript
                         WHEN a.Type = 'U'
                              AND a.CreateScript = 1 THEN a.Script + a.SingleStepScript
                         ELSE a.Script
                    END
            FROM    AllScript a
            OUTER APPLY (SELECT Util.dbo.StringConcat('    ' + b.SingleStepScript, ',
') AS TableAddScript
                         FROM   AllScript b
                         WHERE  @CreateTableInOneStep = 1
                                AND b.CreateScript = 1
                                AND a.CreateScript = 1
                                AND b.TYPE <> 'U'
                                AND A.TYPE = 'U'
                                AND a.SchemaName = b.SchemaName
                                AND a.TableName = b.TableName) b
            LEFT OUTER JOIN (SELECT TYPE,
                                    Description,
                                    OrderId
                             FROM   ( VALUES ( 'U', 'Table (user-defined)', 1), ( 'V', 'View', 2), ( 'PK', 'PRIMARY KEY constraint', 3),
                                    ( 'UQ', 'UNIQUE constraint', 4), ( 'I', 'Index (Gokhan Custom)', 5), ( 'C', 'CHECK constraint', 6),
                                    ( 'D', 'DEFAULT (constraint or stand-alone)', 7), ( 'F', 'FOREIGN KEY constraint', 8), ( 'TR', 'SQL DML trigger', 9),
                                    ( 'FN', 'SQL scalar function', 20), ( 'IF', 'SQL inline table-valued function', 30), ( 'P', 'SQL Stored Procedure', 40),
                                    ( 'TF', 'SQL table-valued-function', 50) ) AS temp (TYPE, Description, OrderId)) s ON a.TYPE = s.TYPE
            WHERE   NOT (@CreateTableInOneStep = 1
                         AND a.TYPE <> 'U'
                         AND a.SingleStepScript IS NOT NULL)
    OPTION  (MAXDOP 1, RECOMPILE)

IF @SilentOperation = 1
    RETURN

IF @ReturnRecordset = 1
    BEGIN
        SELECT  SchemaName,
                TableName,
                ObjectName,
                TYPE,
                CreateScript,
                Script
        FROM    #sp_script
        RETURN
    END

SELECT  @SQL = ISNULL(@SQL, '') + ISNULL('USE ' + QUOTENAME(@NewDatabaseName) + '
', '') + Util.dbo.StringConcat(Script, @BatchTerminator)
FROM    (SELECT TOP 99999999
                Script
         FROM   #sp_script
         ORDER BY OrderId) kk
OPTION  (MAXDOP 1)

IF OBJECT_ID('tempdb..#sp_script') IS NOT NULL
    DROP TABLE #sp_script

IF @SQL <> ''
    AND @BatchTerminator <> ''
    SET @SQL = @SQL + @BatchTerminator

IF @FilePath <> ''
    SELECT  ReturnVal,
            MESSAGE,
            @FilePath AS FilePath,
            'Saved to ' + @FilePath AS Info
    FROM    Util.FS.AppendAllTextToFile(@FilePath, @SQL, 1)
IF @recipients <> ''
    BEGIN
        SET @subject = ISNULL(@subject, 'sp_script')

        EXEC msdb.dbo.sp_send_dbmail
            @recipients = @recipients,
            @subject = @subject,
            @body = @SQL
    END
IF @PrintSQL = 1
    EXEC Util.dbo.PrintLargeText
        @Input = @SQL,
        @RtrimLines = @RtrimLines
IF @ExecuteSQL = 1
    AND @SQL <> ''
    BEGIN
        IF @UseTransaction = 1
            BEGIN TRANSACTION
        EXEC (@SQL)

        IF @@ERROR = 0
            BEGIN
                IF @UseTransaction = 1
                    AND @@TRANCOUNT > 0
                    COMMIT TRANSACTION
                RETURN 0
            END
        ELSE
            BEGIN
                IF @UseTransaction = 1
                    AND @@TRANCOUNT > 0
                    ROLLBACK TRANSACTION
                RETURN 12
            END
    END
RETURN 0
GO
EXEC sys.sp_ms_marksystemobject
    sp_script
GO

Description for Template Script: System Proc\sp_script.sql

Todo
Site Map | Printable View | © 2008 - 2012 NuRoN Consulting, INC | Powered by mojoPortal | HTML 5 | CSS | Original design by Andreas Viklund
Share This Using Popular Bookmarking Services