Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

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)
Read More..   [0 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Paresh Prajapati
6 · 23% · 7464
12
 
6
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

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
    95 · 2% · 586

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
Run query against all the databases without MSFOREACHDB and WHILE/CURSOR , 5.0 out of 5 based on 12 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]