asked on

I have a select statement for all Database's, as some database's have no results from query how do i hide them from showing?

I am able to do a query for all databases using the ' exec sp_msforeachdb @command1= ' command.

So what I want is to limit the results from the query so that any db which isn't showing any results for that period does not show up. I am planning to do run it on a weekly basis.

Is there also a way of making it in a report format?

Jared_S

Is there an if exists evaluation you can do against [?] in your query string to eliminate the blanks?

If not, you could insert the results into a temp table and select non-nulls with something like...

declare @command1 as varchar (250)

create table #mytable (myField integer, ...)

set @command1 = 'USE [?]; insert into #mytable select ...'

exec sp_msforeachdb @command1

select * from #mytable where myField is not null

Open in new window

Kaiz

ASKER

I do have an if exists evaluation which is used to filter out the master db etc.

exec sp_msforeachdb @command1= '
if exists(select ''?'' where ''?'' not in (
       ''master'',
       ''tempdb'') )
begin
       select
             ''?'' dbname,
             w.WorkflowName,
             stalled,
             stallreason

       from
             [?].dbo.work
       inner join
             [?].dbo.flow f on f.flowid = w.workid
       where
             stalled = 0

End'
, @replacechar='?'
Go

But I'm not sure how i would do that for the tables that are showing blank fields for that week.

ASKER CERTIFIED SOLUTION

Jared_S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kaiz

ASKER

Cheers mate thats what I was looking for.