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

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

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

May 27 2011 7:15AM by Paresh Prajapati

I have learned one more thing today, for small queries when we need to run it for all the databses of instance then we are mostly using MSFOREACHDB and WHILE loop or CURSOR to get the data. But with COALESCE i can it do with very small code here, which collect count of the objects for each databases.

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)

1 Comments

You can return a single result-set using UNION ALL (instead of one result-set per 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 UNION ALL'  
FROM SYS.DATABASES 

SET @ObjectSQL = LEFT(@ObjectSQL, LEN(@ObjectSQL) - 9)

PRINT (@ObjectSQL)

EXECUTE (@ObjectSQL)

commented on May 7 2012 5:09AM

Marc Jellinek

97 · 2% · 556

Your Comment

Sign Up or Login to post a comment.

"Run query against all the databases without MSFOREACHDB and WHILE/CURSOR" rated 5 out of 5 by 12 readers +Paresh Prajapati