March 26, 2007 at 2:23 pm
The query below works fine, but I want to exclude all the databases that do not have a certain table. How can do that using the query below. Thanks for help.
Insert into EmailStats
EXEC sp_MSForEachDB 'IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
select distinct ''[?]'',
a.*, b.Opened, b.HardBounced, b.SoftBounced, b.MalFormed, c.Unsubscribed from (
select distinct
act.iniUser,
ii.[name] as IssueName,
c.[name] as CampaignName,
p.[name] as ProjectName,
a.[name] as ActivityName,
a.internalName as InterNalName,
act.[name] as ActionName,
act.[id] as ActionID,
count(cc.[id]) as TotalSent
from [?].dbo.individual1 i
inner join [?].dbo.communication cc
on i.ind_id = cc.indFK
inner join [?].dbo.action act
on cc.actionFK = act.[id]
inner join [?].dbo.activity a
on act.activityFK = a.[id]
inner join [?].dbo.project p
on a.projectFK = p.[id]
inner join [?].dbo.campaign c
on p.campaignFK = c.[id]
inner join [?].dbo.issue ii
on c.issueFK = ii.[id]
where
convert(varchar(10),cc.iniDate,112) >= ''20070101''
group by
act.iniUser,
ii.[name],
c.[name],
p.[name],
a.[name],
a.internalName,
act.[name],
act.[id]
) a
left JOIN (
select M.ActionFK,
case when mbsStatus = 9 then Cnt else 0 end as Opened,
case when mbsStatus = 5 then Cnt else 0 end as HardBounced,
case when mbsStatus = 6 then Cnt else 0 end as SoftBounced,
case when mbsStatus = 7 then Cnt else 0 end as MalFormed
from
(select c.actionFK,
mbsStatusTypeFK as mbsStatus,
count(c.[id]) as cnt
from [?].dbo.communication c
where convert(varchar(10),c.iniDate,112) >= ''20070101''
group by c.actionFK,
mbsStatusTypeFK )M
) b
on a.ACtionID = b.ActionFK
left join
(select action_fk, count(ind_fk) as Unsubscribed
from [?].dbo.unsubscribe
group by action_fk ) c
on a.ActionID = c.Action_fk
'
-- select * From emailStats
March 26, 2007 at 3:30 pm
You could change your starting if statement to check table existence using something like:
IF EXISTS(SELECT * FROM information_schema.tables WHERE TABLE_NAME IN(''...''))
BEGIN
...
SQL guy and Houston Magician
March 26, 2007 at 3:37 pm
Thanks Robert... it did work.
EXEC sp_MSForEachDB 'if exists (select * from [?].dbo.sysobjects where [name] = ''communication'' and xtype = ''u'')
begin
select distinct ''[?]'',
a.*, b.Opened, b.HardBounced, b.SoftBounced, b.MalFormed, c.Unsubscribed from (
.........................................................
March 26, 2007 at 5:17 pm
Try to avoid querying system tables, especially from outside of database.
You need to have quite relaxed security model to perform it, and one day one pissed off employee could make a lot of damage. And it's gonna be you to blame for it.
You may achieve the same result using system functions:
For check if object exists:
IF Object_ID('communication') IS NOT NULL
For check if it's a table:
IF OBJECTPROPERTY ( Object_ID('communication') , 'IsTable' ) = 1
_____________
Code for TallyGenerator
March 27, 2007 at 10:03 pm
Thanks a lot Sergi!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply