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

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*

CodePudding user response：

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.