Copy table to another with index constrait

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bharathi Raja8y ago
1. CREATE PROCEDURE [dbo].[spCloneTableStructure]
2. @SourceSchema nvarchar(255),
3. @SourceTable nvarchar(255),
4. @DestinationSchema nvarchar(255),
5. @DestinationTable nvarchar(255),
6. @RecreateIfExists bit = 0
7. AS
8. BEGIN
9. /*
10. Clones an existing table to another table (without data)
11. Optionally drops and re-creates target table
12. Copies:
13. * Structure
14. * Primary key
15. * Indexes (including ASC/DESC, included columns, filters)
16. * Constraints (and unique constraints)
18. DOES NOT copy:
19. * Triggers
20. * File groups
21. * Probably a lot of other things
23. Note: Assumes that you name (unique) constraints with the table name in it (in order to not duplicate constraint names)
24. */
25. SET NOCOUNT ON;
27. BEGIN TRANSACTION
29. --drop the table
30. if EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @DestinationSchema AND TABLE_NAME = @DestinationTable)
31. BEGIN
32. if @RecreateIfExists = 1
33. BEGIN
34. exec('DROP TABLE [' + @DestinationSchema + '].[' + @DestinationTable + ']')
35. END
36. ELSE
37. RETURN
38. END
40. --create the table
41. exec('SELECT TOP (0) * INTO [' + @DestinationSchema + '].[' + @DestinationTable + '] FROM [' + @SourceSchema + '].[' + @SourceTable + ']')
43. DECLARE @PKSchema nvarchar(255), @PKName nvarchar(255)
44. 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'
46. --create primary key
47. IF NOT @PKSchema IS NULL AND NOT @PKName IS NULL
48. BEGIN
49. DECLARE @PKColumns nvarchar(MAX)
50. SET @PKColumns = ''
52. SELECT @PKColumns = @PKColumns + '[' + COLUMN_NAME + '],'
53. FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
54. where TABLE_NAME = @SourceTable and TABLE_SCHEMA = @SourceSchema AND CONSTRAINT_SCHEMA = @PKSchema AND CONSTRAINT_NAME= @PKName
55. ORDER BY ORDINAL_POSITION
57. SET @PKColumns = LEFT(@PKColumns, LEN(@PKColumns) - 1)
59. exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] ADD CONSTRAINT [PK_' + @DestinationTable + '] PRIMARY KEY CLUSTERED (' + @PKColumns + ')');
60. END
62. --create other indexes
63. DECLARE @IndexId int, @IndexName nvarchar(255), @IsUnique bit, @IsUniqueConstraint bit, @FilterDefinition nvarchar(max)
65. DECLARE indexcursor CURSOR FOR
66. SELECT index_id, name, is_unique, is_unique_constraint, filter_definition FROM sys.indexes WHERE type = 2 and object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']')
67. OPEN indexcursor;
68. FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
69. WHILE @@FETCH_STATUS = 0
70. BEGIN
71. DECLARE @Unique nvarchar(255)
72. SET @Unique = CASE WHEN @IsUnique = 1 THEN ' UNIQUE ' ELSE '' END
74. DECLARE @KeyColumns nvarchar(max), @IncludedColumns nvarchar(max)
75. SET @KeyColumns = ''
76. SET @IncludedColumns = ''
78. select @KeyColumns = @KeyColumns + '[' + c.name + '] ' + CASE WHEN is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END + ',' from sys.index_columns ic
79. inner join sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
80. where index_id = @IndexId and ic.object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']') and key_ordinal > 0
81. order by index_column_id
83. select @IncludedColumns = @IncludedColumns + '[' + c.name + '],' from sys.index_columns ic
84. inner join sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
85. where index_id = @IndexId and ic.object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']') and key_ordinal = 0
86. order by index_column_id
88. IF LEN(@KeyColumns) > 0
89. SET @KeyColumns = LEFT(@KeyColumns, LEN(@KeyColumns) - 1)
91. IF LEN(@IncludedColumns) > 0
92. BEGIN
93. SET @IncludedColumns = ' INCLUDE (' + LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1) + ')'
94. END
96. IF @FilterDefinition IS NULL
97. SET @FilterDefinition = ''
98. ELSE
99. SET @FilterDefinition = 'WHERE ' + @FilterDefinition + ' '
101. if @IsUniqueConstraint = 0
102. exec('CREATE ' + @Unique + ' NONCLUSTERED INDEX [' + @IndexName + '] ON [' + @DestinationSchema + '].[' + @DestinationTable + '] (' + @KeyColumns + ')' + @IncludedColumns + @FilterDefinition)
103. ELSE
104. BEGIN
105. SET @IndexName = REPLACE(@IndexName, @SourceTable, @DestinationTable)
106. exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] ADD CONSTRAINT [' + @IndexName + '] UNIQUE NONCLUSTERED (' + @KeyColumns + ')');
107. END
109. FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
110. END;
111. CLOSE indexcursor;
112. DEALLOCATE indexcursor;
114. --create constraints
115. DECLARE @ConstraintName nvarchar(max), @CheckClause nvarchar(max)
116. DECLARE constraintcursor CURSOR FOR
117. SELECT REPLACE(c.CONSTRAINT_NAME, @SourceTable, @DestinationTable), CHECK_CLAUSE from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE t
118. INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS c ON c.CONSTRAINT_SCHEMA = TABLE_SCHEMA AND c.CONSTRAINT_NAME = t.CONSTRAINT_NAME
119. WHERE TABLE_SCHEMA = @SourceSchema AND TABLE_NAME = @SourceTable
120. OPEN constraintcursor;
121. FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
122. WHILE @@FETCH_STATUS = 0
123. BEGIN
124. exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] WITH CHECK ADD CONSTRAINT [' + @ConstraintName + '] CHECK ' + @CheckClause)
125. exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] CHECK CONSTRAINT [' + @ConstraintName + ']')
126. FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
127. END;
128. CLOSE constraintcursor;
129. DEALLOCATE constraintcursor;
131. COMMIT TRANSACTION
132. END