0

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 Feb 11 at 21:09
  • I was trying to have less parties involved if I could done it from only one source. – Root Loop Feb 13 at 4:28
3

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.

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

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/

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.