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.