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.