通过在SQL Server中运行的SQL脚本复制表数据(包括任何默认值或绑定数据)的存储过程

Question

我希望能够复制一个表和它的数据，也仍然有任何默认值或绑定(因为它被标记在SQL Server管理控制台)复制的约束。

下面的脚本是演示这个想法的测试脚本。我认为最后一行需要替换为对自定义存储过程的调用?

注意:源表(aSourceTbl)模式会随着时间变化而变化。

--TEST SETUP 
--Delete the prev tables so test script can be replayed
IF OBJECT_ID('aSourceTbl', 'U') IS NOT NULL 
        DROP TABLE aSourceTbl;
IF OBJECT_ID('aSourceCopyTbl', 'U') IS NOT NULL 
        DROP TABLE aSourceCopyTbl;
--Simple table to demonstrate table copying does not carry over the table constraits
CREATE TABLE [dbo].[aSourceTbl](
    [aValue] [int] NOT NULL,
    [DELETED] [int] NOT NULL
) ON [PRIMARY]
--Add some dummy data
INSERT INTO aSourceTbl (aValue, DELETED) VALUES (1,2);
INSERT INTO aSourceTbl (aValue, DELETED) VALUES (3,4);
--Add constraints of default values of 0 in this case 
ALTER TABLE [dbo].[aSourceTbl] ADD  CONSTRAINT [DF_aSourceTbl_aValue]  DEFAULT ((0)) FOR [aValue]
ALTER TABLE [dbo].[aSourceTbl] ADD  CONSTRAINT [DF_aSourceTbl_DELETED]  DEFAULT ((0)) FOR [DELETED]
--Actual Required SQL script from here down 
--The line below works nicely but does not copy the 2 constraints from the lines above into the new table. 
--TODO QUESTION: Replace line below with the same functionaility + the constraints are also copied into new table
Select * INTO aSourceCopyTbl FROM aSourceTbl

你能帮我建议一个合适的存储过程，可以取代上面的SQL片段的最后一行吗?任何帮助都非常感谢:)

引用:

• 类似的SO问题侧重于PK约束。在这种情况下，我只对默认值约束感兴趣。

Answer 1

您可以在最后一行之后执行此代码，它将复制默认约束到新表(将变量替换为您的表名)。

declare @table_name sysname, @new_table sysname, @cmd varchar(max)
select @table_name = 'SOURCE_TABLE', @cmd = '', @new_table = 'TEST_TABLE'

select @cmd = @cmd+'ALTER TABLE '+@new_table+' ADD CONSTRAINT [DF_' +@new_table+'_'+a.name+'] 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(@table_name)  
print @cmd
exec (@cmd)