可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
Basically the exact same question as in this question: How to copy indexes from one table to another in SQL Server, BUT, how do I do it programmatically in T-SQL, given a source table name and destination table name?
I.e. without knowing what table up front.
I can copy the basic structure
SELECT TOP (0) * INTO [BackupTable] FROM [OriginalTable]
But that doesn't copy indexes, constraints, triggers etc
I ideally would like a stored proc that looks something like:
spCloneTableStructure @ExistingTableName, @NewTableName
That copies the columns, primary keys and indexes
Anything like that exist? (note that I'm on SQL Server 2008 R2)
回答1:
This is what I came up with. It works for me and copies all the stuff I care about.
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
回答2:
I have made some alterations to Gareth's work, which BTW works and I think is great. I wanted to include cloning the Triggers and copy the tables content as well. In essence "copy" as much of the table as best I can in one shot. I have included the whole piece of code. Remember this is not completely original, and I do not claim credit for any of Gareth's hard work. I hope this is useful 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
回答3:
To give you an idea of what's involved (and why you should use SMO for this as mentioned in comments):
- Get list of indexes from
sys.indexes based on the object_ID of the source table
- This includes other info like
UNIQUE, PK, IGNORE_DUP_KEY, FILL_FACTOR, PADDED, DISABLED, ROW LOCKS, PAGE LOCKS which will all need to be coded separately in your dynamic SQL
- Get list of all key fields (and their order, and if they are ASC or DESC) for each index from
sys.index_columns. This will involve JOINing sys.columns for the names since they are all column IDs
- Get list of included fields from
sys.index_columns
- Get a list of filters for each index from
sys.indexes
Now translate all the above data into valid SQL scripts to execute on the target table.
For efficiency you should also script out the clustered index first and run it, since it will take longer to build a cluster if there are already non-clustered indexes in place.
回答4:
Thanks Gareth. Good work! I needed a TSQL script which works across databases. Basicly what F_Face did. I've adjusted F_Face modification because there is still a hard coded database name and some statements are relying on that.
Anyway, I agree to all the comments that this isn't the way a deep structure copy of a table should be done. Use SMO via PowerShell via xp_cmdshell via TSQL to do it in a propper way would be my suggestion. In that way SMO is taking care that everything is copied and that next SQL Server generation works as well with it.
This mentioned, here the code:
--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
回答5:
Thanks Gareth. That works. I have altered to work across 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 = '
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
回答6:
This is quick and dirty mod, based on this question: Generate CREATE scripts for a list of indexes but should get you most of the way there. You could do what you are already doing to clone the structure, and you a version of this proc to do they indexes and then modify as 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
回答7:
Here is my version. The destination table and schema are the same like the source with something added at the end in my case _STG. You can add something at the end using @NameAdd NVARCHAR(128) variable. I did it this way to avoid sql injection. In my version no outside provided text is executed.
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
回答8:
Another procedure to clone ONLY foreign keys (Note that referenced table must already exists, obviously).
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
