use master; -- A bit naughty, since we shouldn't change the master database
GO
/*
Creates an empty replica of a table - i.e. a new table with the same columns and indexes, but with a different name.
This is useful for creating switch-in / switch-out tables.
Features currently supported:
Heaps, Clustered Indexes and non-clustered indexes, Partitioned Indexes.
Included Columns.
Column Defaults.
Check constraints.
Sparse columns.
Features currently not supported:
Default Bindings.
XML indexes.
Column Store indexes.
*/
CREATE PROC [dbo].[sp_CloneTable]
@table VARCHAR(100) , -- The name of the table to be cloned.
@new_schema VARCHAR(100) = null, -- The schema in which to create the new table. Leave null to create new table in same schema.
@new_table VARCHAR(100) = null, -- The name of the target table. Leave null to keep original table name.
@enable_index_type int = 2 -- Controls which indexes to enable on the target. 2 = clustered & non-clustered, 1 = clustered only, 0 = none.
AS
SET NOCOUNT ON;
DECLARE @object_id int = OBJECT_ID(@table)
IF @object_id IS NULL RAISERROR ('Source table does not exists',16,0);
IF @new_schema IS NULL and @new_table IS NULL RAISERROR ('The new table must be given a different name and/or schema',16,0);
SET @new_schema = ISNULL(@new_schema, OBJECT_SCHEMA_NAME(@object_id));
SET @new_table = ISNULL(@new_table , OBJECT_NAME(@object_id));
DECLARE @SQL VARCHAR(MAX) = '';
SET @SQL = @SQL + 'IF EXISTS(SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(''' + QUOTENAME(@new_schema) + '.' + QUOTENAME(@new_table) + ''')) DROP TABLE ' + @new_schema + '.' + @new_table + ';' + CHAR(13) + CHAR(13);
WITH index_columns AS (
SELECT ic.index_id,
included = ic.is_included_column,
ordinal = ic.key_ordinal,
column_name = c.name,
partition_ordinal = ic.partition_ordinal,
comma_column_name = ', ' + c.name,
comma_column_key = ', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
FROM sys.index_columns ic
JOIN sys.columns c on c.column_id = ic.column_id AND c.object_id = ic.object_id
WHERE ic.object_id = @object_id
), indexes AS (
SELECT i.is_primary_key,
i.type,
i.is_unique_constraint,
i.name,
create_stmt = 'CREATE ' + CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + CASE WHEN i.type = 1 THEN 'CLUSTERED ' ELSE '' END + 'INDEX ',
columns = (SELECT [data()] = column_name FROM index_columns ic WHERE ic.index_id = i.index_id and included = 0 ORDER BY ordinal FOR XML PATH('')),
key_columns = STUFF((SELECT [data()] = comma_column_key FROM index_columns ic WHERE ic.index_id = i.index_id and included = 0 ORDER BY ordinal FOR XML PATH('')) ,1,2,''),
included = STUFF((SELECT [data()] = comma_column_name FROM index_columns ic WHERE ic.index_id = i.index_id and included = 1 FOR XML PATH('')) ,1,2,''),
pk_options = ISNULL('WITH(DATA_COMPRESSION=' + data_compression_desc + ')', ''),
options = ' WITH ( PAD_INDEX = ' + CASE is_padded WHEN 1 THEN 'ON' ELSE 'OFF' END
+ ', ALLOW_ROW_LOCKS = ' + CASE allow_row_locks WHEN 1 THEN 'ON' ELSE 'OFF' END
+ ', ALLOW_PAGE_LOCKS = ' + CASE allow_page_locks WHEN 1 THEN 'ON' ELSE 'OFF' END
+ ISNULL(', FILLFACTOR = ' + NULLIF(CAST(fill_factor AS VARCHAR(10)), '0') , '')
+ ISNULL(', IGNORE_DUP_KEY = ' + CASE ignore_dup_key WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' END, '')
+ ISNULL(', DATA_COMPRESSION = ' + data_compression_desc, '')
+ ISNULL(', STATISTICS_NORECOMPUTE = ' + CASE (SELECT TOP 1 no_recompute FROM sys.stats stats WHERE i.object_id = stats.object_id and i.index_id = stats.stats_id)
WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' END, '')
+ ')',
filegroup = COALESCE(
(SELECT TOP 1 ' ON ' + ps.name + '('
+ STUFF((SELECT [data()]=comma_column_key FROM index_columns ic WHERE ic.index_id = i.index_id and partition_ordinal > 0 ORDER BY partition_ordinal FOR XML PATH('')) ,1,2,'')
+ ')'
FROM sys.partition_schemes ps
JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
WHERE ps.data_space_id = i.data_space_id
),
(SELECT TOP 1 ' ON ' + QUOTENAME(name) FROM sys.filegroups WHERE data_space_id = i.data_space_id)
,'')
FROM sys.indexes i
OUTER APPLY (SELECT data_compression_desc FROM sys.partitions part WHERE i.object_id = part.object_id and i.index_id = part.index_id
GROUP BY data_compression_desc HAVING COUNT(*) = 1) d
WHERE i.object_id = @object_id
)
SELECT @SQL = @SQL
+ 'CREATE TABLE ' + QUOTENAME(@new_schema) + '.' + QUOTENAME(@new_table) + CHAR(13)
+ '(' + CHAR(13)
+ STUFF( (SELECT [data()] = '^*^, ' + QUOTENAME(c.name) + ' '
+ CASE WHEN c.is_computed = 1 THEN ' AS ' + cc.definition + CASE WHEN cc.is_persisted = 1 THEN ' PERSISTED' ELSE '' END
ELSE
t.name
+ CASE WHEN t.user_type_id in (165,167,173,175,231,239) -- varbinary,varbinary,varchar,binary,char,nvarchar,nchar
THEN '(' + REPLACE(CONVERT(VARCHAR(20), c.max_length),'-1','max') + ')'
WHEN t.user_type_id in (40,42) -- date/datetime2
THEN '(' + CONVERT(VARCHAR(20), c.scale) + ')'
WHEN t.user_type_id in (106,108) -- decimal/numeric
THEN '(' + CONVERT(VARCHAR(20), c.precision) + ',' + CONVERT(VARCHAR(20), c.scale) + ')'
ELSE '' END
+ CASE WHEN c.is_sparse = 1 THEN ' SPARSE' ELSE '' END
+ CASE WHEN c.is_identity = 1 THEN ' IDENTITY(1,1)' ELSE '' END -- currently, no support identity seeds
+ CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
+ ISNULL( ' DEFAULT ' + dc.definition,'')
END
FROM sys.columns c
JOIN sys.types t ON t.user_type_id = c.user_type_id
LEFT JOIN sys.computed_columns cc on cc.object_id = c.object_id and cc.column_id = c.column_id
LEFT JOIN sys.default_constraints dc on dc.parent_object_id = c.object_id and dc.parent_column_id = c.column_id
WHERE c.object_id = @object_id
FOR XML PATH(''))
,1,4,'')
+ ISNULL((SELECT ',' + CHAR(13) + ' CONSTRAINT ' + QUOTENAME('PK_' + REPLACE(REPLACE(@new_table, '[',''),']',''))
+ ' PRIMARY KEY ' + CASE WHEN i.type <> 1 THEN 'NON' ELSE '' END
+ 'CLUSTERED (' + i.key_columns + ') ' + pk_options + filegroup
FROM indexes i where i.is_primary_key = 1)
,'')
+ ISNULL((SELECT [data()] = '^*^ CONSTRAINT ' + QUOTENAME(i.name) + ' UNIQUE (' + i.key_columns + ')' + filegroup FROM indexes i where i.is_primary_key = 0 and i.is_unique_constraint = 1 FOR XML PATH('')),'')
+ ISNULL((SELECT [data()] = ',^*^ CONSTRAINT ' + QUOTENAME(cc.name) + ' CHECK ' + cc.definition
FROM sys.check_constraints cc WHERE cc.parent_object_id = @object_id FOR XML PATH('')) ,'')
+ CHAR(13)
+ ') '
+ ISNULL((SELECT TOP 1 filegroup FROM indexes i WHERE i.type in (0,1)),'') + ';' + CHAR(13)
+ ISNULL(
(SELECT [data()] = '^*^' + i.create_stmt
+ QUOTENAME('IX ' + @new_schema + '.' + @new_table + ' ' + i.columns)
+ ' ON ' + QUOTENAME(@new_schema) + '.' + QUOTENAME(@new_table)
+ ' (' + i.key_columns + ' )'
+ ISNULL(' INCLUDE (' + i.included + ')','') + options + filegroup + ';'
FROM indexes i
WHERE i.is_primary_key = 0 and i.is_unique_constraint = 0
FOR XML PATH(''))
,'')
+ CHAR(13)
+ ISNULL(
(SELECT [data()] =
'^*^ALTER INDEX ' + CASE WHEN i.is_primary_key = 1 THEN QUOTENAME('PK_' + REPLACE(REPLACE(@new_table, '[',''),']',''))
ELSE QUOTENAME('IX ' + @new_schema + '.' + @new_table + ' ' + i.columns) END
+ ' ON ' + QUOTENAME(@new_schema) + '.' + QUOTENAME(@new_table)
+ ' DISABLE;'
FROM indexes i
WHERE i.type > @enable_index_type
FOR XML PATH(''))
,'');
SET @SQL = REPLACE(REPLACE(REPLACE(@SQL, '^*^', CHAR(13)) ,'<','<'),'>','>')
EXEC (@SQL);
GO
EXEC sp_ms_marksystemobject 'sp_CloneTable'