0

I am trying to run this against all the DB's on my server and not just on "TEST_1". Is there a simple way to do this? Ideally the select would also return the DB name.

use TEST_1
go
declare @DataSetID int

set @DataSetID = 1;

declare @Linkdate datetime

set @Linkdate = '8/31/2015';

select distinct I.InterfaceName
    ,case 
        when B.brokerCode is null
            then 'Not Populated'
        else B.BrokerCode
        end
    ,case 
        when B.brokerDescription is null
            then 'Not Populated'
        else B.BrokerCode
        end
from transactions T
inner join interfaceoriginations I on I.OriginationID = t.OriginationID
left join brokers B on B.BrokerID = t.BrokerID
where linkdate = @linkdate
order by 1
    ,2
1
  • 2
    Check this answer if you are uisng SQL Server. The concept is same.
    – Kin Shah
    Commented Sep 1, 2015 at 16:48

1 Answer 1

3

OK, replacing the previous answer with this one. USE OF ms_foreach stored procedures is not recommended. This is a little more code but it's more reliable. You can limit the databases you search through, it checks to make sure the transaction table exists, and still provides the database name in the results. Note - this will provide a separate result set for each DB. If this is not desired, create a temp table and dump the results into the temp table by uncommenting the table creation block for #Results, the INSERT just before "EXEC sp_executesql @runMe" and the final SELECT.

    DECLARE 
          @DataSetID INT
        , @Linkdate VARCHAR(19)
        , @runMe NVARCHAR(4000)
        , @dbCnt INT
        , @dbName VARCHAR(500)
        , @tableName VARCHAR(500)
        , @objName VARCHAR(500)

    --initialize variables
    SET @DataSetID = 1;
    SET @Linkdate = '8/31/2015';
    SET @tableName = 'transactions'; --assuming if this is present the other are as well
    SET @dbName='';

    /*
    --CREATE A TABLE TO CAPTURE ALL RESULT
    IF OBJECT_ID('tempdb..#Results','U') IS NOT NULL
        DROP TABLE #Results;

    CREATE TABLE #Results
    (
          DBName            VARCHAR(500)
        , InterfaceName     VARCHAR(255)
        , BrokerCode        VARCHAR(255)
        , BrokerDescription VARCHAR(1000)
    )
    */


    IF OBJECT_ID('tempdb..#DBList','U') IS NOT NULL
        DROP TABLE #DBList;

    SELECT name 
    INTO #DBList
    FROM sys.databases;

    SELECT @dbCnt=COUNT(1)
    FROM #DBList;

    WHILE @dbCnt > 0
    BEGIN
        SELECT @dbName=MIN(name)
        FROM #DBList
        WHERE name > @dbName

        SET @objName = @dbName+'..'+@tableName

        IF OBJECT_ID(@objName) IS NOT NULL
        BEGIN
            SET @runMe = N'
            select distinct '''+@dbName+''' AS DBName, I.InterfaceName
                ,case 
                    when B.brokerCode is null
                        then ''Not Populated''
                    else B.BrokerCode
                    end
                ,case 
                    when B.brokerDescription is null
                        then ''Not Populated''
                    else B.BrokerCode
                    end
            from ['+@dbname+']..transactions T
            inner join ['+@dbname+']..interfaceoriginations I on I.OriginationID = t.OriginationID
            left join ['+@dbname+']..brokers B on B.BrokerID = t.BrokerID
            where linkdate = '''+@linkdate+'''
            order by 1
                ,2;'

            --INSERT #Results
            EXEC sp_executesql @runMe
        END

        SET @dbCnt = @dbCnt-1
    END

    --SELECT * FROM #Results
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.