Подтвердить что ты не робот

Программно копировать индексы из одной таблицы в другую в SQL Server

В основном то же самое, что и в этом вопросе: Как скопировать индексы из одной таблицы в другую в SQL Server, НО, как это сделать программно в T -SQL, учитывая имя исходной таблицы и имя таблицы назначения?

т.е. не зная, какая таблица впереди.

Я могу скопировать основную структуру

SELECT TOP (0) * INTO [BackupTable] FROM [OriginalTable]

Но это не копирует индексы, ограничения, триггеры и т.д.

В идеале я хотел бы, чтобы хранимый процесс выглядел примерно так:

spCloneTableStructure @ExistingTableName, @NewTableName

Копировать столбцы, первичные ключи и индексы

Что-нибудь подобное существует? (обратите внимание, что я на SQL Server 2008 R2)

4b9b3361

ОТВЕТЫ

Ответ 1

Вот что я придумал. Он работает для меня и копирует все, что мне нужно.

CREATE PROCEDURE [dbo].[spCloneTableStructure]
    @SourceSchema nvarchar(255),
    @SourceTable nvarchar(255),
    @DestinationSchema nvarchar(255),
    @DestinationTable nvarchar(255),
    @RecreateIfExists bit = 0
AS
BEGIN
    /*
        Clones an existing table to another table (without data)
        Optionally drops and re-creates target table
        Copies:
            * Structure
            * Primary key
            * Indexes (including ASC/DESC, included columns, filters)
            * Constraints (and unique constraints)

        DOES NOT copy:
            * Triggers
            * File groups
            * Probably a lot of other things

        Note: Assumes that you name (unique) constraints with the table name in it (in order to not duplicate constraint names)
    */
    SET NOCOUNT ON;

    BEGIN TRANSACTION

    --drop the table
    if EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @DestinationSchema AND TABLE_NAME = @DestinationTable)
    BEGIN
        if @RecreateIfExists = 1
        BEGIN
            exec('DROP TABLE [' + @DestinationSchema + '].[' + @DestinationTable + ']')
        END
        ELSE
            RETURN
    END

    --create the table
    exec('SELECT TOP (0) * INTO [' + @DestinationSchema + '].[' + @DestinationTable + '] FROM [' + @SourceSchema + '].[' + @SourceTable + ']')

    DECLARE @PKSchema nvarchar(255), @PKName nvarchar(255)
    SELECT TOP 1 @PKSchema = CONSTRAINT_SCHEMA, @PKName = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = @SourceSchema AND TABLE_NAME = @SourceTable AND CONSTRAINT_TYPE = 'PRIMARY KEY'

    --create primary key
    IF NOT @PKSchema IS NULL AND NOT @PKName IS NULL
    BEGIN
        DECLARE @PKColumns nvarchar(MAX)
        SET @PKColumns = ''

        SELECT @PKColumns = @PKColumns + '[' + COLUMN_NAME + '],'
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
            where TABLE_NAME = @SourceTable and TABLE_SCHEMA = @SourceSchema AND CONSTRAINT_SCHEMA = @PKSchema AND CONSTRAINT_NAME= @PKName
            ORDER BY ORDINAL_POSITION

        SET @PKColumns = LEFT(@PKColumns, LEN(@PKColumns) - 1)

        exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] ADD  CONSTRAINT [PK_' + @DestinationTable + '] PRIMARY KEY CLUSTERED (' + @PKColumns + ')');
    END

    --create other indexes
    DECLARE @IndexId int, @IndexName nvarchar(255), @IsUnique bit, @IsUniqueConstraint bit, @FilterDefinition nvarchar(max)

    DECLARE indexcursor CURSOR FOR
    SELECT index_id, name, is_unique, is_unique_constraint, filter_definition FROM sys.indexes WHERE type = 2 and object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']')
    OPEN indexcursor;
    FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
    WHILE @@FETCH_STATUS = 0
       BEGIN
            DECLARE @Unique nvarchar(255)
            SET @Unique = CASE WHEN @IsUnique = 1 THEN ' UNIQUE ' ELSE '' END

            DECLARE @KeyColumns nvarchar(max), @IncludedColumns nvarchar(max)
            SET @KeyColumns = ''
            SET @IncludedColumns = ''

            select @KeyColumns = @KeyColumns + '[' + c.name + '] ' + CASE WHEN is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END + ',' from sys.index_columns ic
            inner join sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
            where index_id = @IndexId and ic.object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']') and key_ordinal > 0
            order by index_column_id

            select @IncludedColumns = @IncludedColumns + '[' + c.name + '],' from sys.index_columns ic
            inner join sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
            where index_id = @IndexId and ic.object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']') and key_ordinal = 0
            order by index_column_id

            IF LEN(@KeyColumns) > 0
                SET @KeyColumns = LEFT(@KeyColumns, LEN(@KeyColumns) - 1)

            IF LEN(@IncludedColumns) > 0
            BEGIN
                SET @IncludedColumns = ' INCLUDE (' + LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1) + ')'
            END

            IF @FilterDefinition IS NULL
                SET @FilterDefinition = ''
            ELSE
                SET @FilterDefinition = 'WHERE ' + @FilterDefinition + ' '

            if @IsUniqueConstraint = 0
                exec('CREATE ' + @Unique + ' NONCLUSTERED INDEX [' + @IndexName + '] ON [' + @DestinationSchema + '].[' + @DestinationTable + '] (' + @KeyColumns + ')' + @IncludedColumns + @FilterDefinition)
            ELSE
                BEGIN
                    SET @IndexName = REPLACE(@IndexName, @SourceTable, @DestinationTable)
                    exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] ADD  CONSTRAINT [' + @IndexName + '] UNIQUE NONCLUSTERED (' + @KeyColumns + ')');
                END

            FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
       END;
    CLOSE indexcursor;
    DEALLOCATE indexcursor;

    --create constraints
    DECLARE @ConstraintName nvarchar(max), @CheckClause nvarchar(max)
    DECLARE constraintcursor CURSOR FOR
        SELECT REPLACE(c.CONSTRAINT_NAME, @SourceTable, @DestinationTable), CHECK_CLAUSE from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE t
        INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS c ON c.CONSTRAINT_SCHEMA = TABLE_SCHEMA AND c.CONSTRAINT_NAME = t.CONSTRAINT_NAME
         WHERE TABLE_SCHEMA = @SourceSchema AND TABLE_NAME = @SourceTable
    OPEN constraintcursor;
    FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
    WHILE @@FETCH_STATUS = 0
       BEGIN
            exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] WITH CHECK ADD  CONSTRAINT [' + @ConstraintName + '] CHECK ' + @CheckClause)
            exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] CHECK CONSTRAINT [' + @ConstraintName + ']')
            FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
       END;
    CLOSE constraintcursor;
    DEALLOCATE constraintcursor;

    COMMIT TRANSACTION
END

Ответ 2

Я внес некоторые изменения в работу Гарета, которая работает BTW, и я думаю, это здорово. Я хотел включить клонирование триггеров и скопировать содержимое таблиц. По сути, "скопируйте" большую часть таблицы, насколько я могу сделать одним выстрелом. Я включил весь фрагмент кода. Помните, что это не совсем оригинально, и я не претендую на заслуги в любой тяжелой работе Gareth. Надеюсь, это полезно для всех, кого это интересует.

CREATE PROCEDURE [dbo].[spCloneTableStructure]
    @SourceSchema nvarchar(255)
    , @SourceTable nvarchar(255)
    , @DestinationSchema nvarchar(255)
    , @DestinationTable nvarchar(255)
    , @RecreateIfExists bit = 0
AS
BEGIN
/*    
Clones an existing table to another table (without data)
Optionally drops and re-creates target table
Copies:
    * Structure
    * Primary key
    * Indexes (including ASC/DESC, included columns, filters)
    * Constraints (and unique constraints)

DOES NOT copy:
    * Triggers (It seems to do this now)
    * File groups
    * Probably a lot of other things

Note: Assumes that you name (unique) constraints with the table name in it (in order to not duplicate constraint names)
*/

    SET NOCOUNT ON;

    BEGIN TRANSACTION

    --drop the table
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @DestinationSchema AND TABLE_NAME = @DestinationTable)
        BEGIN
            IF @RecreateIfExists = 1
                BEGIN
                    EXEC('DROP TABLE [' + @DestinationSchema + '].[' + @DestinationTable + ']')
                END
            ELSE
                BEGIN
                    RETURN
                END
        END

    --create the table
    EXEC('SELECT TOP (0) * INTO [' + @DestinationSchema + '].[' + @DestinationTable + '] FROM [' + @SourceSchema + '].[' + @SourceTable + ']')

    DECLARE @PKSchema nvarchar(255), @PKName nvarchar(255)
    SELECT TOP 1 @PKSchema = CONSTRAINT_SCHEMA, @PKName = CONSTRAINT_NAME 
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
        WHERE TABLE_SCHEMA = @SourceSchema 
        AND TABLE_NAME = @SourceTable 
        AND CONSTRAINT_TYPE = 'PRIMARY KEY'

-----------------------------------------------------------------------------------

DECLARE @SourceColumns int
DECLARE @DestinationColumns int
DECLARE @MyColumn int

SELECT @SourceColumns = count(*) 
    FROM information_schema.columns 
    WHERE TABLE_NAME = @SourceTable
    AND TABLE_SCHEMA = @SourceSchema

SELECT @DestinationColumns = count(*) 
    FROM information_schema.columns 
    WHERE TABLE_NAME = @DestinationTable
    AND TABLE_SCHEMA = @DestinationSchema


IF @SourceColumns = @DestinationColumns
    BEGIN
        DECLARE @FullSourceTable varchar(128)
        DECLARE @FullDestinationTable varchar(128)

        SET @FullSourceTable = @SourceSchema+'.'[email protected]
        SET @FullDestinationTable = @DestinationSchema+'.'[email protected]

        DECLARE @MySQL varchar(MAX)
        DECLARE @MyValues varchar(MAX)

        SET @MyColumn = 2

        SET @MySQL = 'INSERT INTO '[email protected]+' ('

        SET @MyValues = COL_NAME(OBJECT_ID(@FullSourceTable), 1) + ', '

        WHILE @MyColumn <= @DestinationColumns --Change this back 
            BEGIN
                SET @MyValues = @MyValues+ COL_NAME(OBJECT_ID(@FullSourceTable), @MyColumn) + ', '
                SET @MyColumn = @MyColumn + 1
            END

        SELECT @MyValues = SUBSTRING(LTRIM(RTRIM(@MyValues)),1,DATALENGTH(LTRIM(RTRIM(@MyValues)))-1)
        SET @MySQL = @[email protected]+') '
        SET @MySQL = @MySQL+' SELECT '[email protected]+' FROM '[email protected]

        --SELECT @MySQL
        EXEC(@MySQL)
    END
ELSE 
    BEGIN
        RAISERROR('Number of Source and Destination Columns do not match. Cannot continue with copying content.',16,1)
    END

-----------------------------------------------------------------------------------
    --create primary key
    IF NOT @PKSchema IS NULL 
        AND NOT @PKName IS NULL
        BEGIN
            DECLARE @PKColumns nvarchar(MAX)
            SET @PKColumns = ''

            SELECT @PKColumns = @PKColumns + '[' + COLUMN_NAME + '],'
                FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                WHERE TABLE_NAME = @SourceTable 
                AND TABLE_SCHEMA = @SourceSchema 
                AND CONSTRAINT_SCHEMA = @PKSchema 
                AND CONSTRAINT_NAME= @PKName
                ORDER BY ORDINAL_POSITION

            SET @PKColumns = LEFT(@PKColumns, LEN(@PKColumns) - 1)

            EXEC('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] ADD  CONSTRAINT [PK_' + @DestinationTable + '] PRIMARY KEY CLUSTERED (' + @PKColumns + ')');
        END

    --create other indexes
    DECLARE @IndexId int, @IndexName nvarchar(255), @IsUnique bit, @IsUniqueConstraint bit, @FilterDefinition nvarchar(max)
-------------------------------------------------------------------------------
-- Cursor Start
-------------------------------------------------------------------------------

    DECLARE indexcursor CURSOR FOR

        SELECT index_id, name, is_unique, is_unique_constraint, filter_definition 
            FROM sys.indexes 
                WHERE type = 2 
                AND object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']')

    OPEN indexcursor;
    FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
    WHILE @@FETCH_STATUS = 0
       BEGIN
            DECLARE @Unique nvarchar(255)
            DECLARE @KeyColumns nvarchar(max), @IncludedColumns nvarchar(max)


            SET @Unique = CASE WHEN @IsUnique = 1 THEN ' UNIQUE ' ELSE '' END
            SET @KeyColumns = ''
            SET @IncludedColumns = ''

            SELECT @KeyColumns = @KeyColumns + '[' + c.name + '] ' + CASE WHEN is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END + ',' 
                FROM sys.index_columns ic
                    INNER JOIN sys.columns c 
                        ON c.object_id = ic.object_id 
                        AND c.column_id = ic.column_id
                WHERE index_id = @IndexId 
                AND ic.object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']') 
                AND key_ordinal > 0
                ORDER BY index_column_id

            SELECT @IncludedColumns = @IncludedColumns + '[' + c.name + '],' 
                FROM sys.index_columns ic
                    INNER JOIN sys.columns c 
                        ON c.object_id = ic.object_id 
                        AND c.column_id = ic.column_id
                WHERE index_id = @IndexId 
                AND ic.object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']') 
                AND key_ordinal = 0
                ORDER BY index_column_id

            IF LEN(@KeyColumns) > 0
                BEGIN
                    SET @KeyColumns = LEFT(@KeyColumns, LEN(@KeyColumns) - 1)
                END

            IF LEN(@IncludedColumns) > 0
                BEGIN
                    SET @IncludedColumns = ' INCLUDE (' + LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1) + ')'
                END

            IF @FilterDefinition IS NULL
                BEGIN
                    SET @FilterDefinition = ''
                END
            ELSE
                BEGIN
                    SET @FilterDefinition = 'WHERE ' + @FilterDefinition + ' '
                END

            IF @IsUniqueConstraint = 0
                BEGIN
                    EXEC('CREATE ' + @Unique + ' NONCLUSTERED INDEX [' + @IndexName + '] ON [' + @DestinationSchema + '].[' + @DestinationTable + '] (' + @KeyColumns + ')' + @IncludedColumns + @FilterDefinition)
                END
            ELSE
                BEGIN
                    SET @IndexName = REPLACE(@IndexName, @SourceTable, @DestinationTable)
                    EXEC('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] ADD  CONSTRAINT [' + @IndexName + '] UNIQUE NONCLUSTERED (' + @KeyColumns + ')');
                END

            FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
       END;
    CLOSE indexcursor;
    DEALLOCATE indexcursor;

-------------------------------------------------------------------------------
-- Cursor END
-------------------------------------------------------------------------------
    --create constraints
    DECLARE @ConstraintName nvarchar(max), @CheckClause nvarchar(max)
-------------------------------------------------------------------------------
-- Cursor START
-------------------------------------------------------------------------------
    DECLARE constraintcursor CURSOR FOR

        SELECT REPLACE(c.CONSTRAINT_NAME, @SourceTable, @DestinationTable), CHECK_CLAUSE 
            FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE t
                INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS c 
                    ON c.CONSTRAINT_SCHEMA = TABLE_SCHEMA 
                    AND c.CONSTRAINT_NAME = t.CONSTRAINT_NAME
            WHERE TABLE_SCHEMA = @SourceSchema 
            AND TABLE_NAME = @SourceTable

    OPEN constraintcursor;
    FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
    WHILE @@FETCH_STATUS = 0
       BEGIN
            EXEC('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] WITH CHECK ADD  CONSTRAINT [' + @ConstraintName + '] CHECK ' + @CheckClause)
            EXEC('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] CHECK CONSTRAINT [' + @ConstraintName + ']')
            FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
       END;
    CLOSE constraintcursor;
    DEALLOCATE constraintcursor;

-------------------------------------------------------------------------------
-- Cursor END
-------------------------------------------------------------------------------


-------------------------------------------------------------------------------
-- Build Triggers on new table START
-------------------------------------------------------------------------------

DECLARE @TriggerType varchar(32)
DECLARE @CHeader varchar(255)

DECLARE @trigger_name varchar(128)
DECLARE @table_schema varchar(128)
DECLARE @table_name varchar(128)
DECLARE @isupdate tinyint
DECLARE @isdelete tinyint
DECLARE @isinsert tinyint
DECLARE @isafter tinyint
DECLARE @isinsteadof tinyint
DECLARE @disabled tinyint
DECLARE @TriggerCode varchar(MAX)

DECLARE db_cursor CURSOR FOR

SELECT so.name 
    ,( SELECT TOP 1 SCHEMA_NAME(T1.schema_id)
                        FROM sys.tables AS T1
                        WHERE  T1.name = OBJECT_NAME(parent_obj))
    ,OBJECT_NAME(parent_obj)
    ,OBJECTPROPERTY(so.id, 'ExecIsUpdateTrigger')
    ,OBJECTPROPERTY(so.id, 'ExecIsDeleteTrigger')
    ,OBJECTPROPERTY(so.id, 'ExecIsInsertTrigger')
    ,OBJECTPROPERTY(so.id, 'ExecIsAfterTrigger')
    ,OBJECTPROPERTY(so.id, 'ExecIsInsteadOfTrigger')
    ,OBJECTPROPERTY(so.id, 'ExecIsTriggerDisabled')
    ,LTRIM(RTRIM(c.[text]))
    FROM sys.sysobjects AS so
        INNER JOIN sys.objects o ON so.id = o.object_id
        INNER JOIN  sys.syscomments AS c ON o.object_id = c.id
    WHERE so.type = 'TR'
    AND OBJECT_NAME(parent_object_id) = @SourceTable

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @trigger_name, @table_schema, @table_name, @isupdate, @isdelete, @isinsert, @isafter, @isinsteadof, @disabled, @TriggerCode

WHILE @@FETCH_STATUS = 0   
BEGIN   
       --SELECT  @trigger_name, @table_schema, @table_name, @isupdate, @isdelete, @isinsert, @isafter, @isinsteadof, @disabled, @TriggerCode

        SET @TriggerCode = LTRIM(RTRIM(REPLACE(@TriggerCode, CHAR(13)+CHAR(13)+CHAR(13), CHAR(13))))
        SET @TriggerCode = LTRIM(RTRIM(REPLACE(@TriggerCode, CHAR(13)+CHAR(13), CHAR(13))))
-------------------------------------------------------------------------------
--Which one is first?
-------------------------------------------------------------------------------
        DECLARE @MyStart tinyint
        DECLARE @MyForStart tinyint
        DECLARE @MyAfterStart tinyint
        DECLARE @MyInsteadStart tinyint

        SELECT @MyForStart = CHARINDEX('for',@TriggerCode)
        SELECT @TriggerType = LTRIM(RTRIM(SUBSTRING(@TriggerCode,CHARINDEX('for',@TriggerCode)-1, 4 )))

        SELECT @MyAfterStart = CHARINDEX('after',@TriggerCode)
        SELECT @TriggerType = LTRIM(RTRIM(SUBSTRING(@TriggerCode,CHARINDEX('after',@TriggerCode)-1, 6 )))

        SELECT @MyInsteadStart = CHARINDEX('instead',@TriggerCode)
        SELECT @TriggerType = LTRIM(RTRIM(SUBSTRING(@TriggerCode,CHARINDEX('Instead',@TriggerCode)-1, 8 )))

        IF @MyAfterStart <> 0
            AND @MyAfterStart < @MyForStart
            BEGIN
                SET @MyStart = @MyAfterStart
                SELECT @TriggerType = LTRIM(RTRIM(SUBSTRING(@TriggerCode,@MyStart-1, 6 )))
            END
        ELSE IF @MyInsteadStart <> 0
            AND @MyInsteadStart < @MyForStart 
            BEGIN
                SET @MyStart = @MyInsteadStart
                SELECT @TriggerType = LTRIM(RTRIM(SUBSTRING(@TriggerCode,@MyStart-1, 8 )))
            END
        ELSE IF @MyForStart <> 0
            AND @MyForStart < @MyAfterStart
            AND @MyForStart < @MyInsteadStart
            BEGIN
                SET @MyStart = @MyForStart
                SELECT @TriggerType = LTRIM(RTRIM(SUBSTRING(@TriggerCode,@MyStart-1, 4 )))
            END
-------------------------------------------------------------------------------
--Build the correct header and append it to the create trigger code then run it
-------------------------------------------------------------------------------
        IF @TriggerType LIKE '%FOR%'
            BEGIN
                SET @CHeader = 'CREATE TRIGGER ['[email protected]+'].['[email protected]_name+'] ON ['[email protected]+'].['[email protected]+']'
                --print @CHeader+char(13)+SUBSTRING(@TriggerCode,CHARINDEX('FOR',@TriggerCode)-1,DATALENGTH(@TriggerCode))
                SET @TriggerCode = @CHeader+char(13)+SUBSTRING(@TriggerCode,CHARINDEX('for',@TriggerCode)-1,DATALENGTH(@TriggerCode))
                EXEC(@TriggerCode)
            END
        ELSE IF @TriggerType LIKE '%AFTER%'
            BEGIN
                SET @CHeader = 'CREATE TRIGGER ['[email protected]+'].['[email protected]_name+'] ON ['[email protected]+'].['[email protected]+']'
                --print @CHeader+char(13)+SUBSTRING(@TriggerCode,CHARINDEX('AFTER',@TriggerCode)-1,DATALENGTH(@TriggerCode))
                SET @TriggerCode = @CHeader+char(13)+SUBSTRING(@TriggerCode,CHARINDEX('after',@TriggerCode)-1,DATALENGTH(@TriggerCode))
                EXEC(@TriggerCode)
            END
        ELSE IF @TriggerType LIKE '%INSTEAD%'
            BEGIN
                SET @CHeader = 'CREATE TRIGGER ['[email protected]+'].['[email protected]_name+'] ON ['[email protected]+'].['[email protected]+']'
                --print @CHeader+char(13)+SUBSTRING(@TriggerCode,CHARINDEX('INSTEAD',@TriggerCode)-1,DATALENGTH(@TriggerCode))
                SET @TriggerCode = @CHeader+char(13)+SUBSTRING(@TriggerCode,CHARINDEX('instead',@TriggerCode)-1,DATALENGTH(@TriggerCode))
                EXEC(@TriggerCode)
            END

       FETCH NEXT FROM db_cursor INTO @trigger_name, @table_schema, @table_name, @isupdate, @isdelete, @isinsert, @isafter, @isinsteadof, @disabled, @TriggerCode
END   

CLOSE db_cursor   
DEALLOCATE db_cursor


COMMIT TRANSACTION

END

Ответ 3

Чтобы дать вам представление о том, что связано (и почему вы должны использовать SMO для этого, как указано в комментариях):

  • Получить список индексов из sys.indexes на основе object_ID исходной таблицы
    • Это включает в себя другую информацию типа UNIQUE, PK, IGNORE_DUP_KEY, FILL_FACTOR, PADDED, DISABLED, ROW LOCKS, PAGE LOCKS , которые все должны быть закодированы отдельно в динамическом SQL
  • Получить список всех ключевых полей (и их порядок, а также ASC или DESC) для каждого индекса из sys.index_columns. Это будет включать JOIN ing sys.columns для имен, поскольку все они являются столбцами ID s
  • Получить список включенных полей из sys.index_columns
  • Получить список фильтров для каждого индекса из sys.indexes

Теперь переведите все приведенные выше данные в действительные сценарии SQL для выполнения в целевой таблице.

Для эффективности вы также должны script вывести кластеризованный индекс и запустить его, поскольку для создания кластера потребуется больше времени, если уже есть некластеризованные индексы.

Ответ 4

Это быстрый и грязный мод, основанный на этом вопросе: Сгенерировать сценарии CREATE для списка индексов, но должен получить от вас большую часть пути. Вы можете сделать то, что вы уже делаете, чтобы клонировать структуру, и вы используете версию этого proc для их индексов, а затем, при необходимости, изменяете:

   CREATE sp_CloneIndex   @OldTableName varchar(50), @NewTableName varchar(50)
  AS

WITH indexCTE AS
(
SELECT DISTINCT 
    i.index_id, i.name, i.object_id
FROM 
    sys.indexes i 
INNER JOIN
    sys.index_columns ic 
       ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE 
    EXISTS (SELECT * FROM sys.columns c WHERE c.column_id = ic.column_id AND c.object_id = ic.object_id)
), 
indexCTE2 AS
(
SELECT 
    indexCTE.name 'IndexName', 
    OBJECT_NAME(indexCTE.object_ID) 'TableName',
    CASE indexCTE.index_id 
      WHEN 1 THEN 'CLUSTERED'
      ELSE 'NONCLUSTERED'
    END AS 'IndexType', 
    (SELECT DISTINCT c.name + ','
     FROM 
        sys.columns c 
     INNER JOIN
        sys.index_columns ic 
           ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.Is_Included_Column = 0
     WHERE
        indexCTE.OBJECT_ID = ic.object_id 
        AND indexCTE.index_id = ic.index_id 
     FOR XML PATH('')
    ) ixcols,
    ISNULL(
    (SELECT DISTINCT c.name + ','
     FROM 
        sys.columns c 
     INNER JOIN
        sys.index_columns ic 
           ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.Is_Included_Column = 1
     WHERE
        indexCTE.OBJECT_ID = ic.object_id 
        AND indexCTE.index_id = ic.index_id 
     FOR XML PATH('')
    ), '') includedcols
FROM 
    indexCTE
) 
SELECT 
'CREATE ' + IndexType + ' INDEX ' + IndexName + ' ON ' + @NewTableName + 
    '(' + SUBSTRING(ixcols, 1, LEN(ixcols)-1) + 
    CASE LEN(includedcols)
      WHEN 0 THEN ')'
      ELSE ') INCLUDE (' + SUBSTRING(includedcols, 1, LEN(includedcols)-1) + ')'
    END
FROM 
 indexCTE2
where tablename = @OldTableName
ORDER BY 
  TableName, IndexName

Ответ 5

Спасибо Гарет. Это работает. Я изменил работу с базами данных:

CREATE PROCEDURE [dbo].[spCloneDatabaseTableStructurev3] 
    @SourceDatabase nvarchar(max),
    @SourceSchema nvarchar(max),
    @SourceTable nvarchar(max),
    @DestinationDatabase nvarchar(max),
    @DestinationSchema nvarchar(max),
    @DestinationTable nvarchar(max),
    @RecreateIfExists bit = 0
AS
BEGIN
    /*
        Clones an existing table to another table (without data)
        Optionally drops and re-creates target table
        Copies:
            * Structure
            * Primary key
            * Indexes (including ASC/DESC, included columns, filters)
            * Constraints (and unique constraints)

        DOES NOT copy:
            * Triggers
            * File groups
            * Probably a lot of other things

        Note: Assumes that you name (unique) constraints with the table name in it (in order to not duplicate constraint names)
    */
    declare @sql nvarchar(max)

    SET NOCOUNT ON;

    BEGIN TRANSACTION

    set @sql = 'USE UK_Health_Facts_Repository;
        declare @RecreateIfExists bit = ' + convert(varchar(max),@RecreateIfExists) + '
        --drop the table
        if EXISTS (SELECT * FROM [' + @DestinationDatabase + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''' + @DestinationSchema + ''' AND TABLE_NAME = ''' + @DestinationTable + ''')
        BEGIN
            if @RecreateIfExists = 1
            BEGIN
                DROP TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + ']
            END
            ELSE
                RETURN
        END

        --create the table
        SELECT TOP (0) * INTO [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] FROM [' + @SourceDatabase + '].[' + @SourceSchema + '].[' + @SourceTable + ']

        DECLARE @PKSchema nvarchar(255), @PKName nvarchar(255)
        SELECT TOP 1 @PKSchema = CONSTRAINT_SCHEMA, @PKName = CONSTRAINT_NAME FROM [' + @SourceDatabase + '].INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = ''' + @SourceSchema + ''' AND TABLE_NAME = ''' + @SourceTable + ''' AND CONSTRAINT_TYPE = ''PRIMARY KEY''

        --create primary key
        IF NOT @PKSchema IS NULL AND NOT @PKName IS NULL
        BEGIN
            DECLARE @PKColumns nvarchar(MAX)
            SET @PKColumns = ''''

            SELECT @PKColumns = @PKColumns + ''['' + COLUMN_NAME + ''],''
                FROM [' + @SourceDatabase + '].INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                where TABLE_NAME = ''' + @SourceTable + ''' and TABLE_SCHEMA = ''' + @SourceSchema + ''' AND CONSTRAINT_SCHEMA = @PKSchema AND CONSTRAINT_NAME= @PKName
                ORDER BY ORDINAL_POSITION

            SET @PKColumns = LEFT(@PKColumns, LEN(@PKColumns) - 1)

            exec(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] ADD  CONSTRAINT [PK_' + @DestinationTable + '] PRIMARY KEY CLUSTERED ('' + @PKColumns + '')'')
        END

        --create other indexes
        DECLARE @IndexId int, @IndexName nvarchar(255), @IsUnique bit, @IsUniqueConstraint bit, @FilterDefinition nvarchar(max)

        DECLARE indexcursor CURSOR FOR
        SELECT index_id, name, is_unique, is_unique_constraint, filter_definition FROM sys.indexes WHERE type = 2 and object_id = object_id(''[' + @SourceSchema + '].[' + @SourceTable + ']'')
        OPEN indexcursor;
        FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
        WHILE @@FETCH_STATUS = 0
           BEGIN
                DECLARE @Unique nvarchar(255)
                SET @Unique = CASE WHEN @IsUnique = 1 THEN '' UNIQUE '' ELSE '''' END

                DECLARE @KeyColumns nvarchar(max), @IncludedColumns nvarchar(max)
                SET @KeyColumns = ''''
                SET @IncludedColumns = ''''

                select @KeyColumns = @KeyColumns + ''['' + c.name + ''] '' + CASE WHEN is_descending_key = 1 THEN ''DESC'' ELSE ''ASC'' END + '','' from sys.index_columns ic
                inner join sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
                where index_id = @IndexId and ic.object_id = object_id(''[' + @SourceSchema + '].[' + @SourceTable + ']'') and key_ordinal > 0
                order by index_column_id

                select @IncludedColumns = @IncludedColumns + ''['' + c.name + ''],'' from sys.index_columns ic
                inner join sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
                where index_id = @IndexId and ic.object_id = object_id(''[' + @SourceSchema + '].[' + @SourceTable + ']'') and key_ordinal = 0
                order by index_column_id

                IF LEN(@KeyColumns) > 0
                    SET @KeyColumns = LEFT(@KeyColumns, LEN(@KeyColumns) - 1)

                IF LEN(@IncludedColumns) > 0
                BEGIN
                    SET @IncludedColumns = '' INCLUDE ('' + LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1) + '')''
                END

                IF @FilterDefinition IS NULL
                    SET @FilterDefinition = ''''
                ELSE
                    SET @FilterDefinition = ''WHERE '' + @FilterDefinition + '' ''

                if @IsUniqueConstraint = 0
                    exec(''CREATE '' + @Unique + '' NONCLUSTERED INDEX ['' + @IndexName + ''] ON [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] ('' + @KeyColumns + '')'' + @IncludedColumns + @FilterDefinition)
                ELSE
                    BEGIN
                        SET @IndexName = REPLACE(@IndexName, ''' + @SourceTable + ''', ''' + @DestinationTable + ''')
                        exec(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] ADD  CONSTRAINT ['' + @IndexName + ''] UNIQUE NONCLUSTERED ('' + @KeyColumns + '')'')
                    END

                FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
           END;
        CLOSE indexcursor;
        DEALLOCATE indexcursor;

        --create constraints
        DECLARE @ConstraintName nvarchar(max), @CheckClause nvarchar(max)
        DECLARE constraintcursor CURSOR FOR
            SELECT REPLACE(c.CONSTRAINT_NAME, ''' + @SourceTable + ''', ''' + @DestinationTable + '''), CHECK_CLAUSE from [' + @SourceDatabase + '].INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE t
            INNER JOIN [' + @SourceDatabase + '].INFORMATION_SCHEMA.CHECK_CONSTRAINTS c ON c.CONSTRAINT_SCHEMA = TABLE_SCHEMA AND c.CONSTRAINT_NAME = t.CONSTRAINT_NAME
             WHERE TABLE_SCHEMA = ''' + @SourceSchema + ''' AND TABLE_NAME = ''' + @SourceTable + '''
        OPEN constraintcursor;
        FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
        WHILE @@FETCH_STATUS = 0
           BEGIN
                exec(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] WITH CHECK ADD  CONSTRAINT ['' + @ConstraintName + ''] CHECK '' + @CheckClause)
                exec(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] CHECK CONSTRAINT ['' + @ConstraintName + '']'')
                FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
           END;
        CLOSE constraintcursor;
        DEALLOCATE constraintcursor;'

    exec(@sql)

    COMMIT TRANSACTION
END

Ответ 6

Спасибо Гарет. Хорошая работа! Мне нужен TSQL script, который работает через базы данных. В основном, что сделал F_Face. Я скорректировал модификацию F_Face, потому что все еще есть твердое имя базы данных, и некоторые утверждения полагаются на это.

В любом случае, я согласен со всеми комментариями, что это не так, как должна быть сделана глубокая структурная копия таблицы. Использовать SMO через PowerShell через xp_cmdshell через TSQL, чтобы сделать это в режиме propper, было бы моим предложением. Таким образом SMO заботится о том, чтобы все было скопировано и что следующее поколение SQL Server работает вместе с ним.

Здесь упоминается код:

--If the SP exists, we do nothing. If we would drop it, we would loose security settings. 
--If the SP doesn't exist, create a dummy SP to be able to use ALTER PROCEDURE in both cases. 
DECLARE @spName varchar(255)
SET @spName='spCloneTableStructure'
IF object_id(@spName) IS NULL --does the SP exist?
    EXEC ('CREATE PROCEDURE dbo.'[email protected]+' AS SELECT 1') --create dummy sp
GO
ALTER PROCEDURE [dbo].[spCloneTableStructure] 
    @SourceDatabase nvarchar(max),
    @SourceSchema nvarchar(max),
    @SourceTable nvarchar(max),
    @DestinationDatabase nvarchar(max),
    @DestinationSchema nvarchar(max),
    @DestinationTable nvarchar(max),
    @RecreateIfExists bit = 0
AS
BEGIN
    /*
    Clones an existing table to another table (without data)
    Optionally drops and re-creates target table
    Copies:
        * Structure
        * Primary key
        * Indexes (including ASC/DESC, included columns, filters)
        * Constraints (and unique constraints)

    DOES NOT copy:
        * Triggers
        * File groups
        * Probably a lot of other things

    Note: Assumes that you name (unique) constraints with the table name in it (in order to not duplicate constraint names)
*/
DECLARE @sql NVARCHAR(MAX)

SET NOCOUNT ON;

BEGIN TRANSACTION

set @sql = 
    'DECLARE @RecreateIfExists bit = ' + convert(varchar(max),@RecreateIfExists) + '
    --drop the table
    if EXISTS (SELECT * FROM [' + @DestinationDatabase + '].INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = ''' + @DestinationSchema + ''' 
                 AND TABLE_NAME = ''' + @DestinationTable + ''')
    BEGIN
        IF @RecreateIfExists = 1
        BEGIN
            DROP TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + ']
        END
        ELSE
            RETURN
    END

    --create the table
    SELECT TOP (0) * INTO [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] 
      FROM [' + @SourceDatabase + '].[' + @SourceSchema + '].[' + @SourceTable + ']

    DECLARE @PKSchema nvarchar(255), @PKName nvarchar(255)
    SELECT TOP 1 @PKSchema = CONSTRAINT_SCHEMA, @PKName = CONSTRAINT_NAME 
      FROM [' + @SourceDatabase + '].INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
      WHERE TABLE_SCHEMA = ''' + @SourceSchema + ''' 
      AND TABLE_NAME = ''' + @SourceTable + ''' 
      AND CONSTRAINT_TYPE = ''PRIMARY KEY''

    --create primary key
    IF NOT @PKSchema IS NULL AND NOT @PKName IS NULL
    BEGIN
        DECLARE @PKColumns nvarchar(MAX)
        SET @PKColumns = ''''

        SELECT @PKColumns = @PKColumns + ''['' + COLUMN_NAME + ''],''
          FROM [' + @SourceDatabase + '].INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
          WHERE TABLE_NAME = ''' + @SourceTable + ''' 
          AND TABLE_SCHEMA = ''' + @SourceSchema + ''' 
          AND CONSTRAINT_SCHEMA = @PKSchema 
          AND CONSTRAINT_NAME= @PKName
          ORDER BY ORDINAL_POSITION

        SET @PKColumns = LEFT(@PKColumns, LEN(@PKColumns) - 1)

        EXEC(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] 
               ADD CONSTRAINT [PK_' + @DestinationTable + '] PRIMARY KEY CLUSTERED ('' + @PKColumns + '')'')
    END

    --create other indexes
    DECLARE @IndexId int, @IndexName nvarchar(255), @IsUnique bit, @IsUniqueConstraint bit, @FilterDefinition nvarchar(max)

    DECLARE indexcursor CURSOR FOR
    SELECT index_id, name, is_unique, is_unique_constraint, filter_definition 
      FROM ['[email protected]+'].sys.indexes 
      WHERE type = 2 
      AND object_id = object_id(''['[email protected]+'].[' + @SourceSchema + '].[' + @SourceTable + ']'')
    OPEN indexcursor;
    FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
    WHILE @@FETCH_STATUS = 0
       BEGIN
            DECLARE @Unique nvarchar(255)
            SET @Unique = CASE WHEN @IsUnique = 1 THEN '' UNIQUE '' ELSE '''' END

            DECLARE @KeyColumns nvarchar(max), @IncludedColumns nvarchar(max)
            SET @KeyColumns = ''''
            SET @IncludedColumns = ''''

            SELECT @KeyColumns = @KeyColumns + ''['' + c.name + ''] '' + CASE WHEN is_descending_key = 1 THEN ''DESC'' ELSE ''ASC'' END + '','' 
              FROM ['[email protected]+'].sys.index_columns ic
              INNER JOIN ['[email protected]+'].sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
              WHERE index_id = @IndexId 
              AND ic.object_id = object_id(''['[email protected]+'].[' + @SourceSchema + '].[' + @SourceTable + ']'') 
              AND key_ordinal > 0
              ORDER BY index_column_id

            SELECT @IncludedColumns = @IncludedColumns + ''['' + c.name + ''],'' from ['[email protected]+'].sys.index_columns ic
              INNER JOIN ['[email protected]+'].sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
              WHERE index_id = @IndexId 
              AND ic.object_id = object_id(''['[email protected]+'].[' + @SourceSchema + '].[' + @SourceTable + ']'') 
              AND key_ordinal = 0
              ORDER BY index_column_id

            IF LEN(@KeyColumns) > 0
                SET @KeyColumns = LEFT(@KeyColumns, LEN(@KeyColumns) - 1)

            IF LEN(@IncludedColumns) > 0
            BEGIN
                SET @IncludedColumns = '' INCLUDE ('' + LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1) + '')''
            END

            IF @FilterDefinition IS NULL
                SET @FilterDefinition = ''''
            ELSE
                SET @FilterDefinition = ''WHERE '' + @FilterDefinition + '' ''

            if @IsUniqueConstraint = 0
                exec(''CREATE '' + @Unique + '' NONCLUSTERED INDEX ['' + @IndexName + ''] ON [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] ('' + @KeyColumns + '')'' + @IncludedColumns + @FilterDefinition)
            ELSE
                BEGIN
                    SET @IndexName = REPLACE(@IndexName, ''' + @SourceTable + ''', ''' + @DestinationTable + ''')
                    exec(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] ADD  CONSTRAINT ['' + @IndexName + ''] UNIQUE NONCLUSTERED ('' + @KeyColumns + '')'')
                END

            FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
       END;
    CLOSE indexcursor;
    DEALLOCATE indexcursor;

    --create constraints
    DECLARE @ConstraintName nvarchar(max), @CheckClause nvarchar(max)
    DECLARE constraintcursor CURSOR FOR
        SELECT REPLACE(c.CONSTRAINT_NAME, ''' + @SourceTable + ''', ''' + @DestinationTable + '''), CHECK_CLAUSE 
          FROM [' + @SourceDatabase + '].INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE t
          INNER JOIN [' + @SourceDatabase + '].INFORMATION_SCHEMA.CHECK_CONSTRAINTS c ON c.CONSTRAINT_SCHEMA = TABLE_SCHEMA AND c.CONSTRAINT_NAME = t.CONSTRAINT_NAME
          WHERE TABLE_SCHEMA = ''' + @SourceSchema + ''' 
          AND TABLE_NAME = ''' + @SourceTable + '''
    OPEN constraintcursor;
    FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
    WHILE @@FETCH_STATUS = 0
       BEGIN
            exec(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] WITH CHECK ADD  CONSTRAINT ['' + @ConstraintName + ''] CHECK '' + @CheckClause)
            exec(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] CHECK CONSTRAINT ['' + @ConstraintName + '']'')
            FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
       END;
    CLOSE constraintcursor;
    DEALLOCATE constraintcursor;'

    --PRINT SUBSTRING(@sql, 0, 4000)
    --PRINT SUBSTRING(@sql, 4000, 8000)

    EXEC(@sql)

    COMMIT TRANSACTION
END

Ответ 7

Вот моя версия. Целевая таблица и схема те же, что и источник, с добавлением чего-то в конце в моем случае _STG. Вы можете добавить что-то в конце, используя переменную @NameAdd NVARCHAR (128). Я сделал это таким образом, чтобы избежать инъекции sql. В моей версии внешний текст не был выполнен.

IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name='uspCloneTableStructure')
BEGIN
    DROP PROCEDURE [dbo].[uspCloneTableStructure]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspCloneTableStructure]
    @inSourceSchema nvarchar(128),
    @inSourceTable nvarchar(128),
    @RecreateTable bit = 0,
    @RecreateIndexes bit = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @RecID INT
       ,@RecCount INT
       ,@ExecuteCMD NVARCHAR(MAX) = ''
       ,@DateTime VARCHAR(100)
       ,@SourceSchema NVARCHAR(128)
       ,@SourceTable NVARCHAR(128)
       ,@DestinationSchema NVARCHAR(128)
       ,@DestinationTable NVARCHAR(128)
       ,@NameAdd NVARCHAR(128) = N'_STG';

    BEGIN TRANSACTION;
    BEGIN TRY;
        SET XACT_ABORT ON;
        SELECT  @SourceSchema = s.name
               ,@SourceTable = t.name
               ,@DestinationSchema = s.name
               ,@DestinationTable = t.name + @NameAdd
        FROM    sys.tables AS t
        INNER JOIN sys.schemas AS s
        ON      s.schema_id = t.schema_id
        WHERE   s.name = @inSourceSchema
                AND t.name = @inSourceTable;

        --drop the table
        if @RecreateTable = 1 AND @DestinationSchema IS NOT NULL AND @DestinationTable IS NOT NULL
        BEGIN
            SET @ExecuteCMD ='IF EXISTS (SELECT *   FROM sys.tables AS t 
                                        INNER JOIN sys.schemas AS s
                                            ON s.schema_id = t.schema_id
                                        WHERE s.name = ''' + @DestinationSchema + ''' AND t.name = ''' + @DestinationTable + ''')
                                            DROP TABLE [' + @DestinationSchema + '].[' + @DestinationTable + ']

                              SELECT TOP (0) * INTO [' + @DestinationSchema + '].[' + @DestinationTable + '] FROM [' + @SourceSchema + '].[' + @SourceTable + ']';
            SELECT @DateTime = CONVERT(VARCHAR(100),GETDATE(),(121));
            RAISERROR('--Creating table: %s at: %s ',0,1,@DestinationTable,@DateTime) WITH NOWAIT;  
            PRINT @ExecuteCMD;
            EXECUTE sp_executesql @ExecuteCMD;
        END;

        IF @RecreateIndexes = 1
        BEGIN

            --create other indexes
            DECLARE @IndexId INT
               ,@IndexName NVARCHAR(128)
               ,@FilterDefinition NVARCHAR(MAX)
               ,@IsPrimaryKey BIT
               ,@Unique NVARCHAR(128)
               ,@Clustered NVARCHAR(128)
               ,@DataCompression NVARCHAR(60)
               ,@KeyColumns NVARCHAR(MAX)
               ,@IncludedColumns NVARCHAR(MAX);

            IF OBJECT_ID('tempdb.dbo.#Indexes') IS NOT NULL
            BEGIN 
                DROP TABLE dbo.#Indexes;
            END;

            CREATE TABLE dbo.#Indexes
                (
                 [RecID] INT IDENTITY(1, 1) PRIMARY KEY
                ,IndexId INT
                ,IndexName NVARCHAR(128)
                ,IsUnique BIT
                ,FilterDefinition NVARCHAR(MAX)
                ,IsClustered INT
                ,IsPrimaryKey BIT
                ,DataCompression NVARCHAR(60)
                );

            INSERT INTO dbo.#Indexes
                    ( IndexId
                    ,IndexName
                    ,IsUnique
                    ,FilterDefinition
                    ,IsClustered
                    ,IsPrimaryKey
                    ,DataCompression )
            SELECT  i.index_id
                   ,i.name
                   ,i.is_unique
                   ,i.filter_definition
                   ,i.index_id
                   ,i.is_primary_key
                   ,sp.data_compression_desc
            FROM    sys.indexes AS i
            INNER JOIN sys.tables AS t
            ON      t.[object_id] = i.[object_id]
            INNER JOIN sys.schemas AS s
            ON      s.[schema_id] = t.[schema_id]
            INNER JOIN sys.partitions AS sp 
            ON      i.[object_id] = sp.[object_id]
                    AND i.[index_id] = sp.[index_id]
                    AND sp.partition_number = 1
            WHERE   i.type <>0 
                    AND s.name = @SourceSchema
                    AND t.name = @SourceTable;

            SELECT @RecCount = COUNT(*) FROM dbo.#Indexes;
            SET @RecID = 1;
            WHILE (@RecID <= @RecCount)     
            BEGIN
                SELECT  @IndexId = IndexId
                       ,@IndexName = IndexName
                       ,@Unique = CASE WHEN IsUnique = 1 THEN ' UNIQUE ' ELSE '' END
                       ,@FilterDefinition = FilterDefinition
                       ,@Clustered = CASE WHEN IsClustered = 1 THEN ' CLUSTERED ' ELSE ' NONCLUSTERED ' END
                       ,@IsPrimaryKey = IsPrimaryKey
                       ,@DataCompression = DataCompression
                       ,@KeyColumns = ''
                       ,@IncludedColumns = ''
                FROM    dbo.#Indexes
                WHERE   [RecID] = @RecID;

                SELECT  @KeyColumns = @KeyColumns + '[' + c.name + '] '
                        + CASE WHEN is_descending_key = 1 THEN 'DESC'
                               ELSE 'ASC'
                          END + ','
                FROM    sys.index_columns ic
                INNER JOIN sys.columns c
                ON c.object_id = ic.object_id
                    AND c.column_id = ic.column_id
                INNER JOIN sys.tables AS t
                ON t.object_id = c.object_id
                INNER JOIN sys.schemas AS s
                ON s.schema_id = t.schema_id
                WHERE   ic.index_id = @IndexId
                    AND s.name = @SourceSchema
                    AND t.name = @SourceTable
                    AND key_ordinal > 0
                ORDER BY index_column_id;

                SELECT  @IncludedColumns = @IncludedColumns + '[' + c.name + '],'
                FROM    sys.index_columns ic
                INNER JOIN sys.columns c
                ON      c.object_id = ic.object_id
                        AND c.column_id = ic.column_id
                INNER JOIN sys.tables AS t
                ON t.object_id = c.object_id
                INNER JOIN sys.schemas AS s
                ON s.schema_id = t.schema_id
                WHERE   ic.index_id = @IndexId
                    AND s.name = @SourceSchema
                    AND t.name = @SourceTable
                    AND key_ordinal = 0
                ORDER BY index_column_id;

                IF LEN(@KeyColumns) > 0
                    SET @KeyColumns = LEFT(@KeyColumns, LEN(@KeyColumns) - 1);

                IF LEN(@IncludedColumns) > 0
                BEGIN
                    SET @IncludedColumns = ' INCLUDE (' + LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1) + ')';
                END

                IF @FilterDefinition IS NULL
                    SET @FilterDefinition = '';
                ELSE
                    SET @FilterDefinition = 'WHERE ' + @FilterDefinition + ' ';

                --create the index or PK
                IF @IsPrimaryKey = 1
                    SET @ExecuteCMD = 'ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] ADD  CONSTRAINT [' + @IndexName + @NameAdd + '] PRIMARY KEY CLUSTERED (' + @KeyColumns + ') WITH (SORT_IN_TEMPDB=ON,DATA_COMPRESSION='[email protected]+');';
                ELSE
                    SET @ExecuteCMD = 'CREATE ' + @Unique + @Clustered + ' INDEX [' + @IndexName + '] ON [' + @DestinationSchema + '].[' + @DestinationTable + '] (' + @KeyColumns + ')' + @IncludedColumns + @FilterDefinition + ' WITH (SORT_IN_TEMPDB=ON,DATA_COMPRESSION='[email protected]+');';

                SELECT @DateTime = CONVERT(VARCHAR(100),GETDATE(),(121));
                RAISERROR('--Creating index: %s%s at: %s ',0,1,@IndexName,@NameAdd,@DateTime) WITH NOWAIT;  
                PRINT @ExecuteCMD;
                EXECUTE sp_executesql @ExecuteCMD;

                SET @RecID = @RecID + 1;

            END;/*While loop*/
        END;

        COMMIT TRAN;
        SET XACT_ABORT OFF;
    END TRY BEGIN CATCH;
        SET XACT_ABORT OFF;
        IF (XACT_STATE() != 0)
        BEGIN;
            ROLLBACK TRANSACTION;
        END;
        THROW;
        -- RETURN;
    END CATCH;
END

GO

Ответ 8

Другая процедура для клонирования ТОЛЬКО внешних ключей (обратите внимание, что ссылочная таблица уже должна существовать).

CREATE PROCEDURE [dbo].[spCloneFKStructure] 
    @SourceDatabase nvarchar(max),
    @SourceSchema nvarchar(max),
    @SourceTable nvarchar(max),
    @DestinationDatabase nvarchar(max),
    @DestinationSchema nvarchar(max),
    @DestinationTable nvarchar(max)
AS
BEGIN
    declare @sql nvarchar(max)

    SET NOCOUNT ON;

    BEGIN TRANSACTION

    set @sql = '

    --create foreign keys
    DECLARE @ConstraintName nvarchar(max),@ColName nvarchar(max),@RefTable nvarchar(max),@RefColName nvarchar(max)
    DECLARE fkcursor CURSOR FOR
    select a.name,c.name,object_name(b.referenced_object_id,db_id(''' + @SourceDatabase + ''')),d.name 
    from [' + @SourceDatabase + '].sys.foreign_keys a
        join [' + @SourceDatabase + '].sys.foreign_key_columns b on a.object_id=b.constraint_object_id
        join [' + @SourceDatabase + '].sys.columns c on b.parent_column_id = c.column_id and a.parent_object_id=c.object_id
        join [' + @SourceDatabase + '].sys.columns d on b.referenced_column_id = d.column_id and a.referenced_object_id = d.object_id
    where   
        object_name(a.parent_object_id,db_id(''' + @SourceDatabase + ''')) = ''' + @SourceTable + ''' order by c.name
    OPEN fkcursor;
    FETCH NEXT FROM fkcursor INTO @ConstraintName, @ColName, @RefTable, @RefColName;
    WHILE @@FETCH_STATUS = 0
       BEGIN
            exec(''ALTER TABLE [' + @DestinationDatabase + '].[' + @DestinationSchema + '].[' + @DestinationTable + '] ADD CONSTRAINT ['' + @ConstraintName + ''] FOREIGN KEY ('' + @ColName + '') REFERENCES '' + @RefTable + ''('' + @RefColName + '')'')
            FETCH NEXT FROM fkcursor INTO @ConstraintName, @ColName, @RefTable, @RefColName;
       END;
    CLOSE fkcursor;
    DEALLOCATE fkcursor;'

    exec(@sql)

    COMMIT TRANSACTION
END