foreach loop in sql scripts?

Question

In powershell I use foreach to run a function through a list of targets.

I was wondering that if there is any thing similar to foreach in sql, so I can run the same query through multiple DBs I chose and save the results to one csv file?

• Kin Shah: What you are doing can be done in powershell as well. So why use tsql and then dump into a csv file ?
• Root Loop: I was trying to have less parties involved if I could done it from only one source.

Answer 1

As Kin mentioned, while you can do this in T-SQL, PowerShell is still viable and potentially a lot easier, especially when you leverage dbatools.

You can use Get-DbaDatabase to not only get the databases but also easily filter out problematic ones you probably don't want to execute against (system dbs, offline dbs, etc):

Get-DbaDatabase -Status 'Normal' -ExcludeSystem -OnlyAccessible

And then run Invoke-DbaQuery to run your command against each of the databases returned.

• SQLDevDBA: This is great! I really need to get on the dbaTools.io train. Hopefully soon!
• Root Loop: I ends up with foreah ($db in $dbs ) {invoke-sqlcmd......}

Answer 2

Absolutely. We definitely have FOR loops.

https://www.techonthenet.com/sql_server/loops/for_loop.php

From the site:

DECLARE @cnt INT = 0;
WHILE @cnt < cnt_total
BEGIN
 {...statements...}
 SET @cnt = @cnt + 1;
END;

There is also a "FOREACHDB" functionality and even foreachTable

https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

https://www.sqlservercentral.com/Forums/Topic271576-5-1.aspx https://stackoverflow.com/questions/26496864/how-to-loop-through-all-sql-tables

But quite a few articles on why it (foreachdb) may be unreliable.

Here's an Aaron Bertrand version, which is much better: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/448

This is the direct link. https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/