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脚本的实现方法是：新建临时表用来存储原表结构及数据信息，然后删除原表，再将临时表更名为原表名称，最后为新表添加特殊应用，如触发器。