Программно копировать индексы из одной таблицы в другую в SQL Server
В основном то же самое, что и в этом вопросе: Как скопировать индексы из одной таблицы в другую в SQL Server, НО, как это сделать программно в T -SQL, учитывая имя исходной таблицы и имя таблицы назначения?
т.е. не зная, какая таблица впереди.
Я могу скопировать основную структуру
SELECT TOP (0) * INTO [BackupTable] FROM [OriginalTable]
Но это не копирует индексы, ограничения, триггеры и т.д.
В идеале я хотел бы, чтобы хранимый процесс выглядел примерно так:
spCloneTableStructure @ExistingTableName, @NewTableName
Копировать столбцы, первичные ключи и индексы
Что-нибудь подобное существует? (обратите внимание, что я на SQL Server 2008 R2)
Ответы
Ответ 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
