이 스레드에서 E.Mantovanelli의 버전을 기반으로 한 버전이 있습니다. 이는 고유 인덱스가 결과 스크립트에 키워드 UNIQUE를 포함하지 않는 문제를 수정합니다. 또한 비 클러스터형 인덱스없이 테이블을 만들거나 비 클러스터형 인덱스 만 스크립팅 할 수 있도록 매개 변수를 추가합니다. 이것을 사용하여 스테이지 테이블을 작성하고 비 클러스터형 인덱스를 추가 한 다음 테이블 스위치를 수행하여로드가 더 빠르게 실행되고 인덱스가 조각화되지 않도록합니다.

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

--*************************************************************************************************'
--    La procedura crea lo script di una tabella
--    Tabella   : xxxxx
--    Creata da : E.Mantovanelli
--    Data creazione : 28-06-2012
--    Data modifica: 28-06-2012
--*************************************************************************************************'

/*
    --      ID----|-----Data-----|--    User        ---------   | ----  Note
                        20-11-2013      E.Mantovanelli                  distinzione schema delle tabelle
                                                                        estrazione da db selezionato
                                                                        aggiunta estrazione partizione
*/
--*************************************************************************************************'
--    Creates a copy of a table with optionally all indexes and constraints depending on parameters
--    Updated by : Brent Willis
--    Date Updated : 09-11-2019
--    Changes: Added the ability to create a table with clustered index and constraints only or to 
--             CREATE just the non-clustered indexes. This is helpful so a table can be created, 
--             loaded then non-clusted indexes added so it can them be swapped into a partitioned 
--             table with freshly created indexes to lower fragmentation. 
--
--             Also fixed an issue when a unique index was defined the index resulting table was not unique. 
--*************************************************************************************************'

ALTER PROCEDURE dbo.util_ScriptTable
    @DBName                sysname
  , @schema                sysname
  , @TableName             sysname
  , @includeTable          Bit     = 1
  , @IncludeConstraints    Bit     = 1
  , @IncludeClusteredIndex Bit     = 1
  , @IncludeIndexes        Bit     = 1
  , @NewTableSchema        sysname
  , @NewTableName          sysname = NULL
  , @UseSystemDataTypes    Bit     = 0
  , @script                Varchar(MAX) OUTPUT
AS
    BEGIN TRY
        IF NOT EXISTS (SELECT * FROM sys.types WHERE name = 'TestTableType')
            CREATE TYPE TestTableType AS TABLE (ObjectID Int); --drop type TestTableType

        DECLARE @sql NVarchar(MAX);

        DECLARE @MainDefinition Table (FieldValue Varchar(200));

        --DECLARE @DBName SYSNAME
        DECLARE @ClusteredPK Bit;
        DECLARE @TableSchema NVarchar(255);

        --SET @DBName = DB_NAME(DB_ID())
        SELECT @TableName = name FROM sys.sysobjects WHERE id = Object_Id(@TableName);

        DECLARE @ShowFields Table (FieldID            Int IDENTITY(1, 1)
                                 , DatabaseName       Varchar(100)
                                 , TableOwner         Varchar(100)
                                 , TableName          Varchar(100)
                                 , FieldName          Varchar(100)
                                 , ColumnPosition     Int
                                 , ColumnDefaultValue Varchar(100)
                                 , ColumnDefaultName  Varchar(100)
                                 , IsNullable         Bit
                                 , DataType           Varchar(100)
                                 , MaxLength          Varchar(10)
                                 , NumericPrecision   Int
                                 , NumericScale       Int
                                 , DomainName         Varchar(100)
                                 , FieldListingName   Varchar(110)
                                 , FieldDefinition    Char(1)
                                 , IdentityColumn     Bit
                                 , IdentitySeed       Int
                                 , IdentityIncrement  Int
                                 , IsCharColumn       Bit
                                 , IsComputed         Varchar(255));

        DECLARE @HoldingArea Table (FldID SmallInt IDENTITY(1, 1), Flds Varchar(4000), FldValue Char(1) DEFAULT (0));

        DECLARE @PKObjectID Table (ObjectID Int);

        DECLARE @Uniques Table (ObjectID Int);

        DECLARE @Definition Table (DefinitionID SmallInt IDENTITY(1, 1), FieldValue Varchar(2000));

        SET @sql = N'
  use ' + @DBName + N'
  SELECT distinct DB_NAME()
            ,TABLE_SCHEMA
            ,TABLE_NAME
            ,''[''+COLUMN_NAME+'']'' as COLUMN_NAME
            ,CAST(ORDINAL_POSITION AS INT)
            ,COLUMN_DEFAULT
            ,dobj.name AS ColumnDefaultName
            ,CASE WHEN c.IS_NULLABLE = ''YES'' THEN 1 ELSE 0 END
            ,DATA_TYPE
            ,case CHARACTER_MAXIMUM_LENGTH when -1 then ''max'' else CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) end--CAST(CHARACTER_MAXIMUM_LENGTH AS INT)
            ,CAST(NUMERIC_PRECISION AS INT)
            ,CAST(NUMERIC_SCALE AS INT)
            ,DOMAIN_NAME
            ,COLUMN_NAME + '',''
            ,'''' AS FieldDefinition
            ,CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn
            ,CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed
            ,CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement
            ,CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn 
            ,cc.definition 
            FROM INFORMATION_SCHEMA.COLUMNS c
            JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
            LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
            JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
            LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = ''D''
            left join sys.computed_columns cc on c.TABLE_NAME=OBJECT_NAME(cc.object_id) and sc.column_id=cc.column_id
    WHERE c.TABLE_NAME = @TableName and c.TABLE_SCHEMA=@schema 
    ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
    '   ;

        --PRINT @sql;
        INSERT INTO @ShowFields (DatabaseName
                               , TableOwner
                               , TableName
                               , FieldName
                               , ColumnPosition
                               , ColumnDefaultValue
                               , ColumnDefaultName
                               , IsNullable
                               , DataType
                               , MaxLength
                               , NumericPrecision
                               , NumericScale
                               , DomainName
                               , FieldListingName
                               , FieldDefinition
                               , IdentityColumn
                               , IdentitySeed
                               , IdentityIncrement
                               , IsCharColumn
                               , IsComputed)
        EXEC sys.sp_executesql @sql
                             , N'@TableName varchar(50),@schema varchar(50)'
                             , @TableName = @TableName
                             , @schema = @schema;

        SELECT TOP 1 @TableSchema = TableOwner FROM @ShowFields;

        INSERT INTO @HoldingArea (Flds) VALUES ('(');

        IF @includeTable = 1
            BEGIN
                INSERT INTO @Definition (FieldValue)
                VALUES
                (   'CREATE TABLE ' + CASE
                                          WHEN @NewTableName IS NOT NULL THEN
                                              @DBName + '.' + @NewTableSchema + '.' + @NewTableName
                                          ELSE
                                              @DBName + '.' + @TableSchema + '.' + @TableName
                                      END);

                INSERT INTO @Definition (FieldValue) VALUES ('(');

                INSERT INTO @Definition (FieldValue)
                SELECT Char(10) + FieldName + ' '
                       + CASE
                             WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN
                                 DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
                             ELSE
                                 CASE
                                     WHEN IsComputed IS NULL THEN
                                         Upper(DataType)
                                         + CASE
                                               WHEN IsCharColumn = 1 THEN
                                                   '(' + Cast(MaxLength AS Varchar(10)) + ')'
                                               ELSE
                                                   CASE
                                                       WHEN DataType = 'numeric' THEN
                                                           '(' + Cast(NumericPrecision AS Varchar(10)) + ','
                                                           + Cast(NumericScale AS Varchar(10)) + ')'
                                                       ELSE
                                                           CASE
                                                               WHEN DataType = 'decimal' THEN
                                                                   '(' + Cast(NumericPrecision AS Varchar(10)) + ','
                                                                   + Cast(NumericScale AS Varchar(10)) + ')'
                                                               ELSE
                                                                   ''
                                                           END
                                                   END
                                           END
                                         + CASE
                                               WHEN IdentityColumn = 1 THEN
                                                   ' IDENTITY(' + Cast(IdentitySeed AS Varchar(5)) + ','
                                                   + Cast(IdentityIncrement AS Varchar(5)) + ')'
                                               ELSE
                                                   ''
                                           END + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
                                         + CASE
                                               WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN
                                                   'CONSTRAINT ['
                                                   + Replace(ColumnDefaultName, @TableName, @NewTableName)
                                                   + '] DEFAULT' + Upper(ColumnDefaultValue)
                                               ELSE
                                                   ''
                                           END
                                     ELSE
                                         ' as ' + IsComputed + ' '
                                 END
                         END + CASE
                                   WHEN FieldID = (SELECT Max(FieldID)FROM @ShowFields) THEN
                                       ''
                                   ELSE
                                       ','
                               END
                FROM @ShowFields;

                --------------------------------------------------
                IF @IncludeConstraints = 1
                    BEGIN
                        SET @sql = N'
        use ' +         @DBName + N'
        SELECT  distinct  '',CONSTRAINT ['' + replace(name,@TableName,@NewTableName) + ''] FOREIGN KEY ('' + ParentColumns + '') REFERENCES ['' + ReferencedObject + '']('' + ReferencedColumns + '')'' 
           FROM ( SELECT   ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name
                ,   REVERSE(SUBSTRING(REVERSE((   SELECT cp.name + '',''   
                FROM   sys.foreign_key_columns fkc   
                JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id   
                WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ParentColumns,   
                REVERSE(SUBSTRING(REVERSE((   SELECT cr.name + '',''   
                FROM   sys.foreign_key_columns fkc  
                JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
                WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ReferencedColumns   
                FROM sys.foreign_keys fk    
                    inner join sys.schemas s on fk.schema_id=s.schema_id and s.name=@schema) a    
            WHERE ParentObject = @TableName    
        '               ;

                        --PRINT @sql;
                        INSERT INTO @Definition (FieldValue)
                        EXEC sys.sp_executesql @sql
                                             , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)'
                                             , @TableName = @TableName
                                             , @NewTableName = @NewTableName
                                             , @schema = @schema;

                        SET @sql = N'
            use ' +     @DBName + N'
            SELECT distinct '',CONSTRAINT ['' + replace(c.name,@TableName,@NewTableName) + ''] CHECK '' + definition 
            FROM sys.check_constraints c join sys.schemas s on c.schema_id=s.schema_id and s.name=@schema    
            WHERE OBJECT_NAME(parent_object_id) = @TableName
            '           ;

                        --PRINT @sql;
                        INSERT INTO @Definition (FieldValue)
                        EXEC sys.sp_executesql @sql
                                             , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)'
                                             , @TableName = @TableName
                                             , @NewTableName = @NewTableName
                                             , @schema = @schema;

                        SET @sql = N'
            use ' +     @DBName + N'
            SELECT DISTINCT  PKObject = cco.object_id 
            FROM    sys.key_constraints cco    
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id    
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
            join sys.schemas s on cco.schema_id=s.schema_id and s.name=@schema
            WHERE    OBJECT_NAME(parent_object_id) = @TableName    AND  i.type = 1 AND    is_primary_key = 1
            '           ;

                        --PRINT @sql;
                        INSERT INTO @PKObjectID (ObjectID)
                        EXEC sys.sp_executesql @sql
                                             , N'@TableName varchar(50),@schema varchar(50)'
                                             , @TableName = @TableName
                                             , @schema = @schema;

                        SET @sql = N'
            use ' +     @DBName + N'
            SELECT DISTINCT    PKObject = cco.object_id
            FROM    sys.key_constraints cco   
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id  
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
            join sys.schemas s on cco.schema_id=s.schema_id and s.name=@schema
            WHERE    OBJECT_NAME(parent_object_id) = @TableName AND  i.type = 2 AND    is_primary_key = 0 AND    is_unique_constraint = 1
            '           ;

                        --PRINT @sql;
                        INSERT INTO @Uniques (ObjectID)
                        EXEC sys.sp_executesql @sql
                                             , N'@TableName varchar(50),@schema varchar(50)'
                                             , @TableName = @TableName
                                             , @schema = @schema;

                        SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END;

                        DECLARE @t TestTableType;

                        INSERT @t SELECT * FROM @PKObjectID;

                        DECLARE @u TestTableType;

                        INSERT @u SELECT * FROM @Uniques;

                        SET @sql = N'
            use ' +     @DBName + N'
            SELECT distinct '',CONSTRAINT '' + replace(cco.name,@TableName,@NewTableName) + CASE type WHEN ''PK'' THEN '' PRIMARY KEY '' + CASE WHEN pk.ObjectID IS NULL THEN '' NONCLUSTERED '' ELSE '' CLUSTERED '' END  WHEN ''UQ'' THEN '' UNIQUE '' END + CASE WHEN u.ObjectID IS NOT NULL THEN '' NONCLUSTERED '' ELSE '''' END 
            + ''(''+REVERSE(SUBSTRING(REVERSE(( SELECT   c.name +  + CASE WHEN cc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''    
            FROM   sys.key_constraints ccok   
            LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
            LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id 
            LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id  
            WHERE i.object_id = ccok.parent_object_id AND   ccok.object_id = cco.object_id    
            order by key_ordinal FOR XML PATH(''''))), 2, 8000)) + '')''
            FROM sys.key_constraints cco 
            inner join sys.schemas s on cco.schema_id=s.schema_id and s.name=@schema
            LEFT JOIN @U u ON cco.object_id = u.objectID
            LEFT JOIN @t pk ON cco.object_id = pk.ObjectID    
            WHERE    OBJECT_NAME(cco.parent_object_id) = @TableName 

            '           ;

                        --PRINT @sql;
                        INSERT INTO @Definition (FieldValue)
                        EXEC sys.sp_executesql @sql
                                             , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50),@t TestTableType readonly,@u TestTableType readonly'
                                             , @TableName = @TableName
                                             , @NewTableName = @NewTableName
                                             , @schema = @schema
                                             , @t = @t
                                             , @u = @u;
                    END;

                INSERT INTO @Definition (FieldValue) VALUES (')');
            END;

        SET @sql = N'
        use ' + @DBName
                   + N'
        select '' on '' + d.name + ''([''+c.name+''])''
        from sys.tables t join sys.indexes i on(i.object_id = t.object_id and i.index_id < 2)
                          join sys.index_columns ic on(ic.partition_ordinal > 0 and ic.index_id = i.index_id and ic.object_id = t.object_id)
                          join sys.columns c on(c.object_id = ic.object_id and c.column_id = ic.column_id)
                          join sys.schemas s on t.schema_id=s.schema_id
                          join sys.data_spaces d on i.data_space_id=d.data_space_id
        where t.name=@TableName and s.name=@schema
        order by key_ordinal
        ';

        PRINT 'x';

        --PRINT @sql;
        INSERT INTO @Definition (FieldValue)
        EXEC sys.sp_executesql @sql
                             , N'@TableName varchar(50),@schema varchar(50)'
                             , @TableName = @TableName
                             , @schema = @schema;

        IF @IncludeClusteredIndex = 1
            BEGIN
                SET @sql = N'
            use ' + @DBName
                           + N'
            SELECT distinct '' CREATE '' + CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' end + i.type_desc + '' INDEX ['' + replace(i.name COLLATE SQL_Latin1_General_CP1_CI_AS,@TableName,@NewTableName) + ''] ON '
                           + @DBName + N'.' + @NewTableSchema + N'.' + @NewTableName
                           + N' ('' 
            +   REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''   
            FROM  sys.index_columns sc  
            JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
            WHERE  t.name=@TableName AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                         and is_included_column=0
            ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000)) + '')''+
            ISNULL( '' include (''+REVERSE(SUBSTRING(REVERSE((   SELECT name + '',''   
            FROM  sys.index_columns sc  
            JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
            WHERE  t.name=@TableName AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                         and is_included_column=1 
            ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000))+'')'' ,'''')+''''    
            FROM sys.indexes i join sys.tables t on i.object_id=t.object_id
                               join sys.schemas s on t.schema_id=s.schema_id   
            AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND i.type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0 
                where t.name=@TableName and s.name=@schema and i.type_desc = ''CLUSTERED''
            '   ;

                --PRINT @sql;
                INSERT INTO @Definition (FieldValue)
                EXEC sys.sp_executesql @sql
                                     , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50), @ClusteredPK bit'
                                     , @TableName = @TableName
                                     , @NewTableName = @NewTableName
                                     , @schema = @schema
                                     , @ClusteredPK = @ClusteredPK;
            END;

        IF @IncludeIndexes = 1
            BEGIN
                SET @sql = N'
            use ' + @DBName
                           + N'
            SELECT distinct '' CREATE '' + CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' end + i.type_desc + '' INDEX ['' + replace(i.name COLLATE SQL_Latin1_General_CP1_CI_AS,@TableName,@NewTableName) + ''] ON '
                           + @DBName + N'.' + @NewTableSchema + N'.' + @NewTableName
                           + N' ('' 
            +   REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''   
            FROM  sys.index_columns sc  
            JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
            WHERE  t.name=@TableName AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                         and is_included_column=0
            ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000)) + '')''+
            ISNULL( '' include (''+REVERSE(SUBSTRING(REVERSE((   SELECT name + '',''   
            FROM  sys.index_columns sc  
            JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
            WHERE  t.name=@TableName AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                         and is_included_column=1 
            ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000))+'')'' ,'''')+''''    
            FROM sys.indexes i join sys.tables t on i.object_id=t.object_id
                               join sys.schemas s on t.schema_id=s.schema_id   
            AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND i.type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0 
                where t.name=@TableName and s.name=@schema and i.type_desc <> ''CLUSTERED''
            '   ;

                PRINT @sql;

                INSERT INTO @Definition (FieldValue)
                EXEC sys.sp_executesql @sql
                                     , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50), @ClusteredPK bit'
                                     , @TableName = @TableName
                                     , @NewTableName = @NewTableName
                                     , @schema = @schema
                                     , @ClusteredPK = @ClusteredPK;
            END;

        --SELECT * FROM @Definition;
        INSERT INTO @MainDefinition (FieldValue)
        SELECT FieldValue FROM @Definition ORDER BY DefinitionID ASC;

        --SELECT * FROM @MainDefinition;

        ----------------------------------
        DECLARE @q Varchar(MAX);

        SET @q = (SELECT Replace((SELECT FieldValue FROM @MainDefinition FOR XML PATH('')), '</FieldValue>', ''));
        SET @script = (SELECT Replace(@q, '<FieldValue>', ''));
    END TRY
    -- ##############################################################################################################################################################################
    BEGIN CATCH
        BEGIN
            -- INIZIO  Procedura in errore =========================================================================================================================================================
            PRINT '***********************************************************************************************************************************************************';
            PRINT 'ErrorNumber               : ' + Cast(Error_Number() AS NVarchar(MAX));
            PRINT 'ErrorSeverity             : ' + Cast(Error_Severity() AS NVarchar(MAX));
            PRINT 'ErrorState                : ' + Cast(Error_State() AS NVarchar(MAX));
            PRINT 'ErrorLine                 : ' + Cast(Error_Line() AS NVarchar(MAX));
            PRINT 'ErrorMessage              : ' + Cast(Error_Message() AS NVarchar(MAX));
            PRINT '***********************************************************************************************************************************************************';
        -- FINE  Procedura in errore =========================================================================================================================================================
        END;

        SET @script = '';

        RETURN -1;
    END CATCH;
-- ##############################################################################################################################################################################   

답변

--\
---) Author: Hans Michiels
---) Script to copy a sql server table structure with foreign keys but without indexes.
---) This script handles user defined types and computed columns gracefully.
--/
/*
(c) Copyright 2016 - hansmichiels.com

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>.
*/

--\
---) VARIABLES DECLARATIONS
--/
DECLARE @CrLf NVARCHAR(2) 
DECLARE @Indent NVARCHAR(2) 
DECLARE @nsql NVARCHAR(MAX)
DECLARE @SimulationMode CHAR(1) 
DECLARE @SourceSchemaAndTable NVARCHAR(260) 
DECLARE @TargetSchemaAndTable NVARCHAR(260) 
DECLARE @FkNameSuffix NVARCHAR(128)
DECLARE @TableOptions NVARCHAR(500)

--\
---) CONFIGURATION: set the source and target schema/tablename here, and some other settings.
--/
SELECT 
    @SimulationMode = 'Y' -- Use Y if you only want the SQL statement in the output window without it being executed.
  , @SourceSchemaAndTable = '[Production].[Product]'
  , @TargetSchemaAndTable = '[stg].[Product]'
  , @TableOptions = ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]'
  , @FkNameSuffix = '_' + REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') -- A Guid is added to the foreign key name to make it unique.
  , @CrLf = CHAR(13) + CHAR(10)
  , @Indent = SPACE(2)
  -- For 'min' script use this (in case sql is near 4000 characters):
  -- , @CrLf  = ' '
  -- , @Indent = ''

--\
---) BUILD SQL FOR CLONING TABLE
--/
SELECT @nsql
    = ISNULL(@nsql, '')
    + CASE col_sequence WHEN 1 THEN
      @CrLf + 'IF OBJECT_ID(N''' + @TargetSchemaAndTable + ''', ''U'') IS NOT NULL DROP TABLE ' + @TargetSchemaAndTable + ';'
    + @CrLf + 'CREATE TABLE ' + @TargetSchemaAndTable + @CrLf + @Indent + '( ' ELSE @CrLf + @Indent + ', ' END
    + [definition]
FROM (
      SELECT ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, col.column_id) AS col_sequence
        , QUOTENAME(col.name) + ' '
        + COALESCE(
            'AS ' + cmp.definition + CASE ISNULL(cmp.is_persisted, 0) WHEN 1 THEN ' PERSISTED ' ELSE '' END,
            CASE
              WHEN col.system_type_id != col.user_type_id THEN QUOTENAME(usr_tp.schema_name) + '.' + QUOTENAME(usr_tp.name)
              ELSE
                QUOTENAME(sys_tp.name) +
                CASE
                  WHEN sys_tp.name IN ('char', 'varchar', 'binary', 'varbinary') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length AS varchar(10)) END) + ')'
                  WHEN sys_tp.name IN ('nchar', 'nvarchar') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length/2 AS varchar(10)) END) + ')'
                  WHEN sys_tp.name IN ('decimal', 'numeric') THEN '(' + CAST(col.precision AS VARCHAR) + ',' + CAST(col.scale AS VARCHAR) +  ')'
                  WHEN sys_tp.name IN ('datetime2') THEN '(' + CAST(col.scale AS VARCHAR) +  ')'
                  ELSE ''
                END          
            END
            )       
        + CASE col.is_nullable
            WHEN 0 THEN ' NOT'
            ELSE ''
          END + ' NULL' AS [definition]
        FROM sys.tables tb
        JOIN sys.schemas sch
          ON sch.schema_id = tb.schema_id
        JOIN sys.columns col
          ON col.object_id = tb.object_id
        JOIN sys.types sys_tp
          ON col.system_type_id = sys_tp.system_type_id
        AND col.system_type_id = sys_tp.user_type_id
        LEFT JOIN
            (
            SELECT tp.*, sch.name AS [schema_name]
            FROM sys.types tp
            JOIN sys.schemas sch
            ON tp.schema_id = sch.schema_id
            ) usr_tp
          ON col.system_type_id = usr_tp.system_type_id
        AND col.user_type_id = usr_tp.user_type_id
        LEFT JOIN sys.computed_columns cmp
          ON cmp.object_id = tb.object_id
        AND cmp.column_id = col.column_id
      WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U')
      ) subqry
;
SELECT @nsql
    = ISNULL(@nsql, '')
    + CASE col_sequence
        WHEN 1 THEN @CrLf + ', PRIMARY KEY ' + CASE is_clustered_index WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END
            + @CrLf + @Indent + '( '
        ELSE @CrLf + @Indent + ', '
      END
    + QUOTENAME(pk_cols.column_name)
    + CASE is_descending_key
        WHEN 1 THEN ' DESC'
        ELSE ' ASC'
      END
FROM (
      SELECT TOP 2147483647 sch.name as schema_name, tb.name as table_name, col.name as column_name
                , ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, col.column_id) AS col_sequence
                , ic.is_descending_key
                , CASE WHEN idx.index_id = 1 THEN 1 ELSE 0 END AS [is_clustered_index]
        FROM sys.tables tb
        JOIN sys.schemas sch
          ON sch.schema_id = tb.schema_id
        JOIN sys.indexes idx
          ON idx.is_primary_key = 1
        AND idx.object_id = tb.object_id
        JOIN sys.index_columns ic
          ON is_included_column = 0  
        AND ic.object_id = tb.object_id
        AND ic.index_id = idx.index_id
        JOIN sys.columns col
          ON col.column_id = ic.column_id
        AND col.object_id = tb.object_id
      WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U')
      ORDER BY col.column_id
      ) pk_cols

SELECT @nsql = @nsql + @CrLf + @indent + ') ' + @TableOptions

IF @SimulationMode = 'Y' 
BEGIN
  PRINT '-- Simulation mode: script is not executed.'
END
PRINT @nsql;
IF @SimulationMode != 'Y' 
BEGIN
  EXEC(@nsql);
END

--\
---) Copy foreign key constraints
---) A guid is added to the foreign key name to make it unique within the database.
--/
SET @nsql = N'';
SELECT @nsql += N'
ALTER TABLE '
      + @TargetSchemaAndTable
      + ' ADD CONSTRAINT [' + LEFT(fk.name + @FkNameSuffix, 128) +  '] '
      + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(col.name)
      -- get all the columns in the constraint table
      FROM sys.columns AS col
      JOIN sys.foreign_key_columns AS fkc
        ON fkc.parent_column_id = col.column_id
        AND fkc.parent_object_id = col.[object_id]
      WHERE fkc.constraint_object_id = fk.[object_id]
      ORDER BY fkc.constraint_column_id
      FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
      + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rtb.name)
      + '('
      + STUFF((SELECT ',' + QUOTENAME(col.name)
      -- get all the referenced columns
      FROM sys.columns AS col
      JOIN sys.foreign_key_columns AS fkc
        ON fkc.referenced_column_id = col.column_id
        AND fkc.referenced_object_id = col.[object_id]
      WHERE fkc.constraint_object_id = fk.object_id
      ORDER BY fkc.constraint_column_id
      FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
JOIN sys.tables AS rtb -- referenced table
    ON fk.referenced_object_id = rtb.[object_id]
JOIN sys.schemas AS rs
    ON rtb.[schema_id] = rs.[schema_id]
JOIN sys.tables AS ctb -- constraint table
    ON fk.parent_object_id = ctb.[object_id]
WHERE rtb.is_ms_shipped = 0 AND ctb.is_ms_shipped = 0
  AND ctb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U');

IF @SimulationMode = 'Y' 
BEGIN
  PRINT '-- Simulation mode: script is not executed.'
END
PRINT @nsql;
IF @SimulationMode != 'Y' 
BEGIN
  EXEC(@nsql);
END

Select *
into [new table name]
from [table to be copied name]
where 0=1