处理千方百剂年结存后商品信息无法删除、往来单位信息无法删除的脚本

iwillwei

/* 脚本用途：处理年结存后商品信息期初没有库存但无法删除、往来单位信息无应收应付、期初商品供应商、门店关联往来但无法删除

注意：此脚本执行后只存在基本信息和期初库存、期初应收应付。
*/

declare b cursor for
select name from sysobjects where id in ( select id from syscolumns where name='ptypeid')
and xtype='u' and name<>'inigoodsstocks'
open b
declare @bm varchar(50)
declare @Sql varchar(8000)
fetch next from b into @bm


while(@@fetch_status<>-1)
begin


set @Sql = 'delete from '+ @bm


exec(@Sql)


print(@sql)


fetch next from b into @bm
end
close b
deallocate b
go
declare b cursor for
select name from sysobjects
where id in ( select id from syscolumns where name='providerid')
and xtype='u' and name<>'inigoodsstocks' AND NAME <>'POSINFO'
and name<>'BTYPEATTORNEY' and name<>'GSP_N_AdmitLicence' and name<>'GSP_N_BusinessLicence'
open b
declare @bm varchar(50)
declare @Sql varchar(8000)
fetch next from b into @bm


while(@@fetch_status<>-1)
begin


set @Sql = 'delete from '+ @bm


exec(@Sql)




fetch next from b into @bm
end
close b
deallocate b
GO
declare b cursor for
select name from sysobjects
where id in ( select id from syscolumns where name='BTYPEID')
and xtype='u' and name<>'inigoodsstocks' AND NAME <>'POSINFO'
and name<>'BTYPEATTORNEY' and name<>'GSP_N_AdmitLicence' and name<>'GSP_N_BusinessLicence'
open b
declare @bm varchar(50)
declare @Sql varchar(8000)
fetch next from b into @bm


while(@@fetch_status<>-1)
begin


set @Sql = 'delete from '+ @bm


exec(@Sql)




fetch next from b into @bm
end
close b
deallocate b
GO
UpDate ptype set SonNum =
isnull((Select Count(Typeid) from ptype where a.typeid = Parid and deleted = 0 and status = 0 ),0) from ptype a
UpDate ptype set SonCount =
isnull((Select Cast(Max(Right(typeid,5)) as int) from ptype where a.typeid = Parid ),0) from ptype a
GO
UpDate btype set SonNum =
isnull((Select Count(Typeid) from btype where a.typeid = Parid and deleted = 0 and typeid not like '9%'),0) from btype a
UpDate btype set SonCount =
isnull((Select Cast(Max(Right(typeid,5)) as int) from btype where a.typeid = Parid and typeid not like '9%'/*and deleted = 0 */),0) from btype a