Results form sp_spaceused in one recordset

I would like to collect all databases statistics which contain database size and unallocated size. I'm trying to use sp_spaceused stored pr开发者_高级运维ocedure, but unfortunately I am unable to write results (this procedure get two result sets for each database) to temporary table.

My idea is to modify code of this procedure and save it as new one. What I should change?

It will be enough to concatenate two select statements?

I just created an easy version of this, from the 2008R2 sp_spaceused. It runs as a query, and returns the same data as sp_spaceused (with no parameters) but in one result set. I also added dbsize and logsize fields, because why not.

    select 
        -- from first result set of 'exec sp_spacedused' 
        db_name() as [database_name]
        ,ltrim(str((convert (dec (15,2),sf.dbsize) + convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB') as [database_size]
        ,ltrim(str((case when sf.dbsize >= pages.reservedpages then
            (convert (dec (15,2),sf.dbsize) - convert (dec (15,2),pages.reservedpages)) 
            * 8192 / 1048576 else 0 end),15,2) + ' MB') as [unallocated space]

        -- from second result set of 'exec sp_spacedused' 
        ,ltrim(str(pages.reservedpages * 8192 / 1024.,15,0) + ' KB') as [reserved]
        ,ltrim(str(pages.pages * 8192 / 1024.,15,0) + ' KB') as data
        ,ltrim(str((pages.usedpages - pages.pages) * 8192 / 1024.,15,0) + ' KB') as index_size
        ,ltrim(str((pages.reservedpages - pages.usedpages) * 8192 / 1024.,15,0) + ' KB') as unused

        -- additional:
        ,ltrim(str((convert (dec (15,2),sf.dbsize)) * 8192 / 1048576,15,2) + ' MB')  as dbsize
        ,ltrim(str((convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB')  as logsize
    FROM (
        select 
            sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) as dbsize,
            sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) as logsize
        from dbo.sysfiles
    ) sf,
    (
        select 
            sum(a.total_pages) as reservedpages,
            sum(a.used_pages) as usedpages,
            sum(
                CASE
                    -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
                    When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
                    When a.type <> 1 Then a.used_pages
                    When p.index_id < 2 Then a.data_pages
                    Else 0
                END
            ) as pages
        from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
        left join sys.internal_tables it on p.object_id = it.object_id
    ) pages

Result from sp_spaceused:

Result from above query:

You'll need to create your own version with only one resultset. This is not something I would condone at all in versions later than 2000 because this information is much easier to derive from the DMVs.

USE [master];
GO
EXEC sp_MS_upd_sysobj_category @pSeqMode = 1;
GO
CREATE PROCEDURE dbo.sp_myspaceused
    @updateusage VARCHAR(5) = 'false'
AS
BEGIN
    SET NOCOUNT ON;

    declare @pages  int;
    declare @dbname sysname;
    declare @dbsize dec(15,0);
    declare @logsize dec(15);
    declare @bytesperpage dec(15,0);
    declare @pagesperMB dec(15,0);

    create table #spt_space
    (
        rows        int null,
        reserved    dec(15) null,
        data        dec(15) null,
        indexp      dec(15) null,
        unused      dec(15) null
    );

    if @updateusage = 'true'
        dbcc updateusage(0) with no_infomsgs;

    select @dbsize = sum(convert(dec(15),size))
        from dbo.sysfiles where (status & 64 = 0);

    select @logsize = sum(convert(dec(15),size))
        from dbo.sysfiles where (status & 64 <> 0);

    select @bytesperpage = low
        from master.dbo.spt_values
        where number = 1 and type = 'E';

    select @pagesperMB = 1048576 / @bytesperpage;

    insert into #spt_space (reserved)
        select sum(convert(dec(15),reserved))
            from sysindexeswhere indid in (0, 1, 255);

    select @pages = sum(convert(dec(15),dpages))
        from sysindexes where indid < 2;

    select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
        from sysindexes where indid = 255;

    update #spt_space
        set data = @pages;

    update #spt_space
        set indexp = (select sum(convert(dec(15),used))
            from sysindexes where indid in (0, 1, 255) - data;

    update #spt_space
        set unused = reserved - (select sum(convert(dec(15),used))
            from sysindexes where indid in (0, 1, 255));

    select
        database_name = db_name(),
        database_size = ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
        [unallocated space] = ltrim(str((@dbsize -
            (select sum(convert(dec(15),reserved))
                from sysindexes
                where indid in (0, 1, 255)
            )) / @pagesperMB,15,2)+ ' MB'),
        reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' KB'),
        data = ltrim(str(data * d.low / 1024.,15,0) + ' KB'),
        index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' KB'),
        unused = ltrim(str(unused * d.low / 1024.,15,0) + ' KB')
    from #spt_space CROSS JOIN master.dbo.spt_values d
    where d.number = 1 and d.type = 'E';

    RETURN (0);
END
GO
EXEC dbo.sp_myspaceused;
GO
USE tempdb;
GO
EXEC dbo.sp_myspaceused;
GO
EXEC sp_MS_upd_sysobj_category @pSeqMode = 2;
GO

If those aren't exactly the columns you want in your table, or if you want all KB or all MB, you can comment them out / move them around / adjust the math.

Try this: ( SQL 2016 )

declare @t table(a varchar(99), dbsize varchar(99), c varchar(99), d varchar(99), e varchar(99), f varchar(99), g varchar(99))

insert into @t EXEC sp_spaceused @oneresultset=1

select * from @t