Q9.1: sp_freedevice
<![CDATA[
use master
go
drop proc sp_freedevice
go
create proc sp_freedevice
@devname char(30) = null
as begin
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,
((high - low) * @alloc + 500000) / 1048576,
0
from master.dbo.sysdevices
where cntrltype = 0
and not exists (select * from #freedev
where name = master.dbo.sysdevices.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
end
go
grant execute on sp_freedevice to public
go
]]>