As far as I know, there is no such a native capability in SQL Server. You may try
SELECT * INTO <your_switch_table> FROM <your_original_table>
But this will only copy the list of columns, not the indexes that are needed for partition switch. What is more, the table will be created in default filegroup, where you would prefer the partitioned filegroup.
So I wrote a stored procedure that creates a copy of the table structure, indexes (clustered an non-clustered), and allows some options for the storage filegroup.
/*
This stored procedure generates a SQL script that creates a copy of SQL
table and indexes. Some options allow to fine-tune the generated script,
like include indexes or not, change the filegroup of table, etc.
*/
CREATE PROCEDURE SCRIPT_CREATE_TABLE
@OriginalSchemaName SYSNAME,
@OriginalTableName SYSNAME,
@TargetSchemaName SYSNAME,
@TargetTableName SYSNAME,
@OutputQuery VARCHAR(MAX) OUTPUT,
@FilegroupToUse SYSNAME = NULL,
@ScriptIndexes BIT = 1
AS
BEGIN
/*
References
http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=125819
http://ranjithk.com/2010/01/31/how-to-determine-the-file-group-of-a-table-or-a-index/
http://stackoverflow.com/questions/1121522/generate-create-scripts-for-a-list-of-indexes
*/
BEGIN TRY
/* ******************************** */
/* FIND THE OBJECT_ID CORRESPONDING */
/* TO THE ORIGINAL TABLE */
/* ******************************** */
SET @OutputQuery = ''
DECLARE @Msg VARCHAR(MAX)
DECLARE @TableId INT = NULL
SELECT @TableId = t.object_id
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON c.object_id = t.object_id
WHERE s.name = @OriginalSchemaName
AND t.name = @OriginalTableName
IF @TableId IS NULL
BEGIN
SET @Msg = 'Cannot find table [' + @OriginalSchemaName + '].[' + @OriginalTableName + ']'
RAISERROR(@Msg, 16, 1)
END
/* ************************************* */
/* FIND THE FILEGROUP / PARTITION SCHEME */
/* TO BE USED IN TARGET TABLE */
/* ************************************* */
DECLARE @TargetFilegroup VARCHAR(MAX) = NULL
IF @FilegroupToUse IS NULL OR @FilegroupToUse = ''
/* No filegroup specified, find the original one */
BEGIN
DECLARE @OriginalFilegroup VARCHAR(MAX) = NULL
/* First, try to find a partition scheme used by the table */
SELECT @OriginalFilegroup = '[' + ps.name + ']([' + c.name + '])'
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i ON i.object_id = t.object_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
INNER JOIN sys.index_columns ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id
INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.[type] IN (0, 1) /* Index of type HEAP or CLUSTERED */
AND s.name = @OriginalSchemaName
AND t.name = @OriginalTableName
/* If no partition shceme found, try to find a regular filegroup */
IF @OriginalFilegroup IS NULL
BEGIN
SELECT @OriginalFilegroup = '[' + d.name + ']'
FROM sys.filegroups d
INNER JOIN sys.indexes i ON i.data_space_id = d.data_space_id
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE i.index_id < 2
AND t.name = @OriginalTableName
AND t.schema_id = schema_id(@OriginalSchemaName)
END
/* No regular filegroup neither partition shceme found, there is a problem */
IF @OriginalFilegroup IS NULL
BEGIN
SET @Msg = 'Could not find filegroup neither partition scheme for table [' + @OriginalSchemaName + '].[' + @OriginalTableName + ']'
RAISERROR(@Msg, 16, 1)
END
SET @TargetFilegroup = @OriginalFilegroup
END
ELSE
/* A filegroup has been specified, just use this one */
BEGIN
SET @TargetFilegroup = @FilegroupToUse
END
/* ******************************* */
/* GENERATE CREATE TABLE STATEMENT */
/* ******************************* */
DECLARE @SqlQuery_CreateTable VARCHAR(MAX)
SELECT @SqlQuery_CreateTable = 'CREATE TABLE [' + @TargetSchemaName + '].[' + @TargetTableName + ']
(
' + o.list +
') ON ' + @TargetFilegroup + '
WITH ( DATA_COMPRESSION = ' + CompressionMode + ')
' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE [' + @TargetSchemaName + '].[' + @TargetTableName + '] ADD CONSTRAINT ' + tc.Constraint_Name + ' PRIMARY KEY (' + LEFT(j.List, Len(j.List)-1) + ')' END
FROM sysobjects so
INNER JOIN
(
SELECT p.object_id, MIN(data_compression_desc) AS CompressionMode
FROM sys.partitions p
WHERE p.object_id = @TableId
AND p.index_id < 2
GROUP BY p.object_id
) p ON p.object_id = so.id
CROSS APPLY
(
SELECT ' [' + column_name + '] ' + data_type +
CASE data_type
WHEN 'sql_variant' THEN ''
WHEN 'text' THEN ''
WHEN 'decimal' THEN '(' + cast(numeric_precision_radix AS VARCHAR) + ', ' + CAST(numeric_scale AS VARCHAR) + ')'
ELSE COALESCE('('+ CASE WHEN character_maximum_length = -1 THEN 'MAX' ELSE CAST(character_maximum_length AS VARCHAR) END +')', '')
END + ' ' +
CASE
WHEN EXISTS
(
SELECT id FROM syscolumns
WHERE object_name(id) = so.name
AND name = column_name
AND columnproperty(id,name, 'IsIdentity') = 1
)
THEN 'IDENTITY(' + CAST(IDENT_SEED(so.name) AS VARCHAR) + ', ' + CAST(IDENT_INCR(so.name) AS VARCHAR) + ') '
ELSE ''
END +
(CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END ) + 'NULL' +
CASE WHEN information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN ' DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ',' + CHAR(10)
FROM information_schema.columns
WHERE table_name = so.name
ORDER BY ordinal_position
FOR XML PATH('')
) o (list)
LEFT JOIN information_schema.table_constraints tc
ON tc.Table_name = so.Name AND tc.Constraint_Type = 'PRIMARY KEY'
CROSS APPLY
(
SELECT '[' + Column_Name + '], '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY ORDINAL_POSITION
FOR XML PATH('')
) j (list)
WHERE so.xtype = 'U'
AND so.name NOT IN ('dtproperties')
AND so.id = @TableId
SET @OutputQuery = @OutputQuery + @SqlQuery_CreateTable
/* ******************************** */
/* GENERATE CREATE INDEX STATEMENTS */
/* ******************************** */
IF @ScriptIndexes = 1
BEGIN
DECLARE @SqlQuery_CreateIndex VARCHAR(MAX)
;
WITH IndexCTE AS
(
SELECT DISTINCT
i.index_id,
i.name,
i.object_id,
p.[data_compression_desc] COLLATE SQL_Latin1_General_CP1_CI_AS AS CompressionMode,
ifg.IndexFilegroup,
i.is_unique
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id = i.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN
(
SELECT p.object_id, p.index_id, MIN(p.[data_compression_desc]) AS [data_compression_desc]
FROM sys.partitions p
GROUP BY p.object_id, p.index_id
) p ON p.index_id = i.index_id AND p.object_id = t.object_id
INNER JOIN /* Find the filegroup or partition scheme on which the index relies */
(
SELECT i.index_id, i.name, '[' + ps.name + ']([' + c.name + '])' AS IndexFilegroup
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i ON i.object_id = t.object_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
INNER JOIN sys.index_columns ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id
INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE 1 = 1
AND s.name = @OriginalSchemaName
AND t.name = @OriginalTableName
UNION ALL
SELECT i.index_id, i.name, '[' + d.name + ']' AS IndexFilegroup
FROM sys.filegroups d
INNER JOIN sys.indexes i ON i.data_space_id = d.data_space_id
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE 1 = 1
AND t.name = @OriginalTableName
AND t.schema_id = schema_id(@OriginalSchemaName)
) ifg ON ifg.index_id = p.index_id
WHERE EXISTS
(
SELECT *
FROM sys.columns c
WHERE c.column_id = ic.column_id
AND c.object_id = ic.object_id
)
AND t.name = @OriginalTableName
AND s.name = @OriginalSchemaName
),
IndexCTE2 AS
(
SELECT
IndexCTE.name AS IndexName,
CASE IndexCTE.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END AS IndexUnicity,
CASE IndexCTE.index_id WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END AS IndexType,
(
SELECT CHAR(10) + ' ' + c.name + CASE ic.is_descending_key WHEN 0 THEN ' ASC' ELSE ' DESC' END + ','
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
AND ic.key_ordinal > 0
ORDER BY key_ordinal
FOR XML PATH('')
) ixcols,
ISNULL(
(
SELECT DISTINCT CHAR(10) + ' ' + 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,
IndexCTE.index_id,
IndexCTE.CompressionMode,
IndexCTE.IndexFilegroup
FROM indexCTE
),
CreateSingleIndexQuery AS
(
SELECT
'CREATE ' + IndexUnicity + IndexType + ' INDEX [' + IndexName + '] ON [' + @TargetSchemaName + '].[' + @TargetTableName + ']' + CHAR(10) +
'(' + SUBSTRING(ixcols, 1, LEN(ixcols) - 1) + CHAR(10) +
CASE LEN(includedcols)
WHEN 0 THEN ')'
ELSE ')' + CHAR(10) + 'INCLUDE' + CHAR(10) + '(' + SUBSTRING(includedcols, 1, LEN(includedcols) - 1) + CHAR(10) + ')'
END + CHAR(10) +
'WITH ( DATA_COMPRESSION = ' + CompressionMode + ' )' + CHAR(10) +
'ON ' + IndexFilegroup AS Query,
index_id
FROM IndexCTE2
)
SELECT @SqlQuery_CreateIndex = o.list
FROM CreateSingleIndexQuery
CROSS APPLY
(
SELECT CHAR(10) + Query + CHAR(10)
FROM CreateSingleIndexQuery
ORDER BY index_id
FOR XML PATH('')
) o (list)
SET @OutputQuery = @OutputQuery + @SqlQuery_CreateIndex
END
END TRY
BEGIN CATCH
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
END
Easy to use:
DECLARE @SqlQuery VARCHAR(4000) = ''
EXEC SPR_SCRIPT_CREATE_TABLE 'dbo', 'existing_table', 'dbo', 'switch_table', @SqlQuery OUTPUT EXEC(@SqlQuery)
No comments:
Post a Comment