--本服務(wù)器所有賬套應(yīng)用的模塊
DECLARE @Name nvarchar(300)
if exists (select * from tempdb.dbo.sysobjects where name='Temp_ModuleList')
drop table tempdb..Temp_ModuleList
CREATE TABLE tempdb..Temp_ModuleList(ztName [nvarchar](100) NULL,sysid [nvarchar](4) NULL,InfoID [nvarchar](8) NULL,caption [nvarchar](200) NULL) ON [PRIMARY]
DECLARE My_Cursor CURSOR
FOR
select name FROM MASTER.DBO.SYSDATABASES where left(name,7)='UFTData' ORDER BY NAME
OPEN My_Cursor
FETCH NEXT FROM My_Cursor INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('insert into tempdb..Temp_ModuleList select '''+@name+ ''',sysid,InfoID,caption from ' + @Name + '..EAP_AccInformation where OptionType=1 and idEnablePeriod>0 order by infoid')
FETCH NEXT FROM My_Cursor INTO @Name
END
CLOSE My_Cursor
DEALLOCATE My_Cursor
select sysid,InfoID,caption 本服務(wù)器所有賬套應(yīng)用的模塊 from tempdb..Temp_ModuleList group by sysid,InfoID,caption
select right(ztName,6) ztid,ztName,
count( case InfoID when 322 then caption end) 總賬,
count( case InfoID when 323 then caption end) Tufo報表,
count( case InfoID when 319 then caption end) 固定資產(chǎn),
count( case InfoID when 302 then caption end) 購銷管理,
count( case InfoID when 301 then caption end) 庫存核算,
count( case InfoID when 303 then caption end) 往來現(xiàn)金,
count( case InfoID when 304 then caption end) 自制加工
from tempdb..Temp_ModuleList group by right(ztName,6) ,ztName order by right(ztName,6)
drop table tempdb..Temp_ModuleList
