/*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'
