Sql-server – Run Query Against All DB’s on Server

sql servert-sql

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

Best Answer

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
Related Question