sap 清空数据库

 set nocount on
declare @objectCode varchar(100)
declare @tableName varchar(100)
--此游标清楚UDO主表的数据
declare udoCursor cursor for
select Code,TableName from oudo where Code != 'FSSBOCUFMS'
open udoCursor
fetch next from udoCursor into @objectCode,@tableName
while(@@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
   end
close udoCursor
deallocate udoCurSor
--删除UDO子表中的所有数据
declare udoChildCursor cursor for
select Code,TableName from udo1
open udoChildCursor
fetch next from udoChildCursor into @objectCode,@tableName
while(@@fetch_status = 0)
   begin
       exec('truncate table [@'+@tableName+']') --清楚Udo子表的数据
       fetch next from udoChildCursor into @objectCode,@tableName
   end
close udoChildCursor
deallocate udoChildCursor
--查看表中的哪些表中有数据 ,删除不是UDO的表的数据
declare @tableid int
declare @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 for
select name,id from sysobjects  where xtype = 'U'
open userCursor
fetch next from userCursor into @table,@tableid
while(@@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
    end
close userCursor
deallocate userCursor

--处理系统UDO
declare @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 '清空数据库成功'