Sybase: How to query the space usage of a segment directly

Q: Using sp_helpsegment, you can see the space usage of a segment, but there are all the tables and index information on the paragraph in the result, and for a field that has many tables, it's scary. Want to see directly concerned about the space use of the paragraph, please master pointing?

For:

/*author hobbylu*/
/*reference from Sybase procedures*/
/*2005-07-01*/
CREATE PROCEDURE Sp_helpsegment_me
@segname varchar = 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,/* 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 are 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 are segment, power (2) would overflow
* * Since segmap is a 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 the 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), 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 = 32768)
Begin
/*
* * For a mixed log and data database, we cannot
* * Deduce the log used 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), 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, @total_pages),
Free_pages = Convert (char, @free_pages),
Used_pages = Convert (char, @used_pages),
Reserved_pages = Convert (char, @clr_pages)
Return (0)