set nocount ondeclare @objectCode varchar(100)declare @tableName varchar(100)--此游标清楚UDO主表的数据declare udoCursor cursor for select Code,TableName from oudo where Code != 'FSSBOCUFMS'open udoCursorfetch next from udoCursor into @objectCode,@tableNamewhile(@@fetch_status = 0) begin exec('truncate table [@'+@tableName+']') --清楚表中的数据 update onnm set AutoKey = 1 where ObjectCode = @objectCode --修改数据中的主键 update nnm1 set NextNumber = 1 where ObjectCode = @objectCode fetch next from udoCursor into @objectCode,@tableName endclose udoCursordeallocate udoCurSor--删除UDO子表中的所有数据declare udoChildCursor cursor for select Code,TableName from udo1open udoChildCursorfetch next from udoChildCursor into @objectCode,@tableNamewhile(@@fetch_status = 0) begin exec('truncate table [@'+@tableName+']') --清楚Udo子表的数据 fetch next from udoChildCursor into @objectCode,@tableName endclose udoChildCursordeallocate udoChildCursor--查看表中的哪些表中有数据 ,删除不是UDO的表的数据declare @tableid intdeclare @table varchar(100)declare @tableCol table(name varchar(100)) --删除哪些表insert into @tableCol values('AITW')insert into @tableCol values('ITM1')insert into @tableCol values('AITM')insert into @tableCol values('OIGN')insert into @tableCol values('ADOC')insert into @tableCol values('IGN1')insert into @tableCol values('IGE1')insert into @tableCol values('PDN6')insert into @tableCol values('OITW')insert into @tableCol values('OCRD')insert into @tableCol values('OITM')insert into @tableCol values('@FSCXFSPKINFO')insert into @tableCol values('@REGESTERSMOKEHELP')insert into @tableCol values('@FSSBOTRANSLOG')insert into @tableCol values('ACRD')insert into @tableCol values('OINM')insert into @tableCol values('PDN6')insert into @tableCol values('OITW')insert into @tableCol values('PDN1')insert into @tableCol values('LeaderUser')insert into @tableCol values('OIGE')insert into @tableCol values('PDN12')insert into @tableCol values('AFPR')insert into @tableCol values('AIT1')insert into @tableCol values('@FSCXFITEMBATINFOHELP')insert into @tableCol values('OPDN')insert into @tableCol values('LeaderUser')insert into @tableCol values('CurrentGroup')insert into @tableCol values('CurrentLotto')insert into @tableCol values('@FSCXFFACTORYLOG')
declare userCursor cursor forselect name,id from sysobjects where xtype = 'U'open userCursorfetch next from userCursor into @table,@tableidwhile(@@fetch_status = 0) begin declare @param nvarchar(100) declare @sql nvarchar(1000) declare @num int set @sql = N' select @num1 = count(1) from ['+@table+']' set @param = N'@num1 int output' exec sp_executesql @sql,@param,@num1 = @num output if(@num > 0) begin if(charindex('_Tombstone',@table) > 0) --Pda表的数据 begin exec('truncate table ['+@table+']') end else if(charindex('Table',@table) > 0) --报表的数据 begin exec('truncate table ['+@table+']') end else if(exists(select 1 from @tableCol where name = @table)) --SBO系统表及无对象表数据 begin exec('truncate table ['+@table+']') end end fetch next from userCursor into @table,@tableid endclose userCursordeallocate userCursor
--处理系统UDOdeclare @objtype table(objtype varchar(100))insert into @objtype values('4')insert into @objtype values('64')insert into @objtype values('59')insert into @objtype values('20')insert into @objtype values('60')insert into @objtype values('2')update onnm set AutoKey = 1 where ObjectCode in (select objtype from @objtype)update nnm1 set NextNumber = 1 where ObjectCode in (select objtype from @objtype)--处理系统用户update ousr set U_WhsAuth = null ,U_ItgAuth = null ,U_ShpAuth = null,U_SuperAuth = null print '清空数据库成功'
