Use of undocumented sp_MSforeachtable and MStablespace

  • Thread starter Thread starter Hans-Peter
  • Start date Start date
H

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
 
Hans-Peter said:
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);
}
Click to expand...

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
 
Back
Top