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?
-
2What you are doing can be done in powershell as well. So why use tsql and then dump into a csv file ?– Kin ShahFeb 11, 2019 at 21:09
-
I was trying to have less parties involved if I could done it from only one source.– Root LoopFeb 13, 2019 at 4:28
Add a comment
|
2 Answers
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.
answered Feb 12, 2019 at 0:44
-
1This is great! I really need to get on the dbaTools.io train. Hopefully soon! Feb 12, 2019 at 21:49
-
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.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/
