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
– Thom A ♦, Commented Sep 28, 2021 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
– AlwaysLearning, Commented Sep 28, 2021 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
– Serdia, Commented Sep 28, 2021 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
– AlwaysLearning, Commented Sep 28, 2021 at 22:51
stackoverflow.com/questions/39224692/… - 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
– mjsqu, Commented Sep 28, 2021 at 23:09

mjsqu · Accepted Answer · 2021-09-29 00:54:34Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Problem

Solution

Tip

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Problem

Solution

Tip

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related