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