如何直接查询段的空间使用情况

freekiss

使用sp_helpsegment可以看到段的空间使用情况，但是显示结果中还有段上的所有表和索引信息，对于一个建有很多表的段来说，这是很恐怖的。想直接看到所关心的段的空间使用情况，请高手指点一下。

hobbylu

/*Author Hobbylu*/
/*Reference from sybase procedures*/
/*2005-07-01*/
create procedure sp_helpsegment_me
@segname varchar(30) = NULL                /* segment name */
as
declare @segbit         int,    /* this is the bit version of the segment # */
        @segment        int,    /* the segment number of the segment */
        @free_pages     int,    /* unused pages in segment */
        @factor         float,  /* conversion factor to convert to MB */
        @clr_pages        int,        /* Space reserved for CLRs */
        @total_pages        int,        /* total allocatable log space */
        @used_pages        int,        /* allocated log space */
        @ismixedlog        int        /* mixed log & data database ? */  
if @@trancount = 0
begin
        set chained off
end
set transaction isolation level 1
set nocount on
/*
**  If no segment name given, get 'em all.
*/
if @segname is null
begin
       
--Adaptive Server has expanded all '*' elements in the following statement
select syssegments.segment, syssegments.name, syssegments.status
                from syssegments order by segment
        return (0)
end
/*
**  Make sure the segment exists
*/
if not exists (select *
        from syssegments
                where name = @segname)
begin
        /* 17520, "There is no such segment as '%1!'." */
        raiserror 17520, @segname
        return (1)
end
/*
**  Show the syssegment entry, then the fragments and size it is on,
**  then any dependent objects in the database.
*/
--Adaptive Server has expanded all '*' elements in the following statement
select syssegments.segment, syssegments.name, syssegments.status
        from syssegments
                where name = @segname
/*
**  Set the bit position for the segment.
*/
select @segment = segment
        from syssegments
                where name = @segname
/*
**  Now set the segments on @devname sysusages.
*/
if (@segment < 31)
        select @segbit = power(2, @segment)
else
        /*
        **  Since this is segment 31, power(2, 31) will overflow
        **  since segmap is an int.  We'll grab the machine-dependent
        **  bit mask from spt_values to set the right bit.
        */
        select @segbit = low
                from master.dbo.spt_values
                        where type = "E"
                                and number = 2
/*
** Get factor for conversion of pages to megabytes from spt_values
*/
select @factor = convert(float, low) / 1048576.0
        from master.dbo.spt_values
        where number = 1 and type = "E"
select @total_pages = sum(u.size)
        from master.dbo.sysusages u
        where u.segmap & @segbit = @segbit
        and u.dbid = db_id()
select @ismixedlog = status2 & 32768
        from master.dbo.sysdatabases where dbid = db_id()
/*
** Select the sizes of the segments
*/
if (@segbit = 4)
begin
    select device = d.name,
        size = convert(varchar(20), round((sum(u.size) * @factor), 0)) + "MB"
        from master.dbo.sysusages u, master.dbo.sysdevices d
            where u.segmap & @segbit = @segbit
                and u.dbid = db_id()
                and d.status & 2 = 2
                and u.vstart between d.low and d.high
            group by d.name order by d.name
    select @clr_pages = lct_admin("reserved_for_rollbacks", db_id())
    select @free_pages = lct_admin("logsegment_freepages", db_id())
                        - @clr_pages
    select free_pages = @free_pages
    if(@ismixedlog = 3276
    begin
        /*
        ** For a mixed log and data database, we cannot
        ** deduce the log used space from the total space
        ** as it is mixed with data. So we take the expensive
        ** way by scanning syslogs.
        */
        select @used_pages = lct_admin("num_logpages", db_id())
        /* Account allocation pages as used pages */
        select @used_pages = @used_pages + (@total_pages / 256)
    end
    else
    begin
        /* Dedicated log database */
        select @used_pages = @total_pages - @free_pages
                           - @clr_pages
    end
end
else
begin
    select device = d.name,
        size = convert(varchar(20), round((sum(u.size) * @factor), 0)) + "MB",
        free_pages = sum(curunreservedpgs(db_id(), u.lstart, u.unreservedpgs))
        from master.dbo.sysusages u, master.dbo.sysdevices d
            where u.segmap & @segbit = @segbit
                and u.dbid = db_id()
                and d.status & 2 = 2
                and u.vstart between d.low and d.high
            group by d.name order by d.name
    select @free_pages = sum(curunreservedpgs(db_id(), u.lstart, u.unreservedpgs))
        from master.dbo.sysusages u
            where u.segmap & @segbit = @segbit
                and u.dbid = db_id()
    select @used_pages = @total_pages - @free_pages
    select @clr_pages = 0
end
/*
** Select the dependent objects
*/

/*
** Print total_size, total_pages, free_pages, used_pages and reserved_pages
*/
select total_size = convert(varchar(15),
        round(@total_pages * @factor, 0)) + "MB",
        total_pages = convert(char(15), @total_pages),
        free_pages = convert(char(15), @free_pages),
        used_pages = convert(char(15), @used_pages),
        reserved_pages = convert(char(15), @clr_pages)
               
return (0)

freekiss

多谢了，俺也刚刚找到sp_helpsegment的脚本，正想改呢，可以参考一下大哥的成果。

fbirds

楼上的，看段的空间使用情况有什么作用呢？我一般都是查询数据库的使用空间，比如：sp_helpdb dbname,然后把usage写的data only的free kbytes加起来就是该数据库还有多少空间可用。