You are here:Home » WHILE » Run query against all the databases without MSFOREACHDB and WHILE/CURSOR

Run query against all the databases without MSFOREACHDB and WHILE/CURSOR

I have learned one more thing today, for small queries when we need to run it for all the databases of instance, then we are mostly using MSFOREACHDB and WHILE loop or CURSOR to get the data. But with COALESCE I can I do with very small code here, which collect count of the objects for each database.
DECLARE @ObjectSQL NVARCHAR(MAX)
SET @ObjectSQL = ''
SELECT @ObjectSQL = COALESCE(@ObjectSQL,'') + CHAR(13) + CHAR(10) 
    + 'SELECT ' + QUOTENAME([Name],'''') + ' as DbName, 
    COUNT(1) AS CntObject 
    FROM ' + QUOTENAME([Name],'') + '.DBO.SYSOBJECTS;'  
FROM SYS.DATABASES 
PRINT (@ObjectSQL)
EXECUTE (@ObjectSQL)
Hope you like it.