sp_msforeachdb in dataset

Question

Can someone tell me why the following code only returns one row from the DataSet (for the master db) instead of one for each database on the server?

 $SQLConn = New-Object System.data.SqlClient.SqlConnection
 $SQLConn.ConnectionString = "Data Source = $SQLServer; Initial Catalog = master;
                             Integrated Security = True"
 $SQLConn.Open()

 $query = "exec sp_msForEachDb 'use [?] SELECT TOP 1 [name] FROM sysusers'"

 $SQLCmd = New-Object System.Data.Sqlclient.SqlCommand($query, $SQLConn);
 $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
 $SQLAdapter.SelectCommand = $SQLCmd
 $DataSet = New-Object System.Data.Dataset
 $SQLAdapter.Fill($DataSet) | out-null

 ForEach ($row in $DataSet.Tables[0])
 {
    $Name = $row["name"]
    write-host $Name
 }

 $SQLConn.Close()

Answer 1

Your code shows your using the index position 0 of $dataset.tables. Change it to $DataSet.Tables

ForEach ($row in $DataSet.Tables[0])
 {
    $Name = $row["name"]
    write-host $Name
 }

Change to...

 ForEach ($row in $DataSet.Tables)
     {
        $Name = $row["name"]
        write-host $Name
     }

Answer 2

You're returning getting multiple datatables in your result set, but only showing the first one Tables[0]. Try this:

$DataSet.Tables | foreach {$_.name}

Here's some completed and tested code to combine result set:

$SQLServer = ".\SQL1"
$query = @"
create table #output
(DB varchar(128),
name varchar(128)

)
exec sp_MSforeachdb  @command1='USE [?];
insert #output SELECT TOP 1 ''?'' AS ''DB'', [name]
FROM sysusers';
select * from #output
"@



$SQLConn = New-Object System.data.SqlClient.SqlConnection
 $SQLConn.ConnectionString = "Data Source = $SQLServer; Initial Catalog = master;
                             Integrated Security = True"
 $SQLConn.Open()


 $SQLCmd = New-Object System.Data.Sqlclient.SqlCommand($query, $SQLConn);
 $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
 $SQLAdapter.SelectCommand = $SQLCmd
 $DataSet = New-Object System.Data.Dataset
 $SQLAdapter.Fill($DataSet) | out-null

 $DataSet.Tables[0]

 $SQLConn.Close()

Answer 3

I think I needed to do something like this to get all of the records into one dataset:

 $query = "DECLARE @users TABLE ([name] varchar(100)) INSERT @db_roles 
           exec sp_msForEachDB 'USE [?] SELECT [name] FROM sysusers'
           SELECT [name] FROM @db_roles"