use sybsystemprocs
go
drop procedure sp_db
go
drop procedure sp_dbopt
go
drop procedure sp_deviceUNIX
go
drop procedure sp_devicespaceagg
go
drop procedure sp_devicespaceall
go
drop procedure sp_devicespacedvc
go
drop procedure sp_devicestatus
go
drop procedure sp_fragments
go
drop procedure sp_freedevice
go
drop procedure sp_users
go

set quoted_identifier on
go

create procedure sp_db (@DATABASE varchar (30) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_db> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @bitoff       bit,
           @biton        bit,
           @cachename    varchar (28),
           @dbconfig     smallint,
           @dbid         smallint,
           @dbtotal      smallint,
           @maxdblen     tinyint,
           @maxtextseq   int,
           @sequence     int

   select @bitoff = convert (bit, 0)

   create table #db
      (dbid         int            not null,
       name         varchar (30)   not null,
       status       smallint       not null,
       status2      int            not null,
       owner        smallint       not null,
       crdate       smalldatetime  not null,
       dataflag     bit            not null,
       logflag      bit            not null,
       mixedflag    bit            not null,
       datalogtext  varchar (30)   not null,
       size         char (11)      null,
       txt          varchar (70)   null)

   select @biton      = ~ (@bitoff),
          @maxtextseq = max (txt_seq)
   from sybsystemprocs.dbo.udboption

   select @dbconfig = u.value
   from master.dbo.sysconfigures c,
        master.dbo.syscurconfigs u
   where c.name   = 'number of open databases'
     and u.config = c.config

   select @dbtotal = count (*)
   from master.dbo.sysdatabases

   if (@dbconfig <= @dbtotal)
      begin
         print '    WARNING: THE CONFIGURED NUMBER OF OPEN DATABASES IS %1!.',
               @dbconfig

         print ' '

         print '             THE CURRENT NUMBER OF DATABASES IS %1!.',
               @dbtotal

         print ' '
      end

   if (exists (select *
               from master.dbo.sysprocesses
               where cmd = 'LOG SUSPEND'))
      begin
         print '    WARNING: THERE ARE DATABASES WITH SUSPENDED LOGS.'

         print ' '

         select distinct 'DATABASE' = db_name (dbid)
         from master.dbo.sysprocesses
         where cmd = 'LOG SUSPEND'
         order by 'DATABASE'

         print ' '
      end

   select @sequence = 1

   if ((@DATABASE IS NULL)  or  (upper (@DATABASE) in ('DATE', 'SIZE')))
      begin
         insert into #db
            select distinct d.dbid,
                            d.name,
                            d.status,
                            d.status2,
                            d.suid,
                            d.crdate,
                            @bitoff,
                            @bitoff,
                            @bitoff,
                            'LOGIC ERROR!',
                            substring (convert (char (14),
                                                convert (money,
                                                         sum (u.size)),
                                                1),
                                       1,
                                       11),
                            NULL
            from master.dbo.sysdatabases d,
                 master.dbo.sysusages u
            where d.dbid = u.dbid
            group by d.dbid
            having d.dbid = u.dbid

         update #db
         set txt = '--Cached'
         from #db d, master.dbo.sysattributes a
         where a.class       = 3
           and a.attribute   = 0
           and a.object_type = 'D'
           and a.object      = d.dbid

         while (@sequence < @maxtextseq + 1)
            begin
               update #db
               set txt = substring (ltrim (#db.txt  +  ', '  +  o.txt), 1, 70)
               from sybsystemprocs.dbo.udboption o
               where o.txt_seq   = @sequence
                 and o.dboption  = (#db.status  & o.dboption)
                 and o.dboption2 = 0

               update #db
               set txt = substring (ltrim (#db.txt  +  ', '  +  o.txt), 1, 70)
               from sybsystemprocs.dbo.udboption o
               where o.txt_seq   = @sequence
                 and o.dboption  = 0
                 and o.dboption2 = (#db.status2 & o.dboption2)

               select @sequence = @sequence + 1
            end

         update #db
         set txt = '  (No options set)'
         where txt IS NULL

         select @maxdblen = max (char_length (name))
         from #db

         if (@DATABASE IS NULL)
            begin
            if (@maxdblen < 16)
               begin
                  select NAME          = substring (name, 1, 15),
                         CONFIGURATION = substring (txt, 3, 61)
                  from #db
                  order by name
               end
            else if (@maxdblen < 20)
               begin
                  select NAME          = substring (name, 1, 19),
                         CONFIGURATION = substring (txt, 3, 57)
                  from #db
                  order by name
               end
            else
               begin
                  select NAME          = name,
                         CONFIGURATION = substring (txt, 3, 46)
                  from #db
                  order by name
               end
            end
         else
            begin
               update #db
               set dataflag = @biton
               from #db
               where exists (select u.*
                             from master.dbo.sysusages u
                             where u.dbid          = #db.dbid
                               and (u.segmap & 7) in (0, 1, 2, 3, 5, 6))

               update #db
               set logflag = @biton
               from #db
               where exists (select u.*
                             from master.dbo.sysusages u
                             where u.dbid         = #db.dbid
                               and (u.segmap & 7) = 4)

               update #db
               set mixedflag = @biton
               from #db
               where exists (select u.*
                             from master.dbo.sysusages u
                             where u.dbid         = #db.dbid
                               and (u.segmap & 7) = 7)

               update #db
               set datalogtext = 'separate'
               where mixedflag = @bitoff

               update #db
               set datalogtext = 'mixed'
               where mixedflag = @biton
                 and dataflag  = @bitoff
                 and logflag   = @bitoff

               update #db
               set datalogtext = 'mixed + log'
               where mixedflag = @biton
                 and dataflag  = @bitoff
                 and logflag   = @biton

               update #db
               set datalogtext = 'mixed + data'
               where mixedflag = @biton
                 and dataflag  = @biton
                 and logflag   = @bitoff

               update #db
               set datalogtext = 'mixed + data + log'
               where mixedflag = @biton
                 and dataflag  = @biton
                 and logflag   = @biton

               if (upper (@DATABASE) = 'DATE')
                  begin
                     select CREATED       = convert (char (8), crdate, 11),
                            NAME          = substring (name, 1, 21),
                            OWNER         = substring (isnull (suser_name (owner), '?'), 1, 15),
                            'TOTAL PAGES' = size,
                            'DATA/LOG'    = substring (datalogtext, 1, 18)
                     from #db
                     order by crdate, name
                  end
               else
                  begin
                     select CREATED       = convert (char (8), crdate, 11),
                            NAME          = substring (name, 1, 21),
                            OWNER         = substring (isnull (suser_name (owner), '?'), 1, 15),
                            'TOTAL PAGES' = size,
                            'DATA/LOG'    = substring (datalogtext, 1, 18)
                     from #db
                     order by size, name
                  end
            end
      end
   else
      begin
         select @dbid = dbid
         from master.dbo.sysdatabases
         where name = @DATABASE

         if (@dbid IS NULL)
            begin
               print '    DATABASE <%1!> DOES NOT EXIST.', @DATABASE

               print ' '
            end
         else
            begin
               select @cachename = substring (char_value, 1, 28)
               from master.dbo.sysattributes
               where class       = 3
                 and attribute   = 0
                 and object_type = 'D'
                 and object      = @dbid

               select OWNER   = isnull (suser_name (d.suid), '?'),
                      CREATED = convert (char (8), d.crdate, 11)
                                + ' '
                                + convert (char (8), d.crdate, 8),
                      CACHE   = isnull (@cachename, '-')
               from master.dbo.sysdatabases d
               where d.name = @DATABASE

               print ' '

               if (exists (select *
                           from master.dbo.sysdatabases
                           where name    = @DATABASE
                             and status  = 0
                             and status2 = 0))
                  begin
                     print '    NO OPTIONS ARE SET FOR DATABASE <%1!>.', @DATABASE

                     print ' '
                  end
               else
                  begin
                        select 'CONFIGURATION TEXT' = convert (char (30), ' '),
                               STATUS               = convert (int, 0),
                               STATUS2              = convert (int, 0)
                        where 0 = 1
                     union all
                        select u.txt,
                               u.dboption,
                               0
                        from master.dbo.sysdatabases d,
                             sybsystemprocs.dbo.udboption u
                        where d.name       = @DATABASE
                          and u.dboption   = (d.status & u.dboption)
                          and u.dboption2  = 0
                     union all
                        select u.txt,
                               0,
                               u.dboption2
                        from master.dbo.sysdatabases d,
                             sybsystemprocs.dbo.udboption u
                        where d.name       = @DATABASE
                          and u.dboption   = 0
                          and u.dboption2  = (d.status2 & u.dboption2)
                     order by STATUS2, STATUS
                  end
            end
      end
go
sp_addcomment sp_db, 16, 'Lists database configurations with various scope/sort options'
go
sp_addcomment sp_db, 16, '   (NULL):   (possibly incomplete) info for all databases (default)'
go
sp_addcomment sp_db, 16, '   DATABASE: complete info only for specified database'
go
sp_addcomment sp_db, 16, '   "DATE":   partial info for all databases in creation order'
go
sp_addcomment sp_db, 16, '   "SIZE":   partial info for all databases in size order'
go
sp_procxmode sp_db, anymode
go

create procedure sp_dbopt (@DBOPTION varchar (30) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_dbopt> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @dbcount       tinyint,
           @fulldboption  varchar (30),
           @likecount     tinyint

   if (@DBOPTION IS NULL)
      begin
         set nocount on

         create table #dbopt
            (sort      varchar (30)  not null,
             dboption  varchar (30)  null,
             db        varchar (30)  null)

         insert into #dbopt
            select vD.name, vD.name, d.name
            from master.dbo.sysdatabases d,
                 sybsystemprocs.dbo.usptvalues vD
            where vD.type                = 'D'
              and (d.status & vD.number) = vD.number

         insert into #dbopt
            select vD2.name, vD2.name, d.name
            from master.dbo.sysdatabases d,
                 sybsystemprocs.dbo.usptvalues vD2
            where vD2.type                 = 'D2'
              and (d.status2 & vD2.number) = vD2.number

         insert into #dbopt
            select sort, NULL, NULL
            from #dbopt
            group by sort

         delete from #dbopt
         where dboption like 'trunc.%'

         delete from #dbopt
         where dboption IS NULL
           and sort = (select min (sort)
                       from #dbopt)

         select 'DATABASE OPTION' = isnull (dboption, ' '),
                'DATABASE'        = isnull (db, ' ')
         from #dbopt
         order by sort, db
      end
   else
      begin
         select @likecount = count (*)
         from sybsystemprocs.dbo.usptvalues
         where name like @DBOPTION + '%'
           and type   in ('D', 'D2')

         if (@likecount = 0)
            begin
               print '    <%1!> IS NOT A DATABASE OPTION.', @DBOPTION

               print ' '
            end
         else if (@likecount = 1)
            begin
               select @fulldboption = name
               from sybsystemprocs.dbo.usptvalues
               where name like @DBOPTION + '%'
                 and type   in ('D', 'D2')

               select @dbcount = count (d.dbid)
               from master.dbo.sysdatabases d,
                    sybsystemprocs.dbo.usptvalues   vD
               where vD.name like @DBOPTION + '%'
                 and (((vD.type = 'D')   and ((d.status & vD.number)  = vD.number))  or
                      ((vD.type = 'D2')  and ((d.status2 & vD.number) = vD.number)))

               if (@dbcount = 0)
                  begin
                     print '    NO DATABASE USES OPTION <%1!>.', @fulldboption

                     print ' '
                  end
               else
                  begin
                     print '    DATABASE OPTION IS <%1!>.', @fulldboption

                     print ' '

                        select 'DATABASE' = space (30)
                        where 1 = 2
                     union
                        select d.name
                        from master.dbo.sysdatabases d,
                             sybsystemprocs.dbo.usptvalues   vD
                        where vD.name                 like @DBOPTION + '%'
                          and vD.type                    = 'D'
                          and (d.status  & vD.number)    = vD.number
                     union
                        select d.name
                        from master.dbo.sysdatabases d,
                             sybsystemprocs.dbo.usptvalues   vD2
                        where vD2.name                  like @DBOPTION + '%'
                          and vD2.type                     = 'D2'
                          and (d.status2  & vD2.number)    = vD2.number
                     order by 'DATABASE'
                  end
            end
         else
            begin
               print '    DATABASE OPTION <%1!> IS NOT UNIQUE.', @DBOPTION

               print ' '

               select 'DATABASE OPTION' = name
               from sybsystemprocs.dbo.usptvalues
               where name like @DBOPTION + '%'
                 and type   in ('D', 'D2')
               order by name
            end
      end
go
sp_addcomment sp_dbopt, 16, 'Lists databases with DBOPTION or (default) all databases by oprtion'
go
sp_procxmode sp_dbopt, anymode
go

create procedure sp_deviceUNIX (@DEVICE varchar (30) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_deviceUNIX> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   create table #deviceUNIX
     (cntrltype smallint      not null,
      number    tinyint       not null,
      name      char (30)     not null,
      physical  char (73)     not null,
      mirrorx   char (36)     null,
      sort      varchar (73)  null,
      C         tinyint       null,
      T         tinyint       null,
      S         tinyint       null)

   insert into #deviceUNIX
      select cntrltype,
             low / power (2, 24),
             name,
             substring (phyname, 1, 72),
             substring (mirrorname, 1, 36),
             substring (phyname, 1, 72),
             NULL,
             NULL,
             NULL
      from master.dbo.sysdevices

   if (@DEVICE IS NULL)
      begin
         update #deviceUNIX
         set sort = '0'
         where cntrltype <> 0
            or physical   = 'd_master'

         while (exists (select *
                        from #deviceUNIX
                        where charindex ('/', sort) > 0))
            begin
               update #deviceUNIX
               set sort = substring (sort, charindex ('/', sort) + 1, 73)
               where charindex ('/', sort) > 0
            end

         update #deviceUNIX
         set C = convert (tinyint,
                          substring (sort,
                                     charindex ('c', sort) + 1,
                                     charindex ('t', sort) - charindex ('c', sort) - 1))
         where cntrltype             = 0
           and charindex ('c', sort) > 0
           and charindex ('t', sort) > 0

         update #deviceUNIX
         set T = convert (tinyint,
                          substring (sort,
                                     charindex ('t', sort) + 1,
                                     charindex ('d', sort) - charindex ('t', sort) - 1))
         where cntrltype             = 0
           and charindex ('t', sort) > 0
           and charindex ('d', sort) > 0

         update #deviceUNIX
         set S = convert (tinyint,
                          substring (sort,
                                     charindex ('s', sort) + 1,
                                     3))
         where cntrltype             = 0
           and charindex ('s', sort) > 0

         if (exists (select *
                     from #deviceUNIX
                     where mirrorx IS NOT NULL))
            begin
               select NUM      = number,
                      DEVICE   = substring (name, 1, 21),
                      PHYSICAL = substring (physical, 1, 25),
                      'MIRROR' = isnull (substring (mirrorx, 1, 25), ' ')
               from #deviceUNIX
               order by cntrltype, C, T, S, name
            end
         else
            begin
               select NUM      = number,
                      DEVICE   = name,
                      PHYSICAL = substring (physical, 1, 42)
               from #deviceUNIX
               order by cntrltype, C, T, S, name
            end
      end
   else if (upper (@DEVICE) = 'SORT')
      begin
         if (exists (select *
                     from #deviceUNIX
                     where mirrorx IS NOT NULL))
            begin
               select NUM      = number,
                      DEVICE   = substring (name, 1, 21),
                      PHYSICAL = substring (physical, 1, 25),
                      'MIRROR' = isnull (substring (mirrorx, 1, 25), ' ')
               from #deviceUNIX
               order by cntrltype, number, name
            end
         else
            begin
               select NUM      = number,
                      DEVICE   = name,
                      PHYSICAL = substring (physical, 1, 42)
               from #deviceUNIX
               order by cntrltype, number, name
            end
      end
   else if (exists (select *
                    from #deviceUNIX
                    where name = @DEVICE))
      begin
         set nocount on

         if (exists (select *
                     from #deviceUNIX
                     where name     = @DEVICE
                       and mirrorx IS NOT NULL))
            begin
               select NUM      = number,
                      PHYSICAL = substring (physical, 1, 36),
                      'MIRROR' = mirrorx
               from #deviceUNIX
               where name = @DEVICE
            end
         else
            begin
               select NUM      = number,
                      PHYSICAL = physical
               from #deviceUNIX
               where name = @DEVICE
            end
      end
   else
      begin
         print '    USAGE: sp_deviceUNIX:        All UNIX file names by type & physical device'

         print '           sp_deviceUNIX "SORT": All UNIX file names by type & device number'

         print '           sp_deviceUNIX DEVICE: UNIX file name(s) for DEVICE'

         print ' '
      end
go
sp_addcomment sp_deviceUNIX, 16, 'Lists UNIX file name(s) for DEVICE or (default) all devices'
go
sp_procxmode sp_deviceUNIX, anymode
go

------------ Temporary table needed for creation of sp_devicespace subroutines

create table #devicespace
  (unixname   varchar (127)  not null,
   devname    varchar (30)   not null,
   dbname     varchar (30)   not null,
   status     smallint       not null,
   firstpage  int            not null,
   fpprint    char (15)      not null,
   size       int            not null,
   sprint     char (15)      not null,
   comment    varchar (21)   not null,
   C          tinyint        not null,
   T          tinyint        not null,
   S          smallint       not null)
go

create procedure sp_devicespaceagg as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

   insert into #devicespace                             -- Allocated
      select distinct d.phyname,
                      d.name,
                      ' ',
                      d.status,
                      0,
                      substring (convert (char (18),
                                          convert (money,
                                                   1 + d.high - d.low),
                                          1),
                                 1,
                                 15),
                      1 + d.high - d.low,
                      substring (convert (char (18),
                                          convert (money,
                                                   sum (u.size)),
                                          1),
                                 1,
                                 15),
                      substring (convert (char (18),
                                          convert (money,
                                                   1 + d.high - d.low - sum (u.size)),
                                          1),
                                 1,
                                 15),
                      0,
                      0,
                      0
      from master.dbo.sysdevices d,
           master.dbo.sysusages  u
      where d.cntrltype        = 0
        and u.vstart + u.size >= d.low
        and u.vstart + u.size <= d.high + 1
      group by d.name

   insert into #devicespace                             -- Free
      select d.phyname,
             d.name,
             ' ',
             d.status,
             0,
             substring (convert (char (18),
                                 convert (money,
                                          1 + d.high - d.low),
                                 1),
                        1,
                        15),
             1 + d.high - d.low,
             '              0',
             substring (convert (char (18),
                                 convert (money,
                                          1 + d.high - d.low),
                                 1),
                        1,
                        15),
             0,
             0,
             0
      from master.dbo.sysdevices d
      where d.cntrltype        = 0
        and not exists (select u.*
                        from master.dbo.sysusages u
                        where u.vstart + u.size >= d.low
                          and u.vstart + u.size <= d.high + 1)

   while (exists (select *
                  from #devicespace
                  where charindex ('/', unixname) > 0))
      begin
         update #devicespace
         set unixname = substring (unixname, charindex ('/', unixname) + 1, 73)
         where charindex ('/', unixname) > 0
      end

   update #devicespace
   set C = convert (tinyint,
                    substring (unixname,
                               charindex ('c', unixname) + 1,
                               charindex ('t', unixname) - charindex ('c', unixname) - 1))
   where charindex ('c', unixname)  > 0
     and charindex ('t', unixname)  > 0
     and unixname                  <> 'd_master'

   update #devicespace
   set T = convert (tinyint,
                    substring (unixname,
                               charindex ('t', unixname) + 1,
                               charindex ('d', unixname) - charindex ('t', unixname) - 1))
   where charindex ('t', unixname)  > 0
     and charindex ('d', unixname)  > 0
     and unixname                  <> 'd_master'

   update #devicespace
   set S = convert (tinyint,
                    substring (unixname,
                               charindex ('s', unixname) + 1,
                               3))
   where charindex ('s', unixname)  > 0
     and unixname                  <> 'd_master'

   if (exists (select *
               from #devicespace
               where unixname = 'd_master'))
      begin
         print '    AGGREGATE DEVICE INFORMATION EXCEPT FOR d_master WILL BE LISTED.'
      end
   else
      begin
         print '    AGGREGATE DEVICE INFORMATION WILL BE LISTED.'
      end

   print ' '

   select distinct 'DISK'        = 'c'
                                   +  convert (varchar (3), C)
                                   +  't'
                                   +  convert (varchar (3), T),
                   DEVICES       = convert (tinyint, count (unixname)),
                   'TOTAL PAGES' = substring (convert (char (14),
                                                       convert (money,
                                                                sum (size)),
                                                       1),
                                              1,
                                              11)
   from #devicespace
   where unixname <> 'd_master'
   group by C, T
   having unixname <> 'd_master'
   order by C, T

   print ' '

   set nocount on                                       -- Count would include blank lines

   insert into #devicespace
      select distinct ' ', ' ', ' ', 0, 0, ' ', 0, ' ', ' ', C, T, -1
      from #devicespace
      group by C, T

   delete from #devicespace
   where S               = -1
     and ((255 * C) + T) = (select min ((255 * C) + T)
                            from #devicespace)

   select 'UNIX DEVICE'     = substring (unixname, 1, 11),
          'SYBASE DEVICE'   = substring (devname, 1, 14),
          'TOTAL PAGES'     = substring (fpprint, 1, 15),
          'ALLOCATED PAGES' = substring (sprint, 1, 15),
          'FREE PAGES'      = substring (comment,1 , 15)
   from #devicespace
   order by C, T, S
go
sp_procxmode sp_devicespaceagg, unchained
go

create procedure sp_devicespaceall as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   print ' '

   print '    ALL DEVICE SPACE INFORMATION WILL BE LISTED.'

   print ' '

   insert into #devicespace                             -- allocated
      select d.phyname,
             d.name,
             db_name (u.dbid),
             d.status,
             u.vstart,
             ' ',
             u.size,
             ' ',
             isnull (substring (vS.name, 1, 12), ''),
             0,
             0,
             0
      from master.dbo.sysdevices d,
           master.dbo.sysusages  u,
           master.dbo.spt_values vS
      where d.cntrltype  = 0
        and d.low       <= u.vstart + u.size
        and d.high      >= u.vstart + u.size - 1
        and vS.number    = (u.segmap & 7)
        and vS.type      = 'S'

   insert into #devicespace                             -- free low
      select d.phyname,
             d.name,
             '-------FREE-------',
             d.status,
             d.low,
             ' ',
             abs (u.vstart - d.low),
             ' ',
             ' ',
             0,
             0,
             0
      from master.dbo.sysdevices d,
           master.dbo.sysusages  u
      where u.vstart     = (select min (vstart)
                            from master.dbo.sysusages
                            where vstart + size >= d.low
                              and vstart + size <= d.high + 1)
        and d.low       <> u.vstart
        and d.low       <> u.vstart - 4
        and d.cntrltype  = 0
      group by d.low
      having u.vstart     = (select min (vstart)
                             from master.dbo.sysusages
                             where vstart + size >= d.low
                               and vstart + size <= d.high + 1)
         and d.low       <> u.vstart
         and d.low       <> u.vstart - 4
         and d.cntrltype  = 0

   insert into #devicespace                             -- free middle
      select d.phyname,
             d.name,
             '-------FREE-------',
             d.status,
             u1.vstart + u1.size,
             ' ',
             abs (u2.vstart - u1.vstart - u1.size),
             ' ',
             ' ',
             0,
             0,
             0
      from master.dbo.sysdevices d,
           master.dbo.sysusages u1,
           master.dbo.sysusages u2
      where d.low       <= u1.vstart
        and d.high      >= u1.vstart + u1.size - 1
        and u2.vstart    = (select min (u3.vstart)
                            from master.dbo.sysusages u3
                            where u3.vstart > u1.vstart)
        and u2.vstart   <> u1.vstart + u1.size
        and d.low       <= u2.vstart + u2.size
        and d.high      >= u2.vstart + u2.size - 1
        and d.cntrltype  = 0

   insert into #devicespace                             -- free high
      select d.phyname,
             d.name,
             '-------FREE-------',
             d.status,
             u.vstart + u.size,
             ' ',
             abs (d.high - u.vstart - u.size + 1),
             ' ',
             ' ',
             0,
             0,
             0
      from master.dbo.sysdevices d,
           master.dbo.sysusages  u
      where u.vstart    = (select max (vstart)
                           from master.dbo.sysusages
                           where vstart + size >= d.low
                             and vstart + size <= d.high + 1)
        and d.high      <> u.vstart + u.size - 1
        and d.cntrltype  = 0
      group by d.low
      having u.vstart     = (select max (vstart)
                             from master.dbo.sysusages
                             where vstart + size >= d.low
                               and vstart + size <= d.high + 1)
         and d.high      <> u.vstart + u.size - 1
         and d.cntrltype  = 0

   insert into #devicespace                             -- free devices
      select d.phyname,
             d.name,
             '-------FREE-------',
             d.status,
             d.low,
             ' ',
             abs (d.high - d.low + 1),
             ' ',
             ' ',
             0,
             0,
             0
      from master.dbo.sysdevices d
      where d.cntrltype = 0
        and (not exists (select u.*
                         from master.dbo.sysusages u
                         where d.low  <= u.vstart
                           and d.high >= u.vstart))

   update #devicespace
   set comment = substring ('mirrored ' + comment, 1, 30)
   where (status & 512) = 512
     and (status & 128) = 128
     and (status & 64)  = 64

   while (exists (select *
                  from #devicespace
                  where charindex ('/', unixname) > 0))
      begin
         update #devicespace
         set unixname = substring (unixname, charindex ('/', unixname) + 1, 73)
         where charindex ('/', unixname) > 0
      end

   update #devicespace
   set C = convert (tinyint,
                    substring (unixname,
                               charindex ('c', unixname) + 1,
                               charindex ('t', unixname) - charindex ('c', unixname) - 1))
   where charindex ('c', unixname)  > 0
     and charindex ('t', unixname)  > 0
     and unixname                  <> 'd_master'

   update #devicespace
   set T = convert (tinyint,
                    substring (unixname,
                               charindex ('t', unixname) + 1,
                               charindex ('d', unixname) - charindex ('t', unixname) - 1))
   where charindex ('t', unixname)  > 0
     and charindex ('d', unixname)  > 0
     and unixname                  <> 'd_master'

   update #devicespace
   set unixname = substring (unixname, 1, charindex ('s', unixname) - 1),
       S        = convert (tinyint,
                           substring (unixname,
                                      charindex ('s', unixname) + 1,
                                      3))
   where charindex ('s', unixname)  > 0
     and unixname                  <> 'd_master'

   update #devicespace
   set unixname = ' '
   where unixname = 'd_master'

   update #devicespace
   set sprint = substring (convert (char (18), convert (money, size), 1), 2, 14)

   --
   -- "Separate" by devicename.
   --               ----------

   update #devicespace
   set devname = ' '
   from #devicespace d
   where exists (select *
                 from #devicespace d2
                 where d2.devname   = d.devname
                   and d2.firstpage = d.firstpage
                   and d2.firstpage > (select min (d3.firstpage)
                                       from #devicespace d3
                                       where d3.devname = d2.devname))

   --
   -- But insert blank lines between disks.
   --                                -----

   insert into #devicespace
      select distinct min (unixname), ' ', ' ', 0, 0, ' ', 0, ' ', ' ', C, T, -1
      from #devicespace
      group by unixname

   delete from #devicespace
   where S        = -1
     and unixname = (select min (unixname)
                     from #devicespace)

   select DEVICE           = substring (devname, 1, 16),
          'DATABASE'       = substring (dbname, 1, 18),
          'FRAGMENT PAGES' = substring (sprint, 1, 14),
          'COMMENT'        = comment
   from #devicespace
   order by C, T, S, firstpage
go
sp_procxmode sp_devicespaceall, unchained
go

create procedure sp_devicespacedvc (@DEVICE varchar (30)) as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   print ' '

   print '    SPACE INFORMATION FOR DEVICE <%1!> WILL BE LISTED.', @DEVICE

   print ' '

   insert into #devicespace                             -- allocated
      select ' ',
             ' ',
             db_name (u.dbid),
             d.status,
             u.vstart,
             ' ',
             u.size,
             ' ',
             isnull (substring (vS.name, 1, 12), ''),
             0,
             0,
             0
      from master.dbo.sysdevices d,
           master.dbo.sysusages  u,
           master.dbo.spt_values vS
      where d.cntrltype  = 0
        and d.name       = @DEVICE
        and d.low       <= u.vstart + u.size
        and d.high      >= u.vstart + u.size - 1
        and vS.number    = (u.segmap & 7)
        and vS.type      = 'S'

   insert into #devicespace                             -- free low
      select ' ',
             ' ',
             '--------FREE--------',
             d.status,
             d.low,
             ' ',
             abs (u.vstart - d.low),
             ' ',
             ' ',
             0,
             0,
             0
      from master.dbo.sysdevices d,
           master.dbo.sysusages  u
      where u.vstart     = (select min (vstart)
                            from master.dbo.sysusages
                            where vstart + size >= d.low
                              and vstart + size <= d.high + 1)
        and d.low       <> u.vstart
        and d.low       <> u.vstart - 4
        and d.cntrltype  = 0
        and d.name       = @DEVICE
      group by d.low
      having u.vstart    = (select min (vstart)
                            from master.dbo.sysusages
                            where vstart + size >= d.low
                              and vstart + size <= d.high + 1)
         and d.low       <> u.vstart
         and d.low       <> u.vstart - 4
         and d.cntrltype  = 0
         and d.name       = @DEVICE

   insert into #devicespace                             -- free middle
      select ' ',
             ' ',
             '--------FREE--------',
             d.status,
             u1.vstart + u1.size,
             ' ',
             abs (u2.vstart - u1.vstart - u1.size),
             ' ',
             ' ',
             0,
             0,
             0
      from master.dbo.sysdevices d,
           master.dbo.sysusages u1,
           master.dbo.sysusages u2
      where d.low       <= u1.vstart
        and d.high      >= u1.vstart + u1.size - 1
        and u2.vstart    = (select min (u3.vstart)
                            from master.dbo.sysusages u3
                            where u3.vstart > u1.vstart)
        and u2.vstart   <> u1.vstart + u1.size
        and d.low       <= u2.vstart + u2.size
        and d.high      >= u2.vstart + u2.size - 1
        and d.cntrltype  = 0
        and d.name       = @DEVICE

   insert into #devicespace                             -- free high
      select ' ',
             ' ',
             '--------FREE--------',
             d.status,
             u.vstart + u.size,
             ' ',
             abs (d.high - u.vstart - u.size + 1),
             ' ',
             ' ',
             0,
             0,
             0
      from master.dbo.sysdevices d,
           master.dbo.sysusages  u
      where u.vstart    = (select max (vstart)
                           from master.dbo.sysusages
                           where vstart + size >= d.low
                             and vstart + size <= d.high + 1)
        and d.high      <> u.vstart + u.size - 1
        and d.cntrltype  = 0
        and d.name       = @DEVICE
      group by d.low
      having u.vstart     = (select max (vstart)
                             from master.dbo.sysusages
                             where vstart + size >= d.low
                               and vstart + size <= d.high + 1)
         and d.high      <> u.vstart + u.size - 1
         and d.cntrltype  = 0
         and d.name       = @DEVICE

   insert into #devicespace                             -- free device
      select ' ',
             ' ',
             '--------FREE--------',
             d.status,
             d.low,
             ' ',
             abs (d.high - d.low + 1),
             ' ',
             'device',
             0,
             0,
             0
      from master.dbo.sysdevices d
      where d.cntrltype = 0
        and d.name      = @DEVICE
        and (not exists (select u.*
                         from master.dbo.sysusages u
                         where d.low  <= u.vstart
                           and d.high >= u.vstart))

   update #devicespace
   set comment = substring ('mirrored ' + comment, 1, 30)
   where (status & 512) = 512
     and (status & 128) = 128
     and (status & 64)  = 64

   update #devicespace
   set sprint = substring (convert (char (18),
                                    convert (money,
                                             size),
                                    1),
                           2,
                           14)

   select 'DATABASE'       = substring (dbname, 1, 20),
          'FRAGMENT PAGES' = substring (sprint, 1, 14),
          'COMMENT'        = comment
   from #devicespace
   order by firstpage
go
sp_procxmode sp_devicespacedvc, unchained
go

create procedure sp_devicestatus (@DEVICE varchar (30) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_devicestatus> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @capacity  varchar (8),
           @type      smallint

   create table #devstat
      (num        tinyint       not null,
       status     smallint      not null,
       status64   tinyint       not null,
       status128  tinyint       not null,
       status512  tinyint       not null,
       type       smallint      not null,
       devname    varchar (30)  not null,
       dscrp      varchar (60)  not null,
       tpages     int           not null,
       apages     int           not null)

   insert into #devstat
      select convert (tinyint, low / power (2, 24)),
             status,
             (status & 64) / 64,
             (status & 128) / 128,
             (status & 512) / 512,
             cntrltype,
             name,
             ' ',
             high - low + 1,
             0
      from master.dbo.sysdevices

   update #devstat
   set dscrp  = 'Disk dump device',
       tpages = 0
   where type = 2

   update #devstat
   set dscrp  = 'Tape dump device',
       tpages = 16 * (tpages - 1)
   where type > 2
     and type < 9

   update #devstat
   set dscrp  = 'Channel',
       tpages = 0
   where type > 8

   update #devstat
   set dscrp = 'Serial writes'
   where (status & 32) = 32

   update #devstat
   set dscrp = ltrim (dscrp + ' Mirrored')
   where (status64 + status128 + status512) = 3

   update #devstat
   set dscrp = ltrim (dscrp + ' MIRROR PROBLEM!')
   where ((status & 256) = 256)
      or (((status64 + status128 + status512) > 0)  and
          ((status64 * status128 * status512) = 0))

   update #devstat
   set dscrp = ltrim (dscrp + ' Default disk')
   where (status & 1) = 1

   if (@DEVICE IS NULL)
      begin
         select NUM              = num,
                DEVICE           = substring (devname, 1, 19),
                'STATUS SUMMARY' = isnull (substring (dscrp, 1, 41), ' '),
                'TOTAL PAGES'    = substring (convert (char (14),
                                                       convert (money,
                                                                tpages),
                                                       1),
                                              1,
                                              11)
         from #devstat
         order by type, num
      end
   else if (exists (select *
                    from master.dbo.sysdevices
                    where name = @DEVICE))
      begin
         delete from #devstat
         where devname <> @DEVICE

         select @type = type
         from #devstat

         if (@type = 0)
            begin
               update #devstat
               set apages = sum (u.size)
               from master.dbo.sysdevices d,
                    master.dbo.sysusages u
               where d.name  = @DEVICE
                 and d.low  <= u.vstart + u.size
                 and d.high >= u.vstart + u.size - 1

               select DEVICE         = @DEVICE,
                      TYPE           = convert (tinyint, type),
                      NUMBER         = num,
                      'TOTAL PAGES'  = substring (convert (char (14),
                                                           convert (money,
                                                                    tpages),
                                                           1),
                                                  1,
                                                  11),
                      'ALLOC PAGES'  = substring (convert (char (14),
                                                           convert (money,
                                                                    apages),
                                                           1),
                                                  1,
                                                  11)
               from #devstat
            end
         else if ((@type > 2)  and  (@type < 9))
            begin
               select @capacity = convert (varchar (8), tpages / 512000)
               from #devstat

               select @capacity = space (6 - char_length (@capacity))
                                  +  @capacity  +  'Gb'

               select DEVICE   = @DEVICE,
                      TYPE     = convert (tinyint, type),
                      NUMBER   = num,
                      CAPACITY = @capacity
               from #devstat
            end
         else
            begin
               select DEVICE = @DEVICE,
                      TYPE   = convert (tinyint, type),
                      NUMBER = num
               from #devstat
            end

         print ' '

         select STATUS = convert (smallint, vV.number),
                TEXT   = substring (vV.name, 1, 28)
         from master.dbo.sysdevices d,
              sybsystemprocs.dbo.usptvalues vV
         where d.name    = @DEVICE
           and vV.type   = 'V'
           and vV.number = (d.status & vV.number)
         order by vV.number
      end
   else
      begin
         print '    DEVICE <%1!> DOES NOT EXIST.', @DEVICE

         print ' '
      end
go
sp_addcomment sp_devicestatus, 16, 'Lists all statuses for DEVICE or (default) partial stauses for all devices'
go
sp_procxmode sp_devicestatus, anymode
go

create procedure sp_fragments (@DATABASE varchar (30) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_fragments> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   create table #fragments
      (sort1     varchar (30)   not null,            -- db name
       sort2     int            not null,            -- device name
       dbname    varchar (30)   not null,            -- db name or ' '
       devname   varchar (30)   not null,            -- device name or ' '
       status    smallint       not null,            -- device status
       size      int            null,                -- fragment size
       free      int            null,                -- fragment size used
       comment   varchar (21)   not null,            -- mirrored?  data?  log?
       phyname   varchar (127)  null,                -- physical device name
       C         tinyint        null,                -- controller #
       T         tinyint        null,                -- target #
       S         tinyint        null)                -- slice #

   if ((@DATABASE IS NULL)  or  (db_id (@DATABASE) IS NOT NULL))
      begin
         insert into #fragments
            select db_name (u.dbid),
                   u.vstart,
                   db_name (u.dbid),
                   d.name,
                   d.status,
                   u.size,
                   curunreservedpgs (u.dbid, u.lstart, u.unreservedpgs),
                   substring (vS.name, 1, 21),
                   d.phyname,
                   NULL,
                   NULL,
                   NULL
            from master.dbo.sysdevices d,
                 master.dbo.sysusages  u,
                 sybsystemprocs.dbo.usptvalues vS
            where d.cntrltype  = 0
              and u.dbid       = isnull (db_id (@DATABASE), u.dbid)
              and d.low       <= u.vstart + u.size
              and d.high      >= u.vstart + u.size - 1
              and vS.number    = (u.segmap & 7)
              and vS.type      = 'S'

         update #fragments
         set comment = substring ('mirrored ' + comment, 1, 21)
         where (status & 512) = 512

         update #fragments
         set phyname = NULL,
             C       = 0,
             T       = 0,
             S       = 0
         where phyname = 'd_master'

         while (exists (select *
                        from #fragments
                        where charindex ('/', phyname) > 0))
            begin
               update #fragments
               set phyname = substring (phyname, charindex ('/', phyname) + 1, 73)
               where charindex ('/', phyname) > 0
            end

         update #fragments
         set C = convert (tinyint,
                          substring (phyname,
                                     charindex ('c', phyname) + 1,
                                     charindex ('t', phyname) - charindex ('c', phyname) - 1))
         where charindex ('c', phyname) > 0
           and charindex ('t', phyname) > 0

         update #fragments
         set T = convert (tinyint,
                          substring (phyname,
                                     charindex ('t', phyname) + 1,
                                     charindex ('d', phyname) - charindex ('t', phyname) - 1))
         where charindex ('t', phyname) > 0
           and charindex ('d', phyname) > 0

         update #fragments
         set S = convert (tinyint,
                          substring (phyname,
                                     charindex ('s', phyname) + 1,
                                     3))
         where charindex ('s', phyname) > 0

         if (@DATABASE IS NULL)
            begin
               update #fragments
               set dbname  = ' ',
                   devname = ' '
               from #fragments f
               where exists (select *
                             from #fragments f2
                             where f2.sort1 = f.sort1
                               and f2.C     = f.C
                               and f2.T     = f.T
                               and f2.S     = f.S
                               and f2.sort2 = f.sort2
                               and f2.sort2 > (select min (f3.sort2)
                                               from #fragments f3
                                               where f3.sort1 = f2.sort1
                                                 and f3.C     = f2.C
                                                 and f3.T     = f2.T
                                                 and f3.S     = f2.S))

               update #fragments
               set dbname = ' '
               from #fragments f
               where exists (select *
                             from #fragments f2
                             where f2.sort1 = f.sort1
                               and f2.C     = f.C
                               and f2.T     = f.T
                               and f2.S     = f.S
                               and ((32767 * f2.C) + (255 * f2.T) + f2.S)
                                            > (select min ((32767 * f3.C) + (255 * f3.T) + f3.S)
                                               from #fragments f3
                                               where f3.sort1 = f2.sort1))

               insert into #fragments
                  select min (sort1), 0, ' ', ' ', 0, NULL, NULL, ' ', ' ', NULL, NULL, NULL
                  from #fragments
                  where dbname <> ' '
                  group by dbname
                  having dbname <> ' '

               delete from #fragments
               where dbname  = ' '
                 and C      IS NULL
                 and sort1   = (select min (sort1)
                                from #fragments)

               select 'DATABASE'       = substring (dbname, 1, 17),
                      'DEVICE'         = substring (devname, 1, 11),
                      'FRAGMENT PAGES' = isnull (substring (convert (char (18),
                                                                     convert (money,
                                                                              size),
                                                                     1),
                                                            2,
                                                            14),
                                                 ' '),
                      'USED PAGES'     = isnull (substring (convert (char (14),
                                                                     convert (money,
                                                                              size - free),
                                                                     1),
                                                            2,
                                                            10),
                                                 ' '),
                      USAGE            = comment
               from #fragments
               order by sort1, C, T, S, sort2
            end
         else
            begin
               update #fragments
               set devname = ' '
               from #fragments f
               where exists (select *
                             from #fragments f2
                             where f2.phyname = f.phyname
                               and f2.sort2   = f.sort2
                               and f2.sort2   > (select min (f3.sort2)
                                                 from #fragments f3
                                                 where f3.phyname = f2.phyname))

               select DEVICE           = substring (devname, 1, 16),
                      'FRAGMENT PAGES' = substring (convert (char (18),
                                                             convert (money,
                                                                      size),
                                                             1),
                                                    2,
                                                    14),
                      'USED PAGES    ' = substring (convert (char (18),
                                                             convert (money,
                                                                      size - free),
                                                             1),
                                                    2,
                                                    14),
                      USAGE            = comment
               from #fragments
               order by sort1, C, T, S, sort2
            end
      end
   else
      begin
         print '    DATABASE <%1!> DOES NOT EXIST.', @DATABASE

         print ' '
      end
go
sp_addcomment sp_fragments, 11, 'Lists device fragments for DATABASE or (default) all databases'
go
sp_procxmode sp_fragments, anymode
go

create procedure sp_freedevice (@DEVICE  varchar (30) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_freedevice> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   create table #freedevice
     (phyname  varchar (127)  null,
      device   varchar (30)   not null,
      fpage    int            not null,
      size     int            not null,
      comment  varchar (13)   not null,
      C        tinyint        null,
      T        tinyint        null,
      S        tinyint        null)

   insert into #freedevice
      select d.phyname,                                 -- low device frags
             d.name,
             d.low,
             u.vstart - d.low,
             '',
             NULL,
             NULL,
             NULL
      from master.dbo.sysdevices d,
           master.dbo.sysusages  u
      where u.vstart     = (select min (vstart)
                            from master.dbo.sysusages
                            where vstart + size >= d.low
                              and vstart + size <= d.high + 1)
        and d.low       <> u.vstart
        and d.low       <> u.vstart - 4
        and d.cntrltype  = 0
      group by d.low
      having u.vstart    = (select min (vstart)
                            from master.dbo.sysusages
                            where vstart + size >= d.low
                              and vstart + size <= d.high + 1)
         and d.low       <> u.vstart
         and d.low       <> u.vstart - 4
         and d.cntrltype  = 0

   insert into #freedevice
      select d.phyname,                                  -- middle frags
             d.name,
             u1.vstart + u1.size,
             u2.vstart - u1.vstart - u1.size,
             '',
             NULL,
             NULL,
             NULL
      from master.dbo.sysdevices d,
           master.dbo.sysusages u1,
           master.dbo.sysusages u2
      where d.low       <= u1.vstart
        and d.high      >= u1.vstart + u1.size - 1
        and u2.vstart    = (select min (u3.vstart)
                            from master.dbo.sysusages u3
                            where u3.vstart > u1.vstart)
        and u2.vstart   <> u1.vstart + u1.size
        and d.low       <= u2.vstart + u2.size
        and d.high      >= u2.vstart + u2.size - 1
        and d.cntrltype  = 0

   insert into #freedevice
      select d.phyname,                                  -- high device frags
             d.name,
             u.vstart + u.size,
             d.high - u.vstart - u.size + 1,
             '',
             NULL,
             NULL,
             NULL
      from master.dbo.sysdevices d,
           master.dbo.sysusages  u
      where u.vstart    = (select max (vstart)
                           from master.dbo.sysusages
                           where vstart + size >= d.low
                             and vstart + size <= d.high + 1)
        and d.high      <> u.vstart + u.size - 1
        and d.cntrltype  = 0
      group by d.low
      having u.vstart     = (select max (vstart)
                             from master.dbo.sysusages
                             where vstart + size >= d.low
                               and vstart + size <= d.high + 1)
         and d.high      <> u.vstart + u.size - 1
         and d.cntrltype  = 0

   insert into #freedevice
      select d.phyname,                                  -- unused devices
             d.name,
             low,
             high - low + 1,
             'Entire Device',
             NULL,
             NULL,
             NULL
      from master.dbo.sysdevices d
      where d.cntrltype = 0
        and (not exists (select u.*
                         from master.dbo.sysusages u
                         where d.low <= u.vstart
                           and d.high > u.vstart))

   if (@DEVICE IS NULL)
      begin
         update #freedevice
         set phyname = NULL
         where phyname = 'd_master'

         while (exists (select *
                        from #freedevice
                        where charindex ('/', phyname) > 0))
            begin
               update #freedevice
               set phyname = substring (phyname, charindex ('/', phyname) + 1, 73)
               where charindex ('/', phyname) > 0
            end

         update #freedevice
         set C = convert (tinyint,
                          substring (phyname,
                                     charindex ('c', phyname) + 1,
                                     charindex ('t', phyname) - charindex ('c', phyname) - 1))
         where charindex ('c', phyname) > 0
           and charindex ('t', phyname) > 0

         update #freedevice
         set T = convert (tinyint,
                          substring (phyname,
                                     charindex ('t', phyname) + 1,
                                     charindex ('d', phyname) - charindex ('t', phyname) - 1))
         where charindex ('t', phyname) > 0
           and charindex ('d', phyname) > 0

         update #freedevice
         set S = convert (tinyint,
                          substring (phyname,
                                     charindex ('s', phyname) + 1,
                                     3))
         where charindex ('s', phyname) > 0

         if (exists (select *
                     from #freedevice
                     where comment = 'Entire Device'))
            begin
               select DEVICE  = substring (device, 1, 18),
                      'PAGES' = substring (convert (char (15),
                                                    convert (money,
                                                             size),
                                                    1),
                                           2,
                                           11),
                      COMMENT = comment
               from #freedevice
               order by C, T, S, fpage
            end
         else
            begin
               select DEVICE  = substring (device, 1, 18),
                      'PAGES' = substring (convert (char (15),
                                                    convert (money,
                                                             size),
                                                    1),
                                           2,
                                           11)
               from #freedevice
               order by C, T, S, fpage
            end
      end
   else if (exists (select *
                    from #freedevice
                    where device = @DEVICE))
      begin
         if (exists (select *
                     from #freedevice
                     where device  = @DEVICE
                       and comment = 'Entire Device'))
            begin
               select DEVICE  = substring (device, 1, 18),
                      'PAGES' = substring (convert (char (15),
                                                    convert (money,
                                                             size),
                                                    1),
                                           2,
                                           11),
                      COMMENT = comment
               from #freedevice
               where device = @DEVICE
            end
         else
            begin
               select DEVICE  = substring (device, 1, 18),
                      'PAGES' = substring (convert (char (15),
                                                    convert (money,
                                                             size),
                                                    1),
                                           2,
                                           11)
               from #freedevice
               where device = @DEVICE
               order by fpage
            end
      end
   else if (exists (select *
                    from master.dbo.sysdevices
                    where name = @DEVICE))
      begin
         print '    DEVICE <%1!> HAS NO FREE SPACE.', @DEVICE
      end
   else
      begin
         print '    DEVICE <%1!> DOES NOT EXIST.', @DEVICE
      end
go
sp_addcomment sp_freedevice, 16, 'Lists free space on DEVICE or (default) all database devices'
go
sp_procxmode sp_freedevice, anymode
go

sp_addcomment sp_users, 11, 'Lists logins/users with various scope/sort options'
go
sp_addcomment sp_users, 11, '   (NULL):     all users in name order (default)'
go
sp_addcomment sp_users, 11, '   "ACCOUNT":  all logins in account date order'
go
sp_addcomment sp_users, 11, '   "ALIAS":    all users with an alias or pseudonym'
go
sp_addcomment sp_users, 11, '   "NAME":     all logins in name order'
go
sp_addcomment sp_users, 11, '   PREFIX:     all logins/users like PREFIX% in login/user name order'
go
sp_addcomment sp_users, 11, '   "STATUS":   all logins with locked/expired/bad accounts'
go
sp_addcomment sp_users, 11, '   "USAGE":    all logins in CPU and I/O usage order'
go
sp_procxmode sp_users, anymode
go

dump transaction sybsystemprocs with truncate_only
go
