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
