MohamedG's Log

TW9oYW1lZCBFbC1HZWlzaA==

Nested For-each Loops in SQL

Usually, it’s not recommended that you use loops in SQL unless you need to. You should use set-based queries instead. However, if you need to, there are many ways to loop, one of them is using cursors. For example, let’s say that you have multiple DBs and you need to select count(*) from some tables in each one of them. Here’s a solution:


Use master 


IF OBJECT_ID(‘tempdb..#Stat’) IS NOT NULL EXEC(‘DROP TABLE #Stat’)


CREATE TABLE #Stat
(
      DB          VARCHAR(20)
)


 


INSERT #Stat VALUES (‘My1stDB’)
INSERT #Stat VALUES (‘My2ndDB’) 


 


DECLARE @TableName TABLE 
(
      Name  VARCHAR(20)
)


 
INSERT @TableName VALUES (‘Table1’)
INSERT @TableName VALUES (‘Table2’)
INSERT @TableName VALUES (‘Table3’)


 


DECLARE Table_Cur CURSOR FOR SELECT Name FROM @TableName
DECLARE @table VARCHAR(20)


 


OPEN Table_Cur
FETCH NEXT FROM Table_Cur INTO @table


WHILE @@FETCH_STATUS = 0
BEGIN
      — Add column to store table count
      EXEC(‘ALTER TABLE #Stat ADD ‘ +@table + ‘ INT’)
      FETCH NEXT FROM Table_Cur INTO @table
END
CLOSE Table_Cur 


 


DECLARE Stat_Cur CURSOR FOR SELECT DB FROM #Stat
DECLARE @db VARCHAR(20)


 


OPEN Stat_Cur
FETCH NEXT FROM Stat_Cur INTO @db


WHILE @@FETCH_STATUS = 0
BEGIN
      OPEN Table_Cur
      FETCH NEXT FROM Table_Cur INTO @table
      WHILE @@FETCH_STATUS= 0
      BEGIN
            EXEC(‘UPDATE #Stat SET ‘ + @table + ‘ = (SELECT COUNT(*) FROM ‘ + @db + ‘.dbo.’ + @table + ‘) WHERE DB =”’ + @db + ””)
            FETCH NEXT FROM Table_Cur INTO @table
      END
      CLOSE Table_Cur
      FETCH NEXT FROM Stat_Cur INTO @db
END


 


CLOSE Stat_Cur
DEALLOCATE Stat_Cur
DEALLOCATE Table_Cur


 


SELECT * FROM #Stat
DROP TABLE #Stat


 


There’s another way using PowerShell (sqlps.exe). If you want to, you can run the following:  


foreach($db in “My1stDB”, “My2ndDB”) {foreach($t in “Table1”, “Table2”, “Table3”) {invoke-sqlcmd -serverinstance . -query “SELECT COUNT(*) FROM $db.dbo.$t”}}


Of course you can insert the counts in a temp table too.