Iterate through each database on an instance using ForEach-Object in PowerShell does not work

Question

I am new to PowerShell. Trying to understand how to use ForEach-Object command to iterate through each database on a server.

However it only bring master database multiple times and doesn't seems to be iterating through the rest of them.

# generate list of databases to iterate through
$DB = Invoke-SqlCmd -ServerInstance databasecluster  -Database master -Query "SELECT [name] AS [Database] FROM sys.databases  ORDER BY 1 DESC;"
$Query = "select DB_name();"
$DB | ForEach-Object{
           $DB = "$_";
            Invoke-Sqlcmd -Query $Query -ServerInstance myinstance;
}

What am I missing here?

UPDATE

PS C:\> $DB = Invoke-SqlCmd -ServerInstance myinstance -Database master -Query "SELECT [name] AS [Database] FROM sys.databases  ORDER BY 1 DESC;"
>> $Query = "select DB_name();"
>> $DB | ForEach-Object{
>>              $DB = "$_";
>>              Invoke-Sqlcmd -ServerInstance myinstance -Database $DB -Query $Query
>>   }

Error

*Invoke-Sqlcmd : Cannot open database "System.Data.DataRow" requested by the login. The login failed.
Login failed for user 'domain\username'.
At line:5 char:14
+ ...             Invoke-Sqlcmd -ServerInstance myinstance -Database $ ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand*

All `SELECT DB_NAME()` is going to return is the name of the database you connected to. If you want the names of all the databases on the instance have a look at `sys.databases`. — Thom A, Sep 28 '21 at 20:53
You don't specify a `-Database` parameter on your second `Invoke-SqlCmd` call, so it will be using whatever the default database of your login is... which is probably `master`. — AlwaysLearning, Sep 28 '21 at 21:27
Thanks. But now I have `System.Data.DataRow` error. Running code on local, I can connect without supplying credentials, however error indicates `Login failed` — Serdia, Sep 28 '21 at 22:30
You'll have to check SQL Server's `ERRORLOG` file for the actual reason as to why the login failed. Unless your login is a member of sysadmins in SQL Server it's likely that it doesn't have access to all databases hosted by the instance. — AlwaysLearning, Sep 28 '21 at 22:51
https://stackoverflow.com/questions/39224692/extract-data-from-system-data-datarow-in-powershell - The `$DB` object is an array of objects of types `System.Data.DataRow`. Try `$_.Database` (the name you gave the column in your query) to retrieve the values — mjsqu, Sep 28 '21 at 23:09
@mjsqu Thank you! That was the trick. Please post the answer. — Serdia, Sep 28 '21 at 23:56

score 2 · Accepted Answer · answered Sep 29 '21 at 00:54

Problem

The $DB object is an array of objects of types System.Data.DataRow. What is happening is the $_ contains an entire DataRow from the query you issued:

SELECT [name] AS [Database] FROM sys.databases  ORDER BY 1 DESC;

When $_ is specified in the ForEach-Object loop it just returns the type of the object as it has no default action, after all it is a row that could conceivably contain lots of values. You can see what happens by running these lines to address inspect the first 'row' in the $DB array:

# Just the DataRow
$DB[0]
# DB DataRow 1 column 1
$DB[0][0]
# DB First DataRow Database column
$DB[0].Database

Solution

The solution is to use $_.Database inside the loop to pick up the 'Database' column value from your query:

$DB = Invoke-SqlCmd -ServerInstance myinstance -Database master -Query "SELECT [name] AS [Database] FROM sys.databases  ORDER BY 1 DESC;"
$Query = "select DB_name();"
$DB | ForEach-Object{
             $DB = $_.Database;
             Invoke-Sqlcmd -ServerInstance myinstance -Database $DB -Query $Query
  }

Tip

It is also a good idea to use different variable names - re-using $DB inside a loop which commenced from $DB | may work but it could get confusing in more complex scripts.

Iterate through each database on an instance using ForEach-Object in PowerShell does not work

1 Answers1

Problem

Solution

Tip