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?

  • 2
    What you are doing can be done in powershell as well. So why use tsql and then dump into a csv file ? – Kin Shah Feb 11 '19 at 21:09
  • I was trying to have less parties involved if I could done it from only one source. – Root Loop Feb 13 '19 at 4:28

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.

| improve this answer | |
  • 1
    This is great! I really need to get on the dbaTools.io train. Hopefully soon! – MguerraTorres Feb 12 '19 at 21:49
  • I ends up with foreah ($db in $dbs ) {invoke-sqlcmd......} – Root Loop Feb 13 '19 at 4:26

Absolutely. We definitely have FOR loops.


From the site:

DECLARE @cnt INT = 0;

WHILE @cnt < cnt_total
   SET @cnt = @cnt + 1;

There is also a "FOREACHDB" functionality and even foreachTable


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/

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.