sp_spaceused

[jvcoach23]

has anyone ever tried to get the data from sp_spaceused. I am trying to run just that command in a M$ sql 2000 database. When you run it in query Anaylzer, it has 3 rows returned, then it returns 4 more. when I run the sp and try to stuff it into a datareader, it tells me that there are only 3 rows in the datareader, can anyone tell me how to get all the data from sp_spaceused.

 

   sqlCnT2.Open()
       Dim sqlCMSp_SpaceUsedDb As SqlCommand = New SqlCommand
       With sqlCMSp_SpaceUsedDb
           .Connection = sqlCnT2
           .CommandText = "sp_spaceused"
           .CommandType = CommandType.StoredProcedure
       End With

       'dim dtReaderSp that will be used to hold the info from the sp_spaceused and then put it into the database
       Dim dtReaderSpDb As SqlDataReader = sqlCMSp_SpaceUsedDb.ExecuteReader

[PlausiblyDamp]

After you've looped through the first 3 records using the Read method call the datareader's NextResult method to move to the start of the next set of results.

[jvcoach23]

sorry, i mis-stated what I was trying to get. sp_spaceused give back 1 row in three columns. Then it will bring back four more columns in a second row with three different column headers. How would you navigate that... I'll try to post a result set for you to see. Let's say I'm in the master database and run

 

exec sp_spaceused

 

 

database_name database_size unallocated space

-------------------------- ------------------ ------------------

master 20.63 MB 1.32 MB

 

 

reserved data index_size unused

------------------ ------------------ ------------------ ------------------

16312 KB 11536 KB 1536 KB 3240 KB

 

 

any ideas

[PlausiblyDamp]

The following just simply prints the information in two message boxes - you could just parse the dr.GetString() into variables instead.

Dim dr As SqlClient.SqlDataReader
       Dim conn As New SqlClient.SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=true")
       conn.Open()
       Dim cmd As New SqlClient.SqlCommand
       cmd.Connection = conn
       cmd.CommandType = CommandType.StoredProcedure
       cmd.CommandText = "sp_spaceused"

       dr = cmd.ExecuteReader()

       dr.Read()
       MessageBox.Show(dr.GetString(0) & " " & dr.GetString(1) & " " & dr.GetString(2))
       dr.NextResult()
       dr.Read()
       MessageBox.Show(dr.GetString(0) & " " & dr.GetString(1) & " " & dr.GetString(2) & " " & dr.GetString(3))