SQL Server初始化表：删除数据及主键复位

在做项目的过程中，经常会遇到一种情况，开发和测试过程中，会往业务表中添加很多测试数据，等到项目重新发布时需要删除数据并且让主键重新从1开始，若是表比较少还好办，多起来就不好玩了。昨天就碰到这么个事儿，总共50多张表，弄了个将近半个小时，弄完之后想想有没有更好的办法呢，就去网上搜了一下思路，结合实际情况写了如下的存储过程。

需要说明的几点：

1、数据库比较特殊，系统表和业务表取名不同，系统表如实取名，业务表统一为table...，且在一个为S_ENTITY(实体表)中有注明，所以我只需要从S_ENTITY表中获取以table打开的表进行初始化即可。

 

2、测试数据库用的SQL Server 2005。

 

3、存储过程思路：先将业务表的结构复制到临时表，再删除业务表，再将临时表中的结构复制到业务表，最后删除临时表。

 

4、缺点是：因为复制的临时表，所以原来的业务表中的特殊应用如触发器，索引等都会没有，也就是此操作之后新的业务表什么触发器啊、索引啊等特殊应该都会木有，只有一个基本结构。

 

以下为实现过程：

1、实体表创建：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[S_ENTITY](
	[S_ID] [int] IDENTITY(1,1) NOT NULL,
	[ENTITYNAME] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
	[ENTITYDESCRIPT] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL
 CONSTRAINT [PK_S_ENTITY] PRIMARY KEY CLUSTERED 
(
	[S_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

 

 2、insert测试数据：

insert into S_ENTITY(ENTITYNAME,ENTITYDESCRIPT) values('table1','测试表1')
insert into S_ENTITY(ENTITYNAME,ENTITYDESCRIPT) values('table2','测试表2')
insert into S_ENTITY(ENTITYNAME,ENTITYDESCRIPT) values('table3','测试表3')

 

 3、创建存储过程：

-- =============================================
-- Author:	<wjl>
-- Create date: <2015-5-13>
-- Description:	<初始化业务表中的数据，包括删除业务表数据并复位主键>
-- =============================================
CREATE PROCEDURE [dbo].[initData] 
	
AS
BEGIN
	SET NOCOUNT ON;

	--1、从实体表中获取业务数据表：以table开头
	declare @count int;--记录符合条件的表的数量
	select @count = count(S_ID) from S_ENTITY where ENTITYNAME like '%table%' 
	print @count;
	if(@count>0)--说明有符合条件的table
	begin
		declare @c int;
		declare @tableName varchar(100);--表名
		declare @tempTableName varchar(100);--临时表名称
		declare @PKColumn varchar(50);--主键所在列名称
		declare @sql varchar(200);--拼接的SQL语句
		set @c = 1;
		while(@c<=@count)--依次循环各个表
			begin
				--获取表名称 
				select @tableName = ENTITYNAME from (
				        select ENTITYNAME,row_number() over(order by S_ID) as fn from S_ENTITY 
						where ENTITYNAME like '%table%') 
				as a where fn=@c;
				print @tableName;
				
				--2、将表结构复制到临时表中
				set @tempTableName = '##TEMP_TAB_'+@tableName;--使用全局临时表，使用本地临时表会报“找不到临时表”的错误。
				print @tempTableName;
				--判断临时表是否存在
				if exists(select * from tempdb..sysobjects where id=object_id('tempdb..'+@tempTableName+''))
				begin
					print '临时表存在';
					set @sql = 'drop table '+@tempTableName+';'--若存在则删除
					exec(@sql);
				end
				--判断物理表是否存在
				if object_id(N''+@tableName+'',N'U') is not null
				print '物理表存在'
				else
				begin
				print @tableName;
				print '物理表不存在'
				set @c=@c+1;--循环数加1，否则会死循环
				continue;--不存在跳出当前循环，进行下一个循环
				end

				set @sql = 'SELECT * INTO '+@tempTableName+' FROM '+@tableName+ ' where 1=2';--只复制结构，不复制数据
				print @sql;
				exec(@sql);
				
				--3、获取主键所在列：
				--因为复制结构到临时表时除了主键信息之外,其重要的自增信息也会复制过去,所以只需要设置自增信息即可
				--(前提条件是当前表上没有其他的特殊应用,若有其他特殊应用如触发器/索引什么的,都会丢失)				
				SELECT @PKColumn=COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@tableName; 
				print @PKColumn;

				--4、删除原有表
				set @sql = 'drop table '+@tableName+';'
				print @sql;
				exec(@sql);
		
				--5、通过临时表新建原有表
				set @sql = 'SELECT * INTO '+@tableName+' FROM '+@tempTableName+' where 1=2;';--新建结构，没有数据
				print @sql;
				exec(@sql);
			
				--6、为新表添加主键：因为新建的表有临时表的自增属性，所以不需要添加
				set @sql ='alter table '+@tableName+' Add  Constraint  PK_'+@tableName+'  Primary  Key ('+@PKColumn+')'
				print @sql;
				exec(@sql);--执行SQL

				--7、删除临时表
				set @sql = 'drop table '+@tempTableName+';'
				print @sql;
				exec(@sql);
			set @c=@c+1;
			end
	end

END

 

个人观点：

上面这个存储过程用来初始化没有特殊应用的表倒是不错，有特殊应用就不行了。最好的办法还是删除标识列，然后再添加标识列，这样原有特殊应用就都保存下来了。有问题的是：每次添加的列都会在表的最后，若是没有特殊不要求倒是也无所谓，要是有特殊要求标识列必须放在第一列，这个位置问题就是个大难题了，MySql中有before和after用来调节这个，SQL Server图形化界面本身也有插入列的操作，SQL Server脚本的实现方法是：新建临时表用来存储原表结构及数据信息，然后删除原表，再将临时表更名为原表名称，最后为新表添加特殊应用，如触发器。