CODEWITHSUNDEEP
sqlsql-serverdatabase
mitch2k
mitch2k

Reputation: 526

Execute SQL query on all DB's

I have a SQL Server with 17 databases that have the same table structure. I want to do a count query for each of that table in each DB

DECLARE @command varchar(1000)

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''ReportServer'', ''ReportserverTempDB'') 

BEGIN 
    USE ? 
    EXEC(''SELECT COUNT(MODEL) AS rptdevicecount 
           FROM dbo.devices WHERE MODEL IS NOT NULL'') END'

EXEC sp_MSforeachdb @command

Now I have 2 questions:

  1. how can I combine the count results? So I want to know how many devices there are in total, not per DB.

  2. When running this query, I get an error

    Msg 911, Level 16, State 1, Line 2
    Database 'CH' does not exist. Make sure that the name is entered correctly.

I should fix this by adding IF EXISTS. But where do I have to put that?

Thank you!

Upvotes: 0

Views: 507

Answers (1)

LeeG
LeeG

Reputation: 728

Something like this:

CREATE TABLE #T1 (DBName varchar(20), Qty int)
EXECUTE master.sys.sp_msforeachdb 'USE [?]; 
IF DB_NAME() NOT IN (''master'',''model'',''msdb'',''tempdb'',''ReportServer'',''ReportServerTempDB'')
BEGIN
    INSERT INTO #T1
    SELECT db_Name(), COUNT(*) FROM sys.tables
END
'
SELECT SUM(qty) FROM #T1
DROP TABLE #T1

Upvotes: 1

Related Questions