use sybsystemprocs
go

sp_dropprocedure sp_device
go
sp_dropprocedure sp_device_format
go
sp_dropprocedure sp_deviceright
go
sp_dropprocedure sp_deviceright_format
go
sp_dropprocedure sp_devicespaceagg
go
sp_dropprocedure sp_devicespaceall
go
sp_dropprocedure sp_devicespacedvc
go
sp_dropprocedure sp_devicespace
go
sp_dropprocedure sp_fragments
go
sp_dropprocedure sp_freedevice
go
sp_dropprocedure sp_set
go

set quoted_identifier on
go

------------ Temporary table needed for creation of sp_device_format ---------

create table #device
  (cntrltype   smallint          not null,
   devnum      char (3)          not null,
   devname     varchar ( 30)     not null,
   phyname     varchar ( 73)     not null,
   mirrorname  varchar ( 36)     null,
   dscrp       varchar (255)     not null,
   sort1       varchar ( 73)     not null,
   sort2       tinyint           not null,
   status      smallint          not null,
   tapesz      double precision  null,
   capacity    varchar ( 10)     null,
   C           smallint          null,
   T           smallint          null,
   D           smallint          null,
   S           smallint          null)
go

create procedure sp_device_format (@DSCRPLEN  tinyint,
                                   @SORT2     tinyint,
                                   @NEW_ROWS  tinyint  output)  as

   set ansinull                      off              -- Non-ANSI
   set nocount                       on
   set string_rtruncation            on

   insert into #device
      select cntrltype,
             ' ',
             ' ',
             ' ',
             ' ',
             ltrim (substring (dscrp,
                               2 + @DSCRPLEN - charindex (',',
                                                          reverse (substring (dscrp,
                                                                              1,
                                                                              @DSCRPLEN))),
                               255)),
             sort1,
             1 + @SORT2,
             status,
             tapesz,
             capacity,
             C,
             T,
             D,
             S
      from #device
      where dscrp                  IS NOT NULL
        and char_length (dscrp)     > @DSCRPLEN
        and charindex (',', dscrp)  > 0
        and sort2                   = @SORT2

   update #device
   set dscrp = substring (dscrp,
                          1,
                          1 + @DSCRPLEN - charindex (',',
                                                     reverse (substring (dscrp,
                                                                         1,
                                                                         @DSCRPLEN))))
   where sort2                  = @SORT2
     and char_length (dscrp)    > @DSCRPLEN
     and charindex (',', dscrp) > 0

   select @NEW_ROWS = count (*)
   from #device
   where sort2 = 1 + @SORT2
go
sp_existsprocedure sp_device_format
go

drop table #device
go

create procedure sp_device (@DEVICE varchar (30) = NULL) as
   set ansinull                      off              -- Non-ANSI
   set nocount                       on
   set string_rtruncation            on

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

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

         print ' '

         return 1
      end

   if (exists (select *
               from master.dbo.syslocks
               where dbid = 1
                 and id   = object_id ('sysdevices')
                 and ((spid = 1)  or  (spid > 7))))
      begin
         --
         --  NOTE THAT WE DO NOT CHECK AS TO WHAT KIND OF LOCK TYPES EXIST.
         --
         print ' '

         print '    THERE ARE LOCKS ON sysdevices.'

         print ' '

         print '    TRANSACTION ISOLATION LEVEL 0 WILL BE USED.'

         print ' '
      end
   else
      begin
         set transaction isolation level 1

         print ' '
      end

   declare  @cmdstring   varchar (255),
            @blocksize   double precision,
            @devlen      tinyint,
            @dscrplen    tinyint,
            @i           int,
            @likestring  varchar ( 30),
            @kb          double precision,
            @mb          double precision,
            @mirrlen     tinyint,
            @newrows     tinyint,
            @phylen      tinyint

   create table #device
     (cntrltype   smallint          not null,
      devnum      char (3)          not null,
      devname     varchar ( 30)     not null,
      phyname     varchar ( 73)     not null,
      mirrorname  varchar ( 36)     null,
      dscrp       varchar (255)     not null,
      sort1       varchar ( 73)     not null,
      sort2       tinyint           not null,
      status      smallint          not null,
      tapesz      double precision  null,
      capacity    varchar ( 10)     null,
      C           smallint          null,
      T           smallint          null,
      D           smallint          null,
      S           smallint          null)

   insert into #device
      select cntrltype,
             convert (char (3), convert (tinyint, low / power (2, 24))),
             name,
             substring (phyname, 1, 72),
             substring (mirrorname, 1, 36),
             ' ',
             substring (phyname, 1, 72),
             0,
             status,
             high - low + 1,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL
      from master.dbo.sysdevices
      where name = isnull (@DEVICE, name)

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

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

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

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

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

         update #device
         set D = convert (tinyint,
                          substring (sort1,
                                     charindex ('d', sort1) + 1,
                                     charindex ('s', sort1) - charindex ('d', sort1) - 1))
         where cntrltype               = 0
           and sort1                  <> 'd_master'
           and charindex ('d', sort1)  > 0
           and charindex ('s', sort1)  > 0

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

         update #device
         set sort1 = '0'
         where cntrltype <> 0
            or phyname    = 'd_master'

         update #device
         set dscrp = '   Channel'
         where cntrltype > 8

         select @i = 1

         while (@i < 16385)
            begin
               update #device
               set dscrp = substring (dscrp  +  ', '  +  vV.name, 1, 255)
               from #device,
                    sybsystemprocs.dbo.usptvalues vV
               where vV.type               = 'V'
                 and vV.number             = @i
                 and (#device.status & @i) = @i

               select @i = 2 * @i
            end

         update #device
         set dscrp = substring (dscrp, 4, 255)
         where substring (dscrp, 1, 3) = ' , '

         update #device
         set dscrp  = 'Disk dump device',
             tapesz = 0
         where cntrltype = 2

         if (exists (select *
                     from #device
                     where cntrltype between 3 and 9))
             begin
                select @blocksize = vE4.number
                from sybsystemprocs.dbo.usptvalues vE4
                where vE4.type = 'E4'

                select @blocksize = isnull (@blocksize, 32768)

                update #device
                set tapesz = tapesz - 1
                where cntrltype between 3 and 9

                select @kb = 1024,
                       @mb = 1048576

                update #device
                set tapesz = round ((@blocksize * tapesz) / @mb, 0),
                    dscrp = ' KB Tape dump device'
                where cntrltype between 3 and 9

                update #device
                set tapesz = tapesz / @kb,
                    dscrp = ' GB Tape dump device'
                where cntrltype between 3 and 9
                  and tapesz >= @kb

                update #device
                set capacity = ltrim (convert (varchar (8), str (tapesz, 8, 0)))
                where cntrltype between 3 and 9

                update #device
                set dscrp = capacity  +  dscrp
                where cntrltype between 3 and 9
             end

         select @devlen   = max (char_length (devname)),
                @phylen   = max (char_length (phyname)),
                @mirrlen  = coalesce (max (char_length (mirrorname)), 1)
         from #device

         select @dscrplen = 70 - @devlen - @phylen - @mirrlen,
                @i        = 0,
                @newrows  = 1

         while (@newrows > 0)
            begin
               execute  sp_device_format  @DSCRPLEN=@dscrplen,
                                          @SORT2=@i,
                                          @NEW_ROWS=@newrows  output

               select @i = @i + 1
            end

         select @cmdstring = 'select NUM = devnum,'
                             +  'DEVICE = substring (devname, 1,'
                             +  convert (varchar (2), @devlen)
                             +  '),'
                             +  'PHYSICAL = substring (phyname, 1,'
                             +  convert (varchar (3), @phylen)
                             +  '),'

         if (exists (select *
                     from #device
                     where mirrorname IS NOT NULL))
            begin
               select @cmdstring = @cmdstring
                                   +  '''MIRROR'' = isnull (substring (mirrorname, 1,'
                                   +  convert (varchar (3), @mirrlen)
                                   +  '), '' ''),'
            end
         else if (not exists (select *
                              from #device
                              where devnum = ' '))
            begin
               select @dscrplen = max (char_length (dscrp))
               from #device
            end

         select @cmdstring = @cmdstring
                             +  '''STATUS'' = substring (dscrp, 1,'
                             +  convert (varchar (3), @dscrplen)
                             +  ')'
                             +  ' from #device'
                             +  ' order by cntrltype, C, T, D, S, sort1, sort2, devname'

         execute  (@cmdstring)

         print ' '
      end
   else
      begin
         print '    DEVICE %1! DOES NOT EXIST.', @DEVICE

         print ' '

         execute  sp_like_string  @INPUT_STRING=@DEVICE,
                                  @LIKE_STRING=@likestring  output

         if (exists (select *
                     from master.dbo.sysdevices
                     where name like @likestring))
            begin
               print '    DEVICES LIKE %1! EXIST.', @likestring

               print ' '

               select 'DEVICE NAME' = name
               from master.dbo.sysdevices
               where name like @likestring
               order by name
            end
      end
go
sp_addcomment sp_device, 'Lists UNIX & status info for DEVICE or (default) all devices'
go
sp_procxmode sp_device, anymode
go


------------ Temporary table needed for creation of sp_deviceright_format ----

create table #mrdvstatus
   (sybname     varchar ( 30)  not null,
    phyname     varchar (127)  null,
    mirrorname  varchar (127)  null,
    dscrp       varchar (255)  not null,
    C           smallint       not null,
    T           smallint       not null,
    D           smallint       not null,
    S           smallint       not null,
    sort        tinyint        not null)
go

create procedure sp_deviceright_format (@STATUSLEN  tinyint,
                                        @SORT       tinyint,
                                        @NEW_ROWS   tinyint  output)  as

   set ansinull                      off              -- Non-ANSI
   set nocount                       on
   set string_rtruncation            on

   insert into #mrdvstatus
      select ' ', ' ', ' ',
             ltrim (substring (dscrp,
                               2 + @STATUSLEN - charindex (',',
                                                           reverse (substring (dscrp,
                                                                               1,
                                                                               @STATUSLEN))),
                               255)),
             C, T, D, S, 1 + @SORT
      from #mrdvstatus
      where dscrp                  IS NOT NULL
        and char_length (dscrp)     > @STATUSLEN
        and charindex (',', dscrp)  > 0
        and sort                    = @SORT

   update #mrdvstatus
   set dscrp = substring (dscrp,
                          1,
                          1 + @STATUSLEN - charindex (',',
                                                     reverse (substring (dscrp,
                                                                         1,
                                                                         @STATUSLEN))))
   where sort                   = @SORT
     and char_length (dscrp)    > @STATUSLEN
     and charindex (',', dscrp) > 0

   select @NEW_ROWS = count (*)
   from #mrdvstatus
   where sort = 1 + @SORT
go
sp_existsprocedure sp_deviceright_format
go

drop table #mrdvstatus
go

create procedure sp_deviceright as
   set ansinull                      off              -- Non-ANSI
   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_deviceright CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring1  varchar (255),
           @cmdstring2  varchar (255),
           @dblen       tinyint,
           @i           int,
           @maxvdevno   varchar (  3),
           @minsort1    varchar ( 30),
           @minsort2    int,
           @mirrlen     tinyint,
           @newrows     tinyint,
           @phylen      tinyint,
           @statuslen   tinyint,
           @syblen      tinyint

   create table #unuseddvc
      (vdevno      tinyint        not null)

   create table #mrdv
      (low         int            not null,
       high        int            not null,
       sybname     varchar ( 30)  not null,
       status      smallint       not null,
       dscrp       varchar (255)  not null,
       phyname     varchar (127)  null,
       C           smallint       not null,
       T           smallint       not null,
       D           smallint       not null,
       S           smallint       not null,
       mirrorname  varchar (127)  null,
       MC          smallint       not null,
       MT          smallint       not null,
       MD          smallint       not null,
       MS          smallint       not null)

   create table #mrdvstatus
      (sybname     varchar ( 30)  not null,
       phyname     varchar (127)  null,
       mirrorname  varchar (127)  null,
       dscrp       varchar (255)  not null,
       C           smallint       not null,
       T           smallint       not null,
       D           smallint       not null,
       S           smallint       not null,
       sort        tinyint        not null)

   create table #mru
      (dbid        smallint       not null,
       segmap      int            not null,
       size        int            not null,
       vstart      int            not null,
       dbname      varchar (30)   not null,
       usage       char (12)      not null,
       pages       char (14)      not null)


   -- ANALYZE DEVICE NUMBERS --------------------------------------------

   insert into #unuseddvc
      select number
      from sybsystemprocs.dbo.usptvalues
      where type    = 'P'
        and number >= 0
        and number <= (select max (convert (tinyint, low / power (2, 24)))
                       from master.dbo.sysdevices)

   select @maxvdevno = convert (varchar (3), max (vdevno))
   from #unuseddvc

   delete #unuseddvc
   from #unuseddvc u
   where exists (select d.low
                 from master.dbo.sysdevices d
                 where convert (tinyint, d.low / power (2, 24)) = u.vdevno)

   if (exists (select *
               from #unuseddvc))
      begin
         print '    THE HIGHEST VIRTUAL DEVICE NUMBER IS %1! AND THERE ARE GAPS.',
               @maxvdevno

         print ' '

         select 'UNUSED DEVICE NUMBER' = vdevno
         from #unuseddvc
         order by vdevno
      end
   else
      begin
         print '    THE HIGHEST VIRTUAL DEVICE NUMBER IS %1! AND THERE ARE NO GAPS.',
               @maxvdevno
      end

   print ' '

    -- SET UP TEMP TABLE WITH C/T/D/S VALUES AND STATUS TEXT ------------

   insert into #mrdv
      select low, high, name, status, ' ', phyname, -1, -1, -1, -1, mirrorname, -1, -1, -1, -1
      from master.dbo.sysdevices
      where cntrltype = 0

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

   while (exists (select *
                  from #mrdv
                  where charindex ('/', mirrorname) > 0))
      begin
         update #mrdv
         set mirrorname = substring (mirrorname, charindex ('/', mirrorname) + 1, 127)
         where charindex ('/', mirrorname) > 0
      end

   update #mrdv
   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 #mrdv
   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
     and phyname                  <> 'd_master'

   update #mrdv
   set D = convert (tinyint,
                    substring (phyname,
                               charindex ('d', phyname) + 1,
                               charindex ('s', phyname) - charindex ('d', phyname) - 1))
   where charindex ('d', phyname)  > 0
     and charindex ('s', phyname)  > 0
     and phyname                  <> 'd_master'

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

   update #mrdv
   set MC = convert (tinyint,
                     substring (mirrorname,
                                charindex ('c', mirrorname) + 1,
                                charindex ('t', mirrorname) - charindex ('c', mirrorname) - 1))
   where charindex ('c', mirrorname)  > 0
     and charindex ('t', mirrorname)  > 0
     and mirrorname                  IS NOT NULL

   update #mrdv
   set MT = convert (tinyint,
                     substring (mirrorname,
                                charindex ('t', mirrorname) + 1,
                                charindex ('d', mirrorname) - charindex ('t', mirrorname) - 1))
   where charindex ('t', mirrorname)  > 0
     and charindex ('d', mirrorname)  > 0
     and mirrorname                  <> 'd_master'
     and mirrorname                  IS NOT NULL

   update #mrdv
   set MD = convert (tinyint,
                     substring (mirrorname,
                                charindex ('d', mirrorname) + 1,
                                charindex ('s', mirrorname) - charindex ('d', mirrorname) - 1))
   where charindex ('d', mirrorname)  > 0
     and charindex ('s', mirrorname)  > 0
     and mirrorname                  <> 'd_master'
     and mirrorname                  IS NOT NULL

   update #mrdv
   set MS = convert (tinyint, substring (mirrorname, charindex ('s', mirrorname) + 1, 1))
   where charindex ('s', mirrorname)  > 0
     and mirrorname                  <> 'd_master'
     and mirrorname                  IS NOT NULL

   select @i = 1

   while (@i < 16385)
      begin
         update #mrdv
         set dscrp = substring (dscrp  +  ', '  +  vV.name, 1, 255)
         from #mrdv,
              sybsystemprocs.dbo.usptvalues vV
         where vV.type             = 'V'
           and vV.number           = @i
           and (#mrdv.status & @i) = @i

         select @i = 2 * @i
      end

   -- ANALYZE MIRROR USAGE AND STATUSES ----------------------------------

   if (exists (select *
               from #mrdv
               where mirrorname IS NOT NULL))
      begin
         if (exists (select *
                     from #mrdv
                     where C = MC
                       and phyname <> 'd_master'))
            begin
               print '    THERE ARE MIRRORED DEVICES ON THE SAME CONTROLLER, TARGET, AND/OR DISK.'

               print ' '

               select @syblen  = max (char_length (sybname)),
                      @phylen  = max (char_length (phyname)),
                      @mirrlen = max (char_length (mirrorname))
               from #mrdv
               where C = MC

               select @cmdstring1 = 'select ''DEVICE'' = substring (sybname, 1,'
                                   +  convert (varchar (2), @syblen)
                                   +  '),'
                                   +  'PHYSICAL = isnull (substring (phyname, 1,'
                                   +  convert (varchar (3), @phylen)
                                   +  '), ''-''),'
                                   +  '''MIRROR'' = isnull (substring (mirrorname, 1,'
                                   +  convert (varchar (3), @mirrlen)
                                   +  '), ''-'')'
                                   +  ' from #mrdv'
                                   +  ' where C = MC'
                                   +  ' and phyname <> ''d_master'''
                                   +  ' order by C, T, D, S'

               execute  (@cmdstring1)
            end
         else
            begin
               print '    ALL MIRRORED DEVICES ARE ON DIFFERENT CONTROLLERS FROM THE PRIMARY DEVICE.'
            end

         print ' '

         insert into #mrdvstatus
            select sybname, phyname, mirrorname, substring (dscrp, 4, 255),
                   C, T, D, S, 0
            from #mrdv
            where status         between 64 and 16383
              and (status & 512)      <> 512

         insert into #mrdvstatus
            select sybname, phyname, mirrorname, substring (dscrp, 4, 255),
                   C, T, D, S, 0
            from #mrdv
            where status < 64
              and ((status > 3)  or  (mirrorname IS NOT NULL))

         if (exists (select *
                     from #mrdvstatus))
            begin
               print '    AT LEAST ONE DEVICE HAS AN INCORRECT STATUS.'

               print ' '

               select @syblen  = max (char_length (sybname)),
                      @phylen  = max (char_length (phyname)),
                      @mirrlen = coalesce (max (char_length (mirrorname)), 1)
               from #mrdvstatus

               select @statuslen = 74 - @syblen - @phylen - @mirrlen,
                      @i         = 0,
                      @newrows   = 1

               while (@newrows > 0)
                  begin
                     execute  sp_deviceright_format  @STATUSLEN=@statuslen,
                                                     @SORT=@i,
                                                     @NEW_ROWS=@newrows  output

                     select @i = @i + 1
                  end

               select @cmdstring1 = 'select ''DEVICE'' = substring (sybname, 1,'
                                    +  convert (varchar (2), @syblen)
                                    +  '),'
                                    +  'PHYSICAL = substring (phyname, 1,'
                                    +  convert (varchar (2), @phylen)
                                    +  '),'

               select @cmdstring2 = '''MIRROR'' = isnull (substring (mirrorname, 1,'
                                    +  convert (varchar (2), @mirrlen)
                                    +  '), ''-''),'
                                    +  'STATUS = substring (dscrp, 1,'
                                    +  convert (varchar (3), @statuslen)
                                    +  ')'
                                    +  ' from #mrdvstatus'
                                    +  ' order by C, T, D, S, sort'

               execute  (@cmdstring1  +  @cmdstring2)

               drop table #mrdvstatus
            end
         else
            begin
               print '    ALL DEVICES ARE MIRRORED/UNMIRRORED CONSISTENTLY.'
            end
      end
   else
      begin
         print '    THERE ARE NO MIRRORED DEVICES.'
      end

   print ' '

   insert into #mru
      select u.dbid,
             u.segmap,
             u.size,
             u.vstart,
             db_name (u.dbid),
             substring (vS.name, 1, 12),
             substring (convert (char (17), convert (money, u.size), 1), 1, 14)
      from master.dbo.sysusages u,
           sybsystemprocs.dbo.usptvalues vS
      where vS.type   = 'S'
        and vS.number = (u.segmap & 7)
        and 1         < (select count (distinct (dv2.status & 32766))  -- IGNORE "default"
                         from master.dbo.sysdevices dv2,
                              master.dbo.sysusages  u2
                         where dv2.cntrltype    = 0
                           and u2.dbid          = u.dbid
                           and (u2.segmap & 7)  = (u.segmap & 7)
                           and dv2.low         <= u2.vstart + u2.size
                           and dv2.high        >= u2.vstart + u2.size -1)

   select distinct #mru.dbname,
                   #mru.usage,
                   #mrdv.sybname,
                   dscrp          = substring (#mrdv.dscrp, 4, 255),
                   #mru.pages,
                   sort1          = #mru.dbname,
                   sort2          = #mru.vstart
   into #drunlike
   from #mru,
        #mrdv
   where #mrdv.low  <= #mru.vstart + #mru.size
     and #mrdv.high >= #mru.vstart + #mru.size - 1

   if (exists (select *
               from #drunlike))
      begin
         print '    AT LEAST ONE DATABASE/LOG IS SPLIT ON UNLIKE DEVICES.'

         print ' '

         insert into #drunlike
            select ' ', ' ', ' ', ' ', ' ', sort1, min (sort2)
            from #drunlike
            group by sort1

         select @minsort1 = min (sort1)
         from #drunlike

         select @minsort2 = min (sort2)
         from #drunlike
         where sort1 = @minsort1

         delete from #drunlike
         where dbname  = ' '
           and sybname = ' '
           and sort1   = @minsort1
           and sort2   = @minsort2

         update #drunlike
         set dbname  = ' '
         from #drunlike dr1
         where exists (select *
                       from #drunlike dr2
                       where dr2.sort1  = dr1.sort1
                         and dr2.sort2  = dr1.sort2
                         and dr2.sort2  > (select min (dr3.sort2)
                                           from #drunlike dr3
                                           where dr3.sort1 = dr2.sort1))

         select @dblen  = max (char_length (dbname)),
                @syblen = max (char_length (sybname))
         from #drunlike

         if (@dblen < 8)
            begin
               select @dblen = 8
            end

         if (@syblen < 6)
            begin
               select @syblen = 6
            end

         select @statuslen = 47 - @dblen - @syblen

         select @cmdstring1 = 'select distinct ''DATABASE'' = substring (dbname, 1,'
                             +  convert (varchar (2), @dblen)
                             +  '),'
                             +  'USAGE = usage,'
                             +  '''DEVICE'' = substring (sybname, 1,'
                             +  convert (varchar (3), @syblen)
                             +  '),'
                             +  '''FRAGMENT PAGES'' = pages,'
                             +  'STATUS = substring (dscrp, 1,'
                             +  convert (varchar (3), @statuslen)
                             +  ')'
                             +  ' from #drunlike'
                             +  ' order by sort1, sort2'

         execute  (@cmdstring1)
      end
   else
      begin
         print '    NO DATABASE/LOG IS SPLIT ON UNLIKE DEVICES.'
      end

   print ' '

   -- ANALYZE DATABASE DATA AND LOG USAGE --------------------------------

   truncate table #mru

   insert into #mru
      select u.dbid,
             (u.segmap & 7),
             u.size,
             u.vstart,
             db_name (u.dbid),
             substring (vS.name, 1, 12),
             substring (convert (char (17), convert (money, u.size), 1), 1, 14)
      from master.dbo.sysusages u,
           sybsystemprocs.dbo.usptvalues vS
      where vS.type   = 'S'
        and vS.number = (u.segmap & 7)

   update #mru
   set segmap = 0
   where segmap in (1, 2, 3, 5, 6)

   select distinct u.dbname,
                   dv.sybname,
                   dv.phyname,
                   u.vstart,
                   u.usage,
                   u.pages,
                   sort = u.dbname  +  ' '  +  dv.sybname
   into #dr
   from #mru  u,
        #mrdv dv
   where dv.low  <= u.vstart + u.size
     and dv.high >= u.vstart + u.size - 1
     and exists (select u2.dbid
                 from #mru  u2,
                      #mrdv dv2
                 where u2.dbid    = u.dbid
                   and dv2.low   <= u2.vstart + u2.size
                   and dv2.high  >= u2.vstart + u2.size - 1
                   and dv2.C      = dv.C
                   and dv2.T      = dv.T
                   and dv2.D      = dv.D
                   and u2.segmap <> u.segmap)

   if (exists (select *
               from #dr))
      begin
         print '    THERE ARE "SEPARATED" DATABASES/LOGS ON THE SAME DISK.'

         print ' '

         update #dr
         set dbname  = ' ',
             sybname = ' ',
             phyname = ' '
         from #dr dr1
         where exists (select *
                       from #dr dr2
                       where dr2.sort   = dr1.sort
                         and dr2.vstart = dr1.vstart
                         and dr2.vstart > (select min (dr3.vstart)
                                           from #dr dr3
                                           where dr3.sort = dr2.sort))

         update #dr
         set dbname = ' '
         from #dr dr1
         where dr1.dbname <> ' '
           and exists (select *
                       from #dr dr2
                       where dr2.dbname  = dr1.dbname
                         and dr2.sybname = dr1.sybname
                         and dr2.sybname > (select min (dr3.sybname)
                                            from #dr dr3
                                            where dr3.dbname = dr2.dbname))

         insert into #dr
            select ' ', ' ', ' ', 0, ' ', ' ', min (sort)
            from #dr
            where dbname <> ' '
            group by sort
            having dbname <> ' '

         delete from #dr
         where dbname  = ' '
           and sybname = ' '
           and vstart  = 0
           and sort    = (select min (sort)
                          from #dr)

         select @dblen  = max (char_length (dbname)),
                @syblen = max (char_length (sybname)),
                @phylen = max (char_length (phyname))
         from #dr

         select @cmdstring1 = 'select ''DATABASE'' = substring (dbname, 1,'
                             +  convert (varchar (2), @dblen)
                             +  '),'
                             +  '''SYBASE DEVICE'' = substring (sybname, 1,'
                             +  convert (varchar (2), @syblen)
                             +  '),'
                             +  '''UNIX DEVICE'' = substring (phyname, 1,'
                             +  convert (varchar (3), @phylen)
                             +  '),'
                             +  'USAGE = usage,'
                             +  '''FRAGMENT PAGES'' = pages'
                             +  ' from #dr'
                             +  ' order by sort, vstart'

         execute  (@cmdstring1)
      end
   else
      begin
         print '    ALL "SEPARATED" DATABASES/LOGS ARE ON DIFFERENT DISKS.'
      end

   print ' '
go
sp_addcomment sp_deviceright, 'Checks for device (mirror & database/log) inconsistencies'
go
sp_procxmode sp_deviceright, anymode
go

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

create table #devicespace
  (unixname   varchar (127)  default ' '  not null,
   devname    varchar (30)   default ' '  not null,
   dbname     varchar (30)   default ' '  not null,
   status     smallint       default 0    not null,
   firstpage  int            default 0    not null,
   totpgs     int            default 0    not null,
   allocpgs   int            default 0    not null,
   freepgs    int            default 0    not null,
   comment    varchar (21)   default ' '  not null,
   totpgtext  char (15)      default ' '  not null,
   alcpgtext  char (15)      default ' '  not null,
   frepgtext  char (15)      default ' '  not null,
   C          smallint       default 0    not null,
   T          smallint       default 0    not null,
   D          smallint       default 0    not null,
   S          smallint       default 0    not null)
go

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

   declare @cmdstring  varchar (255),
           @minC       smallint,
           @minD       smallint,
           @minT       smallint,
           @pct        double precision,
           @syblen     tinyint,
           @unixlen    tinyint

   select @pct = 100

   -- ALLOCATED

   insert into #devicespace (unixname, devname, totpgs, allocpgs, freepgs)
      select distinct d.phyname,
                      d.name,
                      1 + d.high - d.low,
                      sum (u.size),
                      1 + d.high - d.low - sum (u.size)
      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

   -- FREE

   insert into #devicespace (unixname, devname, totpgs, allocpgs, freepgs)
      select d.phyname,
             d.name,
             1 + d.high - d.low,
             0,
             1 + d.high - d.low
      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)

   select 'DEVICES         ' = count (unixname),
          'TOTAL PAGES'      = substring (convert (char (18),
                                                   convert (money,
                                                            sum (totpgs)),
                                                   1),
                                          1,
                                          15),
          'ALLOCATED PAGES'  = substring (convert (char (18),
                                                   convert (money,
                                                            sum (allocpgs)),
                                                   1),
                                          1,
                                          15),
          'FREE PAGES'       = substring (convert (char (18),
                                                   convert (money,
                                                            sum (freepgs)),
                                                   1),
                                          1,
                                          15),
          'FREE%'            = convert (tinyint, round (@pct * sum (freepgs) / sum (totpgs), 0))
   from #devicespace

   print ' '

   print ' '

   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 D = convert (tinyint,
                    substring (unixname,
                               charindex ('d', unixname) + 1,
                               charindex ('s', unixname) - charindex ('d', unixname) - 1))
   where charindex ('d', unixname)  > 0
     and charindex ('s', 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'

   update #devicespace
   set C = -1,
       T = -1,
       D = -1
   where unixname = 'd_master'

   if (exists (select *
               from #devicespace
               where unixname = 'd_master'))
      begin
         print '    UNIX DISK INFORMATION FOR d_master IS UNKNOWN.'

         print ' '
      end

   select distinct 'DISK'            = substring ('c'
                                                  +  convert (varchar (3), C)
                                                  +  't'
                                                  +  convert (varchar (3), T)
                                                  +  'd'
                                                  +  convert (varchar (3), D),
                                                  1,
                                                  8),
                   DEVICES           = convert (tinyint, count (unixname)),
                   'TOTAL PAGES'     = substring (convert (char (18),
                                                           convert (money,
                                                                    sum (totpgs)),
                                                           1),
                                                  1,
                                                  15),
                   'ALLOCATED PAGES' = substring (convert (char (18),
                                                           convert (money,
                                                                    sum (allocpgs)),
                                                           1),
                                                  1,
                                                  15),
                   'FREE PAGES'      = substring (convert (char (18),
                                                           convert (money,
                                                                    sum (freepgs)),
                                                           1),
                                                  1,
                                                  15),
          'FREE%'            = convert (tinyint, round (@pct * sum (freepgs) / sum (totpgs), 0))
   from #devicespace
   where unixname <> 'd_master'
   group by C, T, D
   having unixname <> 'd_master'
   order by C, T, D

   print ' '

   print ' '

   update #devicespace
   set totpgtext = substring (convert (char (18), convert (money, totpgs), 1), 1, 15),
       alcpgtext = substring (convert (char (18), convert (money, allocpgs), 1), 1, 15),
       frepgtext = substring (convert (char (18), convert (money, freepgs), 1), 1, 15)

   select @minC = min (C)
   from #devicespace

   select @minT = min (T)
   from #devicespace
   where C = @minC

   select @minD = min (D)
   from #devicespace
   where C = @minC
     and T = @minT

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

   delete from #devicespace
   where C = @minC
     and T = @minT
     and D = @minD
     and S = -1

   select @unixlen = max (char_length (unixname)),
          @syblen  = max (char_length (devname))
   from #devicespace

   select @cmdstring = 'select ''UNIX DEVICE'' = substring (unixname, 1,'
                       +  convert (varchar (2), @unixlen)
                       +  '),'
                       +  '''SYBASE DEVICE'' = substring (devname, 1,'
                       +  convert (varchar (2), @syblen)
                       +  '),'
                       +  '''TOTAL PAGES'' = totpgtext,'
                       +  '''ALLOCATED PAGES'' = alcpgtext,'
                       +  '''FREE PAGES'' = frepgtext'
                       +  ' from #devicespace'
                       +  ' order by C, T, D, S'

   execute  (@cmdstring)

   print ' '
go
sp_existsprocedure sp_devicespaceagg
go

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

   declare @cmdstring  varchar (255),
           @comlen     tinyint,
           @dblen      tinyint,
           @devlen     tinyint,
           @freestring char (30),
           @minC       smallint,
           @minD       smallint,
           @minT       smallint

   print '    ALL DEVICE SPACE INFORMATION WILL BE LISTED.'

   print ' '

   select @freestring = replicate ('-', 30)

   -- ALLOCATED

   insert into #devicespace (unixname, devname, dbname, status, firstpage,
                             totpgs, comment)
      select d.phyname,
             d.name,
             db_name (u.dbid),
             d.status,
             u.vstart,
             u.size,
             isnull (substring (vS.name, 1, 12), '')
      from master.dbo.sysdevices d,
           master.dbo.sysusages  u,
           sybsystemprocs.dbo.usptvalues 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'

   -- FREE LOW

   insert into #devicespace (unixname, devname, dbname, status, firstpage,
                             totpgs)
      select d.phyname,
             d.name,
             @freestring,
             d.status,
             d.low,
             abs (u.vstart - d.low)
      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

   -- FREE MIDDLE

   insert into #devicespace (unixname, devname, dbname, status, firstpage,
                             totpgs)
      select d.phyname,
             d.name,
             @freestring,
             d.status,
             u1.vstart + u1.size,
             abs (u2.vstart - u1.vstart - u1.size)
      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

   -- FREE HIGH

   insert into #devicespace (unixname, devname, dbname, status, firstpage,
                             totpgs)
      select d.phyname,
             d.name,
             @freestring,
             d.status,
             u.vstart + u.size,
             abs (d.high - u.vstart - u.size + 1)
      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

   -- FREE DEVICES

   insert into #devicespace (unixname, devname, dbname, status, firstpage,
                             totpgs)
      select d.phyname,
             d.name,
             @freestring,
             d.status,
             d.low,
             abs (d.high - d.low + 1)
      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 D = convert (tinyint,
                    substring (unixname,
                               charindex ('d', unixname) + 1,
                               charindex ('s', unixname) - charindex ('d', unixname) - 1))
   where charindex ('d', unixname)  > 0
     and charindex ('s', 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 = ' ',
       C        = -1,
       T        = -1,
       D        = -1
   where unixname = 'd_master'

   --
   -- "SEPARATE" BY DEVICE NAME.
   --               -----------

   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.
   --                                -----

   update #devicespace
   set totpgtext = substring (convert (char (18), convert (money, totpgs), 1), 1, 15)

   select @minC = min (C)
   from #devicespace

   select @minT = min (T)
   from #devicespace
   where C = @minC

   select @minD = min (D)
   from #devicespace
   where C = @minC
     and T = @minT

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

   delete from #devicespace
   where C = @minC
     and T = @minT
     and D = @minD
     and S = -1

   select @devlen = max (char_length (devname)),
          @comlen = max (char_length (comment))
   from #devicespace

   select @dblen = max (char_length (dbname))
   from #devicespace
   where dbname <> @freestring

   select @cmdstring = 'select DEVICE = substring (devname, 1,'
                       +  convert (varchar (2), @devlen)
                       +  '),'
                       +  '''DATABASE'' = substring (dbname, 1,'
                       +  convert (varchar (2), @dblen)
                       +  '),'
                       +  '''FRAGMENT PAGES'' = totpgtext,'
                       +  '''COMMENT'' = substring (comment, 1,'
                       +  convert (varchar (2), @comlen)
                       +  ')'
                       +  ' from #devicespace'
                       +  ' order by C, T, D, S, firstpage'

   execute  (@cmdstring)
go
sp_existsprocedure sp_devicespaceall
go

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

   declare @cmdstring  varchar (255),
           @comlen     tinyint,
           @dblen      tinyint,
           @freestring char (30)

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

   print ' '

   select @freestring = replicate ('-', 30)

   -- ALLOCATED

   insert into #devicespace (dbname, status, firstpage, totpgs, comment)
      select db_name (u.dbid),
             d.status,
             u.vstart,
             u.size,
             isnull (substring (vS.name, 1, 12), '')
      from master.dbo.sysdevices d,
           master.dbo.sysusages  u,
           sybsystemprocs.dbo.usptvalues 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'

   -- FREE LOW

   insert into #devicespace (dbname, status, firstpage, totpgs)
      select @freestring,
             d.status,
             d.low,
             abs (u.vstart - d.low)
      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

   -- FREE MIDDLE

   insert into #devicespace (dbname, status, firstpage, totpgs)
      select @freestring,
             d.status,
             u1.vstart + u1.size,
             abs (u2.vstart - u1.vstart - u1.size)
      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

   -- FREE HIGH

   insert into #devicespace (dbname, status, firstpage, totpgs)
      select @freestring,
             d.status,
             u.vstart + u.size,
             abs (d.high - u.vstart - u.size + 1)
      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

   -- FREE DEVICE

   insert into #devicespace (dbname, status, firstpage, totpgs, comment)
      select @freestring,
             d.status,
             d.low,
             abs (d.high - d.low + 1),
             'device'
      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

   select @dblen  = max (char_length (dbname))
   from #devicespace
   where dbname <> @freestring

   select @comlen = max (char_length (comment))
   from #devicespace

   select @cmdstring = 'select ''DATABASE'' = substring (dbname, 1,'
                       +  convert (varchar (2), @dblen)
                       +  '),'
                       +  '''FRAGMENT PAGES'' = substring (convert (char (17),'
                       +  'convert (money, totpgs), 1), 1, 14),'
                       +  '''COMMENT'' = substring (comment, 1,'
                       +  convert (varchar (2), @comlen)
                       +  ')'
                       +  ' from #devicespace'
                       +  ' order by firstpage'

   execute  (@cmdstring)
go
sp_existsprocedure sp_devicespacedvc
go

drop table #devicespace
go

create procedure sp_devicespace (@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_devicespace CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @likestring  varchar ( 30)

   create table #devicespace
     (unixname   varchar (127)  default ' '  not null,
      devname    varchar (30)   default ' '  not null,
      dbname     varchar (30)   default ' '  not null,
      status     smallint       default 0    not null,
      firstpage  int            default 0    not null,
      totpgs     int            default 0    not null,
      allocpgs   int            default 0    not null,
      freepgs    int            default 0    not null,
      comment    varchar (21)   default ' '  not null,
      totpgtext  char (15)      default ' '  not null,
      alcpgtext  char (15)      default ' '  not null,
      frepgtext  char (15)      default ' '  not null,
      C          smallint       default 0    not null,     -- Controller
      T          smallint       default 0    not null,     -- Target
      D          smallint       default 0    not null,     -- Disk
      S          smallint       default 0    not null)     -- Slice

   if (@DEVICE IS NULL)
      begin
         execute  sp_devicespaceall
      end
   else if (upper (@DEVICE) = 'AGG')
      begin
         execute  sp_devicespaceagg
      end
   else if (exists (select *
                    from master.dbo.sysdevices
                    where name      = @DEVICE
                      and cntrltype = 0))
      begin
         execute  sp_devicespacedvc @DEVICE=@DEVICE
      end
   else if (exists (select *
                     from master.dbo.sysdevices
                     where name       = @DEVICE
                       and cntrltype <> 0))
      begin
         print '    DEVICE %1! IS NOT A DISK DEVICE.', @DEVICE

         print ' '
      end
   else
      begin
         print '    INVALID ARGUMENT %1! FOR sp_devicespace.', @DEVICE

         execute  sp_utilprocs  devicespace

         execute  sp_like_string  @INPUT_STRING=@DEVICE,
                                  @LIKE_STRING=@likestring  output

         if (exists (select *
                     from master.dbo.sysdevices
                     where name      like @likestring
                       and cntrltype    = 0))
            begin
               print ' '

               print '           THERE ARE DISK DEVICES LIKE %1!.', @likestring

               print ' '

               select DEVICE = name
               from master.dbo.sysdevices
               where name      like @likestring
                 and cntrltype    = 0
               order by DEVICE
            end
      end
go
sp_addcomment sp_devicespace, 'Lists space allocation information for disk devices:'
go
sp_addcomment sp_devicespace, '   NULL:   information for all devices (default)'
go
sp_addcomment sp_devicespace, '   "AGG":  aggregate information (three levels)'
go
sp_addcomment sp_devicespace, '   DEVICE: information only for DEVICE'
go
sp_procxmode sp_devicespace, 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

   declare  @cmdstring1  varchar (255),
            @cmdstring2  varchar (255),
            @dblen       tinyint,
            @devlen      tinyint,
            @likestring  varchar ( 30),
            @usagelen    tinyint

   create table #fragments
      (sort1     varchar (30)   not null,            -- db name
       sort2     int            not null,            -- vstart
       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         smallint       null,                -- controller #
       T         smallint       null,                -- target #
       D         smallint       null,                -- disk #
       S         smallint       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,
                   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,
             D       = 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 D = convert (tinyint,
                          substring (phyname,
                                     charindex ('d', phyname) + 1,
                                     charindex ('s', phyname) - charindex ('d', phyname) - 1))
         where charindex ('d', phyname) > 0
           and charindex ('s', 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.D     = f.D
                               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.D     = f2.D
                                                 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.D     = f.D
                               and f2.S     = f.S
                               and ((4860 * f2.C) + (81 * f2.T) + (9 * f2.D) + f2.S)
                                            > (select min ((4860 * f3.C) + (81 * f3.T) + (9 * f3.D) + f3.S)
                                               from #fragments f3
                                               where f3.sort1 = f2.sort1))

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

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

               select @dblen    = max (char_length (dbname)),
                      @devlen   = max (char_length (devname)),
                      @usagelen = max (char_length (comment))
               from #fragments

               select @cmdstring1 = 'select ''DATABASE'' = substring (dbname, 1,'
                                    +  convert (varchar (2), @dblen)
                                    +  '),'
                                    +  '''DEVICE'' = substring (devname, 1,'
                                    +  convert (varchar (2), @devlen)
                                    +  '),'
                                    +  '''FRAGMENT PAGES'' = isnull (substring (convert (char (18),'
                                    +  'convert (money, size), 1), 2, 14), '' ''),'

               select @cmdstring2 = '''USED PAGES'' = isnull (substring (convert (char (14),'
                                    +  'convert (money, size - free), 1), 2, 10), '' ''),'
                                    +  'USAGE = substring (comment, 1,'
                                    +  convert (varchar (2), @usagelen)
                                    +  ')'
                                    +  ' from #fragments'
                                    +  ' order by sort1, C, T, D, S, sort2'

               execute  (@cmdstring1  +  @cmdstring2)
            end
         else
            begin
               update #fragments
               set phyname = ' '
               where phyname IS NULL

               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 @devlen   = max (char_length (devname)),
                      @usagelen = max (char_length (comment))
               from #fragments

               select @cmdstring1 = 'select ''DEVICE'' = substring (devname, 1,'
                                    +  convert (varchar (2), @devlen)
                                    +  '),'
                                    +  '''FRAGMENT PAGES'' = isnull (substring (convert (char (18),'
                                    +  'convert (money, size), 1), 2, 14), '' ''),'

               select @cmdstring2 = '''USED PAGES'' = isnull (substring (convert (char (14),'
                                    +  'convert (money, size - free), 1), 2, 10), '' ''),'
                                    +  'USAGE = substring (comment, 1,'
                                    +  convert (varchar (2), @usagelen)
                                    +  ')'
                                    +  ' from #fragments'
                                    +  ' order by sort1, C, T, D, S, sort2'

               execute  (@cmdstring1  +  @cmdstring2)
            end
      end
   else
      begin
         print '    DATABASE %1! DOES NOT EXIST.', @DATABASE

         print ' '

         execute  sp_like_string  @INPUT_STRING=@DATABASE,
                                  @LIKE_STRING=@likestring  output

         if (exists (select *
                     from master.dbo.sysdatabases
                     where name like @likestring))
            begin
               print ' '

               print '           THERE ARE DATABASES LIKE %1!.', @likestring

               print ' '

               select 'DATABASE' = name
               from master.dbo.sysdatabases
               where name like @likestring
               order by 'DATABASE'
            end
      end
go
sp_addcomment sp_fragments, 'Lists device space 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,
      D        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,
             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,
             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,
             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,
             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 D = convert (tinyint,
                          substring (phyname,
                                     charindex ('d', phyname) + 1,
                                     charindex ('s', phyname) - charindex ('d', phyname) - 1))
         where charindex ('d', phyname) > 0
           and charindex ('s', 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, D, 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, D, 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, 'Lists free space on DEVICE or (default) all database devices'
go
sp_procxmode sp_freedevice, anymode
go

create proc sp_set (@FORMAT_FLAG  varchar (1) = NULL)  as

   declare @aggcnt          tinyint,
           @clientname      varchar ( 30),
           @clientapplname  varchar ( 30),
           @clienthostname  varchar ( 30),
           @cmdstring       varchar (255),
           @high            int,
           @identity        varchar ( 60),
           @len2            tinyint,
           @len3            tinyint,
           @low             int,
           @maxlen          tinyint,
           @maxseq          tinyint,
           @rowcount        varchar ( 60),
           @transtate       varchar ( 60),
           @seq2            numeric (7, 0),
           @seq3            tinyint

   select @identity  = convert (varchar (40), @@identity),
          @rowcount  = convert (varchar (40), @@rowcount),
          @transtate = convert (varchar (40), @@transtate)

   print ' '

   commit work

   create table #set1
      (txt  varchar (60))

   commit work

   create table #set2
      (txt  varchar (55)    not null,
       seq  numeric (7, 0)  identity)

   commit work

   --  LOAD INDIVIDUAL SESSION VARIABLES

   insert into #set1
      values ('@@char_convert ('  +  convert (varchar (40), @@char_convert))

   insert into #set1
      values ('@@client_csid ('  +  convert (varchar (40), @@client_csid))

   insert into #set1
      values ('@@client_csname ('  +  @@client_csname)

   insert into #set1
      values ('@@identity ('  +  @identity)

   insert into #set1
      values ('@@isolation ('  +  convert (varchar (40), @@isolation))

   insert into #set1
      values ('@@langid ('  +  convert (varchar (40), @@langid))

   insert into #set1
      values ('@@language ('  +  @@language)

   insert into #set1
      values ('@@maxcharlen ('  +  convert (varchar (40), @@maxcharlen))

   insert into #set1
      values ('@@nestlevel ('  +  convert (varchar (40), @@nestlevel - 1))

   insert into #set1
      values ('@@ncharsize ('  +  convert (varchar (40), @@ncharsize))

   insert into #set1
      values ('@@parallel_degree ('  +  convert (varchar (40), @@parallel_degree))

   insert into #set1
      values ('@@rowcount ('  +  @rowcount)

   insert into #set1
      values ('@@scan_parallel_degree ('  +  convert (varchar (40), @@scan_parallel_degree))

   insert into #set1
      values ('@@sqlstatus ('  +  convert (varchar (40), @@sqlstatus))

   insert into #set1
      values ('@@textcolid ('  +  convert (varchar (40), @@textcolid))

   insert into #set1
      values ('@@textdbid ('  +  convert (varchar (40), @@textdbid))

   insert into #set1
      values ('@@textobjid ('  +  convert (varchar (40), @@textobjid))

   insert into #set1
      values ('@@textsize ('  +  ltrim (substring (convert (char (10), convert (money, @@textsize), 1), 1, 7)))

   insert into #set1
      values ('@@textts ('  +  convert (varchar (40), @@textts))

   insert into #set1
      values ('@@tranchained ('  +  convert (varchar (40), @@tranchained))

   insert into #set1
      values ('@@trancount ('  +  convert (varchar (40), @@trancount - @@tranchained))

   insert into #set1
      values ('@@transtate ('  +  @transtate)

   -- LOAD NON-NULL SETTABLE CLIENT IDENTIFIER VALUES

   select @clientapplname = ltrim (clientapplname),
          @clienthostname = ltrim (clienthostname),
          @clientname     = ltrim (clientname)
   from master.dbo.sysprocesses
   where spid = @@spid

   if (@clientapplname IS NOT NULL)
      begin
         insert into #set1
            values ('clientapplname ('  +  @clientapplname)
      end

   if (@clienthostname IS NOT NULL)
      begin
         insert into #set1
            values ('clienthostname ('  +  @clienthostname)
      end

   if (@clientname IS NOT NULL)
      begin
         insert into #set1
            values ('clientname ('  +  @clientname)
      end

   --  LOAD SESSION VARIABLES STORED IN @@options

   select @high = convert (int, substring (@@options, 1, 4)),
          @low  = convert (int, substring (@@options, 5, 4))

   -- CHECK HIGH BYTES OF @@options

   insert into #set1
      select substring (vSET.name  +  ' (on', 1, 55)
      from sybsystemprocs.dbo.usptvalues vSET
      where vSET.type              = 'SET'
        and vSET.number           <> 0
        and vSET.aux1              = 0
        and (@high & vSET.number)  = vSET.number

   insert into #set1
      select substring (vSET.name  +  ' (off', 1, 55)
      from sybsystemprocs.dbo.usptvalues vSET
      where vSET.type              = 'SET'
        and vSET.number           <> 0
        and vSET.aux1              = 0
        and (@high & vSET.number) <> vSET.number

   -- CHECK LOW BYTES OF @@options

   insert into #set1
      select substring (vSET.name  +  ' (on', 1, 55)
      from sybsystemprocs.dbo.usptvalues vSET
      where vSET.type             = 'SET'
        and vSET.number          <> 0
        and vSET.aux1             = 1
        and (@low & vSET.number)  = vSET.number

   insert into #set1
      select substring (vSET.name  +  ' (off', 1, 55)
      from sybsystemprocs.dbo.usptvalues vSET
      where vSET.type             = 'SET'
        and vSET.number          <> 0
        and vSET.aux1             = 1
        and (@low & vSET.number) <> vSET.number

   insert into #set2
      select txt  +  ')'
      from #set1
      order by txt

   -- AGGREGATE THE arithabort AND process_limit_action OPTIONS

   select @aggcnt = count (*)
   from #set2
   where txt like 'arithabort%(off)'

   if (@aggcnt = 0)
      begin
         delete from #set2
         where txt = 'arithabort arith_overflow (on)'

         update #set2
         set txt = 'arithabort (on)'
         where txt = 'arithabort numeric_truncation (on)'
      end
   else if (@aggcnt > 1)
      begin
         delete from #set2
         where txt = 'arithabort arith_overflow (off)'

         update #set2
         set txt = 'arithabort (off)'
         where txt = 'arithabort numeric_truncation (off)'
      end

   select @aggcnt = count (*)
   from #set2
   where txt like 'process%(off)'

   if (@aggcnt = 1)
      begin
         select @aggcnt = count (*)
         from #set2
         where txt = 'process_limit_action abort (off)'

         if (@aggcnt = 1)
            begin
               delete from #set2
               where txt = 'process_limit_action abort (off)'

               update #set2
               set txt = 'process_limit_action (warning)'
               where txt = 'process_limit_action warning (on)'
            end
         else
            begin
               delete from #set2
               where txt = 'process_limit_action warning (off)'

               update #set2
               set txt = 'process_limit_action (abort)'
               where txt = 'process_limit_action abort (on)'
            end
      end
   else if (@aggcnt = 2)
      begin
         delete from #set2
         where txt = 'process_limit_action abort (off)'

         update #set2
         set txt = 'process_limit_action (quiet)'
         where txt = 'process_limit_action warning (off)'
      end
   else if (@aggcnt = 2)
      begin
         print '    LOGIC ERROR WITH process_limit_action)'

         print ' '
      end

   -- DISPLAY DATA DEPENDING UPON @FORMAT_FLAG

   if (@FORMAT_FLAG IS NULL)
      begin
         update #set2
         set txt = substring (txt, 1, char_length (txt) - 1)

         select @maxlen = max (charindex (' (', txt))
         from #set2

         select @maxlen = @maxlen
                          + max (char_length (txt) - charindex (' (', txt))
         from #set2

         update #set2
         set txt = stuff (txt,
                          charindex (' (', txt),
                          2,
                          ':'  +  space (@maxlen - char_length (txt)))

         select @cmdstring = 'select ''SESSION VALUES'' = substring (txt, 1,'
                             +  convert (varchar (2), @maxlen - 1)
                             +  ')'
                             +  ' from #set2'
                             +  ' order by seq'

         execute  (@cmdstring)
      end
   else
      begin
         commit work

         create table #set3
            (seq  tinyint       not null,
             txt  varchar (77)  not null)

         commit work

         select @maxseq = convert (tinyint, max (seq)),
                @seq2   = 2,
                @seq3   = 1
         from #set2

         insert into #set3 (seq, txt)
            select 1, txt
            from #set2
            where seq = 1

         while (@seq2 <= @maxseq)
            begin
               select @len2 = char_length (txt)
               from #set2
               where seq = @seq2

               select @len3 = char_length (txt)
               from #set3
               where seq = @seq3

               if ((@len2 + @len3) > 74)
                  begin
                     update #set3
                     set txt = txt  +  ','
                     where seq = @seq3

                     select @seq3 = @seq3 + 1

                     insert into #set3 (seq, txt)
                        select @seq3, txt
                        from #set2
                        where seq = @seq2
                  end
               else
                  begin
                     update #set3
                     set txt = s3.txt  +  ', '  +  s2.txt
                     from #set2 s2, #set3 s3
                     where s3.seq = @seq3
                       and s2.seq = @seq2
                  end

               select @seq2 = @seq2 + 1
            end

         select 'SESSION VALUES' = txt
         from #set3
         order by seq
      end

   commit work
go
sp_addcomment sp_set, 'Lists client session values'
go
sp_addcomment sp_set, '   If FORMAT FLAG is non-null compress the output'
go
sp_procxmode sp_set, 'anymode'
go

dump transaction sybsystemprocs with truncate_only
go
