清空数据库中表记录

/*--------------------------------------------
--清空数据库中表记录，允许排除某些表
--yangys  2006/03/29
*/

--首先独立删除外健约束
declare @strsql varchar(8000)
declare curTemp cursor local for
select s='alter table ['+object_name(parent_obj)+'] drop constraint ['+name+']'
from sysobjects where xtype ='F'

open curTemp
fetch next from curTemp into @strsql
while @@fetch_status=0
begin
 exec(@strsql)
 fetch next from curTemp into @strsql
end
close curTemp
deallocate curTemp
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpSource]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmpSource]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpSpec]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmpSpec]
GO

--创建临时表
--所有表名称信息
create table tmpSource
(
 tableName varchar(100)
)

--要删除表信息
create table tmpSpec
(
 tableName varchar(100)
)
go

--插入要删除表信息
insert tmpSpec
select 'InfoFormType'
union all
select 'FormTypeID'
union all
select 'InfoSettingModule'
union all
select 'InfoSettingModule'
union all
select 'permit_info'
union all
select 'ManufacturePlanType'
union all
select 'PrintLableName'
union all
select 'ManufacturePlanGrade'
union all
select 'QualityMethodType'
union all
select 'StkInType'
union all
select 'StkOutType'
union all
select 'Stk'
union all
select 'InfoOperator'
union all
select 'InfoCurrency'

go

--插入表名信息
insert tmpSource
select [name] from dbo.sysobjects where xtype='U' and [name]<>'dtproperties' And [Name] <>'MSreplication_options'

--删除不需要清空的表
delete from tmpSource
where exists(select 1 from tmpSpec where tableName=tmpSource.tableName)

go

--执行删除
declare @str varchar(8000)
set @str=''

select @str=@str+[tableName] +'   , ' from tmpSource

declare @strCommand varchar(2000)
set @strCommand='delete  '
set @str=left(@str,len(@str)-1)
set @str= replace(@str,',',@strCommand)

set @str=@strCommand+' '+@str

exec(@str)

go