(1) 清查資料表及 View 物件 ,
select a.* from (
select object_name(id) tableName from sys.syscolumns where name like '%acctype%'
union all
select object_name(id) from sys.syscolumns where name like '%actype%'
) a inner join (
select object_name(id) tableName from sys.syscolumns where name like '%invtx%' ) b on a.tableName=b.tableName
(2) 清查 sp , function 及 trigger 物件
select a.* from
(select distinct b.name from sys.syscomments a,sys.sysobjects b where text like '%acctype%' and a.id=b.id
union all
select distinct b.name from sys.syscomments a,sys.sysobjects b where text like '%actype%' and a.id=b.id ) a inner join
(select distinct b.name from sys.syscomments a,sys.sysobjects b where text like '%invtx%' and a.id=b.id ) b on a.name=b.name
---------------------
一:
select mfgWorkID,'打包扣' workName,'A40'WorkGroupNo,'1'WageRate,var2 WorkNo,var3 WorkTime,var3 WorkTime2,2.3*var3/60 WorkCost,
2.3*var3/60 WorkCost2,2.3*var3/60 WorkCost5
Into #D2
--select *
from #D aa
left join mfgBasic b on aa.var1=b.SourceNo
left join mfgWork c on c.MFGBasicID=b.MFGBasicID and c.workNO =aa.var2
where
exists (select * from MFGWork a1
left join mfgBasic b1 on a1.mfgbasicID=b1.mfgbasicID
where a1.workNo=aa.var2 and b1.SourceNo=aa.var1 and a1.WorkTime!=aa.var3)--and and a1.WorkTime=aa.var3
and isnull(mfgWorkID,'')=''
update a set a.WorkTime=b.WorkTime,a.WorkTime2=b.WorkTime2,a.WorkCost=b.WorkCost,a.WorkCost2=b.WorkCost2,a.WorkCost5=b.WorkCost5
--select *
from MFGWork a INner Join #D2 b on a.mfgWorkID=b.mfgWorkID
where a.mfgWorkID in (select b.mfgWorkID from #D2)
二:
select var1,var2,var3 from aa Group By var1,var2,var3
select var1,var2,var3 from aa Group By var1,var2,var3 Having Count(*)>1
select var1,var2,var3 Into #D from aa Group By var1,var2,var3