Use of undocumented sp_MSforeachtable and MStablespace

[Hans-Peter]

What is wrong with the following code? What I want to do is to store
the results of MStablespace for every table in a DataTable. The result
contains the number of rows and the data space and the index space.
This seems to be okay, because dataTable has three columns after
executing the code. The problem is, that I get only one row.

using ( SqlConnection conn = new SqlConnection(connString)
{
conn.Open();
SqlCommand cmd = new SqlCommand("sp_MSforeachtable", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@command1","exec sp_MStablespace '?'");
SqlDataAdapter adapter = new SqlDataAdapter( cmd );
DataTable dataTable = new DataTable("Statistics");
adapter.Fill(dataTable);
}

Thanks for any hints,
Hans

 

[David Browne]

What is wrong with the following code? What I want to do is to store
the results of MStablespace for every table in a DataTable. The result
contains the number of rows and the data space and the index space.
This seems to be okay, because dataTable has three columns after
executing the code. The problem is, that I get only one row.

using ( SqlConnection conn = new SqlConnection(connString)
{
conn.Open();
SqlCommand cmd = new SqlCommand("sp_MSforeachtable", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@command1","exec sp_MStablespace '?'");
SqlDataAdapter adapter = new SqlDataAdapter( cmd );
DataTable dataTable = new DataTable("Statistics");
adapter.Fill(dataTable);
}

That command will return N different 1-row result sets. The DataAdapter
will only fill the DataTable with the first result set.

Something like:

using (SqlDataReader r = cmd.ExecuteReader())
{
while (true)
{
while (r.Read() )
{
//manually fill datatable
}
if (!r.NextResult() )
{
break;
}
}
}

David

 

[Hans-Peter]

that's it! thanks
Hans