SQL Query Multiple Databases (Microsoft SQL Server)

By | September 21, 2017

DECLARE @Database VARCHAR(255)
DECLARE @stmt nvarchar(1000)
DECLARE @params NVARCHAR(500)
DECLARE @resTable TABLE (dbName VARCHAR(255), resCountFound INT)
DECLARE @resFound INT

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state_desc=’ONLINE’ and name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’distribution’,’ReportServerTempDB’,’ReportServer$SQL2008R2′,’ReportServer$SQL2008R2TempDB’)
ORDER BY 1

OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

SET @stmt=N’select @resFoundOUT=Count(*) from [‘+@Database+’].dbo.MyTable WHERE MyValue > 200′
SET @params = N’@resFoundOUT INT OUTPUT’

EXECUTE sp_executesql @stmt, @params, @resFoundOUT=@resFound OUTPUT

INSERT INTO @resTable VALUES (@Database, @resFound)

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

SELECT * FROM @resTable ORDER by resCountFound desc, dbname