如何优化查询 - 使用视图提取计数信息

Question

如何同时构建以下查询以通过多个数据库运行。

要计算数据库中活动用户的总数，我目前必须一次运行查询一个数据库。

我可以动态运行某种foreach函数吗？

with data as
(select p.CoNum CoNum, p.LastName, p.Name,co.name Company, liGender.Description as Gender, liEth.Description as EthnicGroup,Isnull(Limed.Description,'Not Specified') FinancialAid,
  vwPTU.[UserP Type] as UserPType
from UsersP p
  left outer join ListItem liGender on liGender.Id = p.Gender
  left outer join ListItem liEth on liEth.Id = p.EthnicGroup
  left outer join ListItem limed on limed.Id = p.FinancialAidId
  inner join UserPOrgNodeAssociation pona on P.Id = pona.UserPId
  inner join CoAssociation coda on pona.CoNodeDescAssociationId = coda.Id
  inner join Company co on coda.companyid=co.id
  left outer join [dbo].[vw_XUserUniqueView] vwPTU on p.Id = vwPTU.Id
    and vwPTU.[ValidToDate] > '2015/12/31 23:59 PM'
where vwPTU.[User Type] is not null
)
select Company, count(distinct CoNum) as Total
from data
group by company

Answer 1

尝试使用sp_msforeachdb过程。

exec sp_msforeachdb  'Your Query here'