dump tran sybsystemprocs with truncate_only
go

use sybsystemprocs
go

if exists (select * from sysobjects where name = "sp_vdev" and type = 'P')
   drop proc sp_vdev
go

create procedure sp_vdev 
as        

/* sp_vdev: reports all SQL devices and how much free space there is for
** further allocation to databases
**
** Author: Ramakrishna Raju, Sybase Tech Support, Emeryville
** (obtained from SQL Server DBA survival guide example)
** 10/12/93	R.Raju	creation
** 9/26/97	T.Boss	bug fixed, made work, added header and footer info
**
**
** WARNING: the magic number 16777216 is valid only for 32-bit machines.
** It may or may not be valid for 64-bit machines, depending on
** whether we decide to use short or long for low, high, and vstart.
*/

begin
   set nocount on
   declare @magic int, @mbfactor float
   select @magic=16777216  /* may need to recalc for 64-bit machines */

   select @mbfactor=low/4 from master..spt_values 
   where type = "E" and number=1

   create table #tmp (nam char(32) null,
                      dev int,
                      ize float null,
                      full float)
   if @@error != 0
   begin
      print "Error encounter creating temp table.  Cannot proceed."
      return 1
   end

   insert #tmp
   select null, vstart/@magic, null, sum(size)/@mbfactor
   from master..sysusages
   group by vstart/@magic

   update #tmp 
   set nam = s.name, ize = (s.high-s.low+1)/@mbfactor
   from master..sysdevices s
   where dev = s.low/@magic and (s.status & 2 > 0 or s.status & 4 > 0)

   insert #tmp
   select name, low/@magic, (high-low+1)/@mbfactor, 0
   from master..sysdevices a
   where a.low/@magic not in (select vstart/@magic from master..sysusages)
   and (a.status&2 >0 or a.status&4 > 0)

   print "DEVICE-ALLOCATION SUMMARY."
   print "**************************"
   select "VDev #" = convert(char(3),dev), 
          "Device" = convert(char(15),nam),
          "Size (MB)" = str(ize,8,2),
          "Allocated (MB)" = str(full,8,2),
          "Free (MB)" = str(ize - full,8,2)
   from #tmp
   order by dev

   set nocount off
   return 0
end
go

if object_id('sp_vdev') is not null
begin
    print '<<< Created procedure dbo.sp_vdev >>>'
    grant execute on dbo.sp_vdev to public
end
else
begin
    print '<<< Failed creating proc dbo.sp_vdev >>>'
end
go