Skip to content

Instantly share code, notes, and snippets.

@linhx

linhx/sqlserver-clonetable.sql

Last active October 21, 2021 02:53
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save linhx/26a380c933a582d62b4201f145e0a110 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save linhx/26a380c933a582d62b4201f145e0a110 to your computer and use it in GitHub Desktop.
Download ZIP
SQL Server clone table: clone columns, contraints, default values
Raw
sqlserver-clonetable.sql
CREATE PROCEDURE [dbo].[spCloneTableStructure]
@SourceSchema nvarchar(255),
@SourceTable nvarchar(255),
@DestinationSchema nvarchar(255),
@DestinationTable nvarchar(255),
@RecreateIfExists bit = 0
AS
BEGIN
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
DECLARE @Message nvarchar(100) = '[' + @DestinationSchema + '].[' + @DestinationTable + '] already exists!';
THROW 50000, @Message, 1;
END
END
--create the table
exec('SELECT TOP (0) * INTO [' + @DestinationSchema + '].[' + @DestinationTable + '] FROM [' + @SourceSchema + '].[' + @SourceTable + ']')
DECLARE @Today char(17) = FORMAT(GETDATE(), 'yyyyMMddhhmmssfff')
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)
DECLARE @NewPKName varchar(255) = SUBSTRING('PK__' + @Today + '_' + @DestinationSchema + '_' + @DestinationTable, 0, 128);
exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] ADD CONSTRAINT [' + @NewPKName + '] PRIMARY KEY CLUSTERED (' + @PKColumns + ')');
END
--create other indexes
DECLARE @RowNum int, @IndexId int, @IndexType int, @IndexName sysname, @IsUnique bit, @IsUniqueConstraint bit, @FilterDefinition nvarchar(max)
SET @RowNum = 0;
DECLARE indexcursor CURSOR FOR
SELECT index_id, name, type, is_unique, is_unique_constraint, filter_definition FROM sys.indexes
WHERE is_primary_key = 0
and object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']')
ORDER BY index_id;
OPEN indexcursor;
FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IndexType, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RowNum = @RowNum + 1;
IF @FilterDefinition IS NULL
SET @FilterDefinition = ''
ELSE
SET @FilterDefinition = 'WHERE ' + @FilterDefinition + ' '
IF @IndexType = 2 --nonclustered
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 @IsUniqueConstraint = 0
BEGIN
SET @IndexName = SUBSTRING('IDX__' + CAST(@RowNum AS VARCHAR(10)) + '_' + @Today + '_' + @DestinationSchema + '_' + @DestinationTable, 0, 128);
exec('CREATE ' + @Unique + ' NONCLUSTERED INDEX [' + @IndexName + '] ON [' + @DestinationSchema + '].[' + @DestinationTable + '] (' + @KeyColumns + ')' + @IncludedColumns + @FilterDefinition)
END
ELSE
BEGIN
SET @IndexName = SUBSTRING('CS__' + CAST(@RowNum AS VARCHAR(10)) + '_' + @Today + '_' + @DestinationSchema + '_' + @DestinationTable, 0, 128);
exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] ADD CONSTRAINT [' + @IndexName + '] UNIQUE NONCLUSTERED (' + @KeyColumns + ')');
END
END
ELSE IF @IndexType = 5 -- Clustered columnstore
BEGIN
SET @IndexName = SUBSTRING('IDX__' + CAST(@RowNum AS VARCHAR(10)) + '_' + @Today + '_' + @DestinationSchema + '_' + @DestinationTable, 0, 128);
exec('CREATE CLUSTERED COLUMNSTORE INDEX [' + @IndexName + '] ON [' + @DestinationSchema + '].[' + @DestinationTable + '];');
END
ELSE IF @IndexType = 6 -- Nonclustered columnstore
BEGIN
DECLARE @OnColumns nvarchar(max);
SET @OnColumns = ''
SELECT @OnColumns = @OnColumns + '[' + 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 + ']')
ORDER BY index_column_id
IF LEN(@OnColumns) > 0
SET @OnColumns = LEFT(@OnColumns, LEN(@OnColumns) - 1)
SET @IndexName = SUBSTRING('IDX__' + CAST(@RowNum AS VARCHAR(10)) + '_' + @Today + '_' + @DestinationSchema + '_' + @DestinationTable, 0, 128);
exec('CREATE NONCLUSTERED COLUMNSTORE INDEX [' + @IndexName + '] ON [' + @DestinationSchema + '].[' + @DestinationTable + '] (' + @OnColumns + ') ' + @FilterDefinition);
END
ELSE THROW 50000, 'Not implement this type of index', 1;
FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IndexType, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
END;
CLOSE indexcursor;
DEALLOCATE indexcursor;
--create constraints
DECLARE @ConstraintName nvarchar(max), @CheckClause nvarchar(max)
SET @RowNum = 0;
DECLARE constraintcursor CURSOR FOR
SELECT 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 @CheckClause;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RowNum = @RowNum + 1;
SET @ConstraintName = SUBSTRING('CCS__' + CAST(@RowNum AS VARCHAR(10)) + '_' + @Today + '_' + @DestinationSchema + '_' + @DestinationTable, 0, 128);
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 @CheckClause;
END;
CLOSE constraintcursor;
DEALLOCATE constraintcursor;
-- add default value
DECLARE @DefaultValQuery NVARCHAR(max)
SET @DefaultValQuery = ''
SELECT @DefaultValQuery = @DefaultValQuery+'ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] ADD CONSTRAINT [DF__' + @DestinationSchema + '_' + @DestinationTable + '_' +a.name + '_' + @Today + '] DEFAULT '+b.definition+' FOR['+a.name+'];'
FROM sys.columns a
JOIN sys.default_constraints b ON a.object_id = b.parent_object_id AND a.column_id = b.parent_column_id
WHERE a.object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']')
EXEC (@DefaultValQuery)
COMMIT TRANSACTION
END;
@linhx
Copy link
Copy Markdown
Author

linhx commented Sep 28, 2021

fork from https://stackoverflow.com/a/7584511/12686871 and https://stackoverflow.com/a/7603899/12686871
add:

  • avoid duplicate constrant name

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment