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?
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.
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
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/