sqlserver表结构（含约束）复制存储过程

? ? ? ? 上文中介绍了SQL Server中各种约束以及使用sql查询各种约束的方法，本文基于上文实现了表结构

（含约束）复制的存储过程。该存储过程在SQL Server 2008 及 SQL Server 2014上测试可行。sql如下

 /************************************************************
 * 表结构复制（含约束）存储过程
 * Time: 2017/7/23 19:54:38
 ************************************************************/
if object_id(N'sp_copy_table',N'P') is not null
    drop procedure sp_copy_table;
go

create procedure sp_copy_table
	@srcTableName varchar(200),@dstTableName varchar(220)
as
	set nocount on
	
	begin try
		-- 如果源表不存在，则抛出异常
		declare @tabID varchar(30) = object_id(@srcTableName,N'U'); 
		if @tabID is null
		    raiserror('src table not exists',16,1); 
		
		-- 如果目标表已经存在，则抛出异常
		if object_id(@dstTableName,N'U') is not null
		    raiserror('destination table already exists',1);
		
		-- 创建表（不复制数据）
		declare @createSql varchar(max) = '';
		set @createSql = 'SELECT * INTO ' + @dstTableName + ' FROM ' + @srcTableName 
		    + ' WHERE 1 > 1';
		exec (@createSql); 
		
		-- ============== 添加约束 ================
		--
		-- ============= 1. unique constraint / primary constraint =============
		declare @tb1 table
		        (IdxName varchar(255),colName varchar(255),consType tinyint)
		
		declare @tb2 table
		        (IdxName varchar(255),consType tinyint)
		
		-- 查询原表的主键约束、唯一约束(统一约束可能作用与多列上，结果集中作为多列)
		insert into @tb1
		select idx.name as idxName,col.name as colName,(case when idx.is_primary_key = 1 then 1 
                             when idx.is_unique_constraint = 1 then 2 else 0 end) consType
		from   sys.indexes idx
		       join sys.index_columns idxCol
		       on (
		           idx.object_id = idxCol.object_id
		           and idx.index_id = idxCol.index_id
		           and (idx.is_unique_constraint = 1 or idx.is_primary_key = 1)
		       )
		       join sys.columns col
		       on (idx.object_id = col.object_id and idxCol.column_id = col.column_id)
		       where idx.[object_id] = @tabID
		
		-- 按照约束名，将同一约束的多行结果集合并为一行，写入临时表
		insert into @tb2
		select idxName,colsName = stuff(
		           (
		               select ',' + colName
		               from   @tb1
		                      where IdxName = a.idxName
		               and consType = a.consType for xml path('')
		           ),1,''
		       ),a.consType
		from   @tb1 a;
		
		-- @tb1 临时表数据已经没用，删除
		delete 
		from   @tb1; 
		
		-- 循环遍历约束，写入目标表
		declare @checkName     varchar(255),@colName       varchar(255),@consType      varchar(255),@tmp           varchar(max);
		
		while exists(
		          select 1
		          from   @tb2
		      )
		begin
		    select @checkName = IdxName,@colName = colName,@consType = consType
		    from   @tb2;
		    
		    -- 主键约束		
		    if @consType = 1
		    begin
		        set @tmp = 'ALTER TABLE ' + @dstTableName + ' ADD CONSTRAINT ' 
                            + @checkName + '_01' 
		            + ' PRIMARY KEY (' + @colName + ')';
		        exec (@tmp);
		    end-- 	唯一约束
		    else 
		    if @consType = 2
		    begin
		        set @tmp = 'ALTER TABLE ' + @dstTableName + ' ADD CONSTRAINT ' 
                            + @checkName + '_01' 
		            + ' UNIQUE (' + @colName + ')'; 
		        exec (@tmp);
		    end
		    
		    -- 使用完后，删除
		    delete 
		    from   @tb2
		    where  IdxName = @checkName
		           and colName = @colName;
		end
		
		-- ================= 2. 外键约束 ===================
		declare @tb3 table (fkName varchar(255),referTabName varchar(255),referColName varchar(255))
		
		-- 查询源表外键约束，写入临时表
		insert into @tb3
		select fk.name as fkName,SubCol.name as colName,oMain.name as referTabName,MainCol.name as referColName
		from   sys.foreign_keys fk
		       join sys.all_objects oSub
		       on (fk.parent_object_id = oSub.object_id)
		       join sys.all_objects oMain
		       on (fk.referenced_object_id = oMain.object_id)
		       join sys.foreign_key_columns fkCols
		       on (fk.object_id = fkCols.constraint_object_id)
		       join sys.columns SubCol
		       on (oSub.object_id = SubCol.object_id and fkCols.parent_column_id = SubCol.column_id)
		       join sys.columns MainCol
		       on (oMain.object_id = MainCol.object_id
                           and fkCols.referenced_column_id = MainCol.column_id)
		       where oSub.[object_id] = @tabID;
		
		-- 遍历每一个外键约束，写入目标表
		declare @referTabName     varchar(255),@referColName     varchar(255);
		while exists(
		          select 1
		          from   @tb3
		      )
		begin
		    select @checkName = fkName,@referTabName = referTabName,@referColName = referColName
		    from   @tb3;
		    
		    set @tmp = 'ALTER TABLE ' + @dstTableName + ' ADD CONSTRAINT ' 
                        + @checkName + '_01' 
		        + ' FOREIGN KEY (' + @colName + ') REFERENCES ' 
                        + @referTabName + '(' + @referColName + ')';
		    exec (@tmp);
		    
		    delete 
		    from   @tb3
		    where  fkName = @checkName;
		end
		
		-- =============== 3.CHECK约束 ===================
		declare @tb4 table (checkName varchar(255),definition varchar(max));
		
		insert into @tb4
		select chk.name as checkName,chk.definition
		from   sys.check_constraints chk
		       join sys.columns col
		       on (chk.parent_object_id = col.object_id and chk.parent_column_id = col.column_id)
		       where chk.parent_object_id = @tabID
		
		-- 遍历每一个CHECK约束，为目标表添加约束
		declare @definition varchar(max);
		while exists(
		          select 1
		          from   @tb4
		      )
		begin
		    select @checkName = checkName,@definition = [definition]
		    from   @tb4;
		    
		    set @tmp = 'ALTER TABLE ' + @dstTableName + ' ADD CONSTRAINT ' 
                        + @checkName + '_01' 
		        + ' CHECK ' + @definition;
		    exec (@tmp);
		    
		    delete 
		    from   @tb4
		    where  checkName = @checkName;
		end
		
		--  ================ 4. default约束 =====================
		insert into @tb4
		select df.name as checkName,c.name as colName,df.definition
		from   sys.default_constraints df
		       join sys.[columns] as c
		       on df.parent_column_id = c.column_id
		       and df.parent_object_id = c.[object_id]
		       where df.parent_object_id = @tabID;
		
		-- 遍历每一个default 约束
		while exists(
		          select 1
		          from   @tb4
		      )
		begin
		    select @checkName = checkName,@definition = [definition]
		    from   @tb4;
		    
		    set @tmp = 'ALTER TABLE ' + @dstTableName + ' ADD CONSTRAINT ' 
                        + @checkName + '_01' 
		        + ' DEFAULT ' + @definition + ' FOR ' + @colName;
		    print 'default: ' + @tmp;
		    exec (@tmp);
		    
		    delete 
		    from   @tb4
		    where  checkName = @checkName;
		end
	end try
	begin catch
		-- 输出错误信息
		select error_number() as ErrorNumber,error_severity() as ErrorSeverity,error_state() as ErrorState,error_procedure() as ErrorProcedure,error_line() as ErrorLine,error_message() as ErrorMessage
	end catch

? ? ? ? 存储过程定义好之后，就可以调用该存储过程进行表结构复制了。这里使用ReportServer数据库的 Schedule表做测试 ?

use ReportServer
exec dbo.sp_copy_table
     @srcTableName = '[dbo].Schedule',@dstTableName = '[dbo].Schedule_bak'

exec sp_help 'dbo.Schedule_bak'

? ? ? 注意事项：

? ? ? 1. 由于该存储过程中使用了所在数据库的一些系统表，不同的数据库所含的系统表的数据不一致，

该存储过程不支持跨数据库复制表结构。使用时务必在需要进行表复制的数据库新建该存储过程，

再调用存储过程进行表结构复制

? ? ? 2. 上面ReportServer数据库的Schedule表进行表结构复制时，会有如下警告：

警告! 最大键长度为 900 个字节。索引 'IX_Schedule_01' 的最大长度为 1040 个字节。
 对于某些大值组合，插入/更新操作将失败。

? ? ? 这个是由于Schedule表的唯一约束含有两个长度为520的varchar列，导致最大长度超出键的最大长度900导致的

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!