一个记录集中使用 sp_space 形式的结果

Question

我想收集所有数据库统计信息，其中包含数据库大小和未分配大小。 我正在尝试使用 sp_spaceused 存储过程，但不幸的是我无法将结果（此过程为每个数据库获取两个结果集）写入临时表。

我的想法是修改这个程序的代码并将其另存为新的。 我应该改变什么？

连接两个 select 语句就足够了吗？

Answer 1

我刚刚从 2008R2 sp_spaceused 创建了一个简单版本。它作为查询运行，并返回与 sp_spaceused 相同的数据（不带参数），但在一个结果集中。我还添加了 dbsize 和 logsize 字段，因为为什么不呢。

    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

sp_spaceused 的结果：

上述查询的结果：

Answer 2

您需要创建自己的版本，仅包含一个结果集。在 2000 年以后的版本中，我根本不会容忍这种情况，因为从 DMV 中获取此信息要容易得多。

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

如果这些列不完全是您想要在表中出现的列，或者如果您想要所有 KB 或所有 MB，您可以将它们注释掉/移动它们/调整数学。

Answer 3

试试这个：（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