?
Solved

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?

Posted on 2012-09-12
4
Medium Priority
?
387 Views
Last Modified: 2012-09-20
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.

How do I not show these, where there is no record for that query
Is there also a way of making it in a report format?
0
Comment
Question by:Kaiz
  • 2
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Jared_S
ID: 38390879
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

0
 

Author Comment

by:Kaiz
ID: 38405407
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.
0
 
LVL 12

Accepted Solution

by:
Jared_S earned 1680 total points
ID: 38405959
Can you nest another exist statement? Maybe something like this..

exec sp_msforeachdb @command1= '
   if exists(select ''?'' where  ''?'' not in (
        ''master'', 
        ''tempdb'') ) 
   begin
   
   if exists(select 
             ''?'' dbname, 
        from 
             [?].dbo.work
        inner join 
             [?].dbo.flow f on f.flowid = w.workid
        where 
             stalled = 0)
             
        Begin
        select 
             ''?'' dbname, 
             w.WorkflowName, 
             stalled,
             stallreason

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

        End
    End'
    , @replacechar='?' 
    Go 

Open in new window

0
 

Author Comment

by:Kaiz
ID: 38417071
Cheers mate thats what I was looking for.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question