sql查table，VIEW，sp， function 及 trigger 物件

 

(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