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

概述        上文中介绍了SQL Server中各种约束以及使用sql查询各种约束的方法，本文基于上文实现了表结构 （含约束）复制的存储过程。该存储过程在SQL Server 2008 及 SQL Server 2014上测试可行。sql如下 /************************************************************ * 表结构复制（含约束）存

        上文中介绍了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;gocreate 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 reportserverexec dbo.sp_copy_table     @srctablename = '[dbo].Schedule',@dsttablename = '[dbo].Schedule_bak'exec sp_help 'dbo.Schedule_bak'

      可以发现，已经成功复制了 reportserver数据库的 Schedule表

      注意事项：

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

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

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

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

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

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

总结

以上是内存溢出为你收集整理的sqlserver表结构（含约束）复制存储过程全部内容，希望文章能够帮你解决sqlserver表结构（含约束）复制存储过程所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。