read output stored procedure that returns multiple recordsets in sql 2005

Question

I'm trying to programmatically access the result of executing the system stored procedure sp_spaceused.

if you pass the name of an object as a parameter, the sp returns a recordset which you can read like this

CREATE TABLE #TempTable 
(   [Table_Name] varchar(50),
    Row_Count int,
    Table_Size varchar(50),
    Data_Space_Used varchar(50),
    Index_Space_Used varchar(50),
    Unused_Space varchar(50)
)


insert into #TempTable EXEC( 'sp_spaceused "tablexx"' )

select * from #TempTable

Table_Name   Row_Count   Table_Size  Data_Space_Used  Index_Space_Used  Unused_Space
------------ ----------- ----------- ---------------- ----------------- ------------
tablexx      67          64 KB       16 KB            48 KB             0 KB

(1 row(s) affected)

(1 row(s) affected)

the problem is that this stored procedure, when used with no parameters returns two different recordset, like this:

sp_spaceused

database_name  database_size  unallocated space
-------------- -------------- ------------------
Convenios      11622.75 MB    3.16 MB

reserved      data          index_size  unused
------------- ------------- ----------- ---------
11897696 KB   11784392 KB   103264 KB   10040 KB

I just want to get the database_size into a variable... So the problem in fact is how to programmatically access the data returned by a stored procedure that returns multiple recordset...

How can I achieve this?

Answer 1

If you are using .NET, when you fill a DataSet with the results, it will create a datatable for each result set.

Answer 2

I don't have a solution for your original problem of getting hold of the second result set returned by the stored procedure. But in this case here, could you maybe use this script instead, which should give you roughly the same information, and it's a nice, clean T-SQL script:

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN   
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND  
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
    object_name(i.object_id)

If you still insist on using the sp_spaceused stored proc, how about this solution: use the undocumented (but extremely helpful) sp_MSforeachtable stored proc to call sp_spaceused iteratively for each table in your database:

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

In this case, you get one row per table in the database and you should be able to catch that and insert it into your "space used table".

Hope this helps!
Marc