sybase db 공간확인

use sybsystemprocs
go
drop proc sp_freedevice
go
create proc sp_freedevice
@devname char(30) = null
as
declare @showdev bit
declare @alloc int
if @devname = null
select @devname = "%"
,@showdev = 0
else
select @showdev = 1
select @alloc = low
from master.dbo.spt_values
where type = "E"
and number = 1
create table #freedev
(
name char(30)
,size float
,used float
)
insert #freedev
select dev.name
,((dev.high - dev.low) * @alloc + 500000) / 1048576
,sum((usg.size * @alloc + 500000) / 1048576)
from master.dbo.sysdevices dev
,master.dbo.sysusages usg
where dev.low <= usg.size + usg.vstart - 1
and dev.high >= usg.size + usg.vstart - 1
and dev.cntrltype = 0
group by dev.name
insert #freedev
select name
,((sd.high - sd.low) * @alloc + 500000) / 1048576
,0
from master.dbo.sysdevices sd
where sd.cntrltype = 0
and not exists (select 1
from #freedev fd
where fd.name = sd.name)
if @showdev = 1
begin
select devname = dev.name
,size = convert(varchar(10),f.size) + " MB"
,used = convert(varchar(10),f.used) + " MB"
,free = convert(varchar(10),f.size - f.used) + " MB"
from master.dbo.sysdevices dev
,#freedev f
where dev.name = f.name
and dev.name like @devname
select dbase = db.name
,size = convert(varchar(10),
(usg.size * @alloc + 500000) / 1048576
) + " MB"
,usage = vl.name
from master.dbo.sysdatabases db
,master.dbo.sysusages usg
,master.dbo.sysdevices dev
,master.dbo.spt_values vl
where db.dbid = usg.dbid
and usg.segmap = vl.number
and dev.low <= usg.size + usg.vstart - 1
and dev.high >= usg.size + usg.vstart - 1
and dev.status & 2 = 2
and vl.type = "S"
and dev.name = @devname
end
else
begin
select total = convert(varchar(10), sum(size)) + " MB"
,used = convert(varchar(10), sum(used)) + " MB"
,free = convert(varchar(10), sum(size) - sum(used)) + " MB"
from #freedev
select devname = dev.name
,size = convert(varchar(10), f.size) + " MB"
,used = convert(varchar(10), f.used) + " MB"
,free = convert(varchar(10), f.size - f.used) + " MB"
from master.dbo.sysdevices dev
,#freedev f
where dev.name = f.name
end
go
grant execute on sp_freedevice to public
go