Use of undocumented sp_MSforeachtable and MStablespace

microsoft.public.dotnet.framework.adonet

30 Nov 2004 4:07 PM

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

30 Nov 2004 4:36 PM

David Browne

Show quote Hide quote

"Hans-Peter" <haberland***@gmx.at> wrote in message
news:b5064435.0411300807.38b3d959@posting.google.com...
> 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

Are all your drivers up to date? click for free checkup

1 Dec 2004 9:01 AM

Hans-Peter

that's it! thanks
Hans