Programmatically copy indexes from one table to another in SQL Server

Question

Basically the same thing as in this question: How to copy indexes from one table to another in SQL Server , BUT, how to do it programmatically in T -SQL, given the name of the source table and the name of the destination table?

those. not knowing which table is ahead.

I can copy the main structure

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

But it does not copy indexes, restrictions, triggers, etc.

Ideally, I would like the stored process to look something like this:

 spCloneTableStructure @ExistingTableName, @NewTableName 

Copy Columns, Primary Keys, and Indexes

Does anything like that exist? (note that I'm on SQL Server 2008 R2)

Answer 1

Here is what I came up with. It works for me and copies everything I need.

 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 

Answer 2

I made some changes to the work of Gareth, which runs BTW, and I think it's great. I wanted to enable cloning of triggers and copy the contents of the tables. In essence, “copy” most of the table as far as I can with one shot. I have included the entire code snippet. Remember that this is not entirely original, and I do not claim to be a merit in any hard work of Gareth. Hope this is helpful for anyone interested.

 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+'.'+@SourceTable SET @FullDestinationTable = @DestinationSchema+'.'+@DestinationTable DECLARE @MySQL varchar(MAX) DECLARE @MyValues varchar(MAX) SET @MyColumn = 2 SET @MySQL = 'INSERT INTO '+@FullDestinationTable+' (' 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 = @MySQL+@MyValues+') ' SET @MySQL = @MySQL+' SELECT '+@MyValues+' FROM '+@FullSourceTable --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 ['+@DestinationSchema+'].['+@trigger_name+'] ON ['+@DestinationSchema+'].['+@DestinationTable+']' --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 ['+@DestinationSchema+'].['+@trigger_name+'] ON ['+@DestinationSchema+'].['+@DestinationTable+']' --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 ['+@DestinationSchema+'].['+@trigger_name+'] ON ['+@DestinationSchema+'].['+@DestinationTable+']' --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 

Answer 3

To give you an idea of ​​what is related (and why you should use SMO for this, as noted in the comments):

• Get a list of indexes from sys.indexes based on the object_ID of the source table
  • This includes other information such as UNIQUE , PK , IGNORE_DUP_KEY , FILL_FACTOR , PADDED , DISABLED , ROW LOCKS , PAGE LOCKS , which all must be encoded separately in dynamic SQL
• Get a list of all key fields (and their order, as well as ASC or DESC) for each index from sys.index_columns . This will include JOIN ing sys.columns for names, as they are all ID s columns
• Get list of included fields from sys.index_columns
• Get a list of filters for each index from sys.indexes

Now translate all of the above data into valid SQL scripts for execution in the target table.

For efficiency, you should also script the clustered index and run it, since it will take longer to create the cluster if there are already non-clustered indexes.

Answer 4

This is a quick and dirty mod based on this question: Generate CREATE scripts for a list of indexes , but should get most of the way from you. You can do what you are already doing to clone the structure, and you use the version of this proc for their indices, and then change if necessary:

  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 

Answer 5

Thanks Gareth. It works. I changed the work with databases:

 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 

Answer 6

Thanks Gareth. Good job! I need a TSQL script that works through databases. Basically what F_Face did. I adjusted the F_Face modification because there is still a solid database name, and some statements rely on this.

In any case, I agree with all the comments that this is not the way a deep structural copy of the table should be made. Using SMO through PowerShell through xp_cmdshell through TSQL to do this in propper mode would be my suggestion. In this way, SMO makes sure that everything is copied and that the next generation of SQL Server works with it.

The code is mentioned here:

 --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.'+@spName+' 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 ['+@SourceDatabase+'].sys.indexes WHERE type = 2 AND object_id = object_id(''['+@SourceDatabase+'].[' + @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 ['+@SourceDatabase+'].sys.index_columns ic INNER JOIN ['+@SourceDatabase+'].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(''['+@SourceDatabase+'].[' + @SourceSchema + '].[' + @SourceTable + ']'') AND key_ordinal > 0 ORDER BY index_column_id SELECT @IncludedColumns = @IncludedColumns + ''['' + c.name + ''],'' from ['+@SourceDatabase+'].sys.index_columns ic INNER JOIN ['+@SourceDatabase+'].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(''['+@SourceDatabase+'].[' + @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 

Answer 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='+@DataCompression+');'; ELSE SET @ExecuteCMD = 'CREATE ' + @Unique + @Clustered + ' INDEX [' + @IndexName + '] ON [' + @DestinationSchema + '].[' + @DestinationTable + '] (' + @KeyColumns + ')' + @IncludedColumns + @FilterDefinition + ' WITH (SORT_IN_TEMPDB=ON,DATA_COMPRESSION='+@DataCompression+');'; 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 

Answer 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