How to Store sp_spaceused Results

Try this:

With a slight mod, you could have this insert into your table.

/*similar to sp_spaceused */


declare @dbsize decimal(19,2)
       ,@logsize decimal(19,2)


set nocount on

/*
**  Summary data.
*/
begin
    select @dbsize = sum(convert(decimal(19,2),case when type = 0 then size else 0 end)) * 8/1024
       , @logsize = sum(convert(decimal(19,2),case when type = 1 then size else 0 end)) * 8/1024
       from sys.database_files

end
/*
**  We want all objects.
*/
Begin
    With FirstPass as (
       SELECT OBJECT_ID,
         ReservedPage = convert(decimal(19,2),SUM(reserved_page_count)) * 8/1024,
         UsedPage = convert(decimal(19,2),SUM(used_page_count)) *8/1024,
         PageCnt = SUM(
         convert(decimal(19,2),CASE
          WHEN (index_id < 2) 
              THEN (used_page_count)
          ELSE lob_used_page_count + row_overflow_used_page_count
          END
         )) * 8/1024,
         RowCnt = SUM(
         CASE
          WHEN (index_id < 2) 
              THEN row_count
          ELSE 0
         END
         )
       FROM sys.dm_db_partition_stats 
       --Where OBJECTPROPERTY(OBJECT_ID,'IsMSShipped') = 0
       Group By OBJECT_ID
    )
    ,InternalTables as (
       Select ps.OBJECT_ID,
         ReservedPage = convert(decimal(19,2),SUM(reserved_page_count)) * 8/1024,
         UsedPage = convert(decimal(19,2),SUM(used_page_count)) *8/1024
       From sys.dm_db_partition_stats  ps
         Inner Join sys.internal_tables it
          On it.OBJECT_ID = ps.OBJECT_ID
          And it.internal_type IN (202,204,211,212,213,214,215,216)
       Where it.parent_id = ps.OBJECT_ID
         --And OBJECTPROPERTY(ps.OBJECT_ID,'IsMSShipped') = 0
       Group By ps.OBJECT_ID
    )
    ,Summary as (
       SELECT 
         ObjName = OBJECT_NAME (f.OBJECT_ID),
         NumRows = MAX(f.rowcnt),
         ReservedPageMB = SUM(IsNull(f.reservedpage,0) + IsNull(i.ReservedPage,0)),
         DataSizeMB = Sum(f.PageCnt),
         IndexSizeMB = Sum(CASE WHEN (f.UsedPage + IsNull(i.UsedPage,0)) > f.PageCnt 
                   THEN ((f.UsedPage + IsNull(i.UsedPage,0)) - f.PageCnt) ELSE 0 END) ,-- Equivalent of max_record_size from sys.dm_db_index_physical_stats
         UnusedSpace = Sum(CASE WHEN (f.ReservedPage + IsNull(i.ReservedPage,0)) > (f.UsedPage + IsNull(i.UsedPage,0)) 
          THEN ((f.ReservedPage + IsNull(i.ReservedPage,0)) - (f.UsedPage + IsNull(i.UsedPage,0))) ELSE 0 END),
         DBSizeMB = @Dbsize,
         LogSizeMB = @logsize
       From FirstPass F
         Left Outer Join InternalTables i
         On i.OBJECT_ID = f.OBJECT_ID
       Group By f.OBJECT_ID
    )
    Select ObjName,NumRows, ReservedPageMB, DataSizeMB, IndexSizeMB, UnusedSpace, DBSizeMB, LogSizeMB,
         PercentofDB = ((IndexSizeMb + DataSizeMB) / @DBsize) * 100

    From Summary
    Order By PercentofDB desc
End

You can also find that on my blog at http://jasonbrimhall.info/2010/05/25/space-used/

If you are just looking to capture the size of your database and store this for historical reasons and for monitoring try using sysfiles instead of sp_spaceused.

Hello

Try the below script

create table #test (    --drop table #test                                                             
  database_name varchar(255)                                                                           
  ,database_size varchar(255) not null                                                                 
  ,unallocated_space varchar(255) not null                                                             
  ,reserved varchar(255) not null                                                                      
  ,data varchar(255) not null                                                                          
  ,index_size varchar(255) not null                                                                    
  ,unused varchar(255) not null                                                                        
  )                                                                                                    
go                                                                                                     
exec dbo.sp_msforeachdb                                                                                
'                                                                                                      
   if db_id(''?'') > 4                                                                                 
            begin                                                                                         
      use [?];                                                                                         
      declare @cmd varchar(max);                                                                       
      declare c cursor local for                                                                       
                    select cmd = ''use [?]; exec sp_spaceused ''''''                           
                   + schema_name(schema_id) + ''.''                                  
                   + name + '''''', true'' from sys.objects                          
       where objectproperty(object_id, ''IsUserTable'') = 1                       
      open c;                                                                                          
      fetch next from c into @cmd                                                                     
      while @@fetch_status = 0                                                                         
               begin                                                                               
                 insert #test (database_name, database_size, unallocated_space,reserved,   data, index_size, unused) exec (@cmd);                                                                
         fetch next from c into @cmd                                                                  
                    end;                                                                                
      close c;                                                                                         
      deallocate c;                                                                                    
   end                                                                                                 
'                                                                                                      
select * from #test                                                                                    

I am not aware of a way to do this within T-SQL, and some quick research did not turn up a way. I suspect that it may not be possible in pure T-SQL, but I would be glad to be proven wrong.

On the other hand, if you step out of pure T-SQL most client languages make it relatively easy to deal with multiple returned data sets. If you are using C# for instance, you should just be able to use the NextResult method, as described here http://csharp.net-informations.com/data-providers/csharp-multiple-resultsets.htm and here: http://www.mindfiresolutions.com/NextResult-in-C-or-ADONET-data-reader-705.php

If you are using python with pyodbc, then you can use the nextset function of the cursor to advance to the next dataset. See http://code.google.com/p/pyodbc/wiki/Cursor#nextset