use sybsystemprocs
go

drop procedure sp_db
go
drop procedure sp_dbopt                 -- discontinued; not beneficial
go
drop procedure sp_deviceUNIX
go
drop procedure sp_devicespaceagg
go
drop procedure sp_devicespaceall
go
drop procedure sp_devicespacedvc
go
drop procedure sp_devicestatus
go
drop procedure sp_fragments
go
drop procedure sp_freedevice
go
drop procedure sp_logins_like
go
drop procedure sp_logins_roles
go
drop procedure sp_logins_status
go
drop procedure sp_logins
go
drop procedure sp_users_alias
go
drop procedure sp_users_all
go
drop procedure sp_users_like
go
drop procedure sp_users_list            -- In 1151B
go
drop procedure sp_users_roles
go
drop procedure sp_users
go

set quoted_identifier on
go

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

   if (@DATABASE IS NULL)
      begin
         set nocount on
      end

   print ' '

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

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

         print ' '

         return 1
      end

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

   create table #db
      (dbid         int            default 0           not null,
       name         varchar (30)                       not null,
       status       smallint       default 0           not null,
       status2      int            default 0           not null,
       owner        smallint       default 1           not null,
       crdate       smalldatetime  default getdate ()  not null,
       dataflag     bit            default 0           not null,
       logflag      bit            default 0           not null,
       mixedflag    bit            default 0           not null,
       datalogtext  varchar (30)   default ' '         not null,
       size         char (11)      default ' '         null,
       txt          varchar (180)                      null,
       sortname     varchar (30)                       not null,
       seq          tinyint                            not null)

   select @bitoff = convert (bit, 0)

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

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

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

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

         print ' '

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

         print ' '
      end

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

         print ' '

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

         print ' '
      end

   select @sequence = 1

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

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

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

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

               select @sequence = @sequence + 1
            end

         update #db
         set txt = substring (txt, 3, 180)
         where txt IS NOT NULL

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

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

         if (@DATABASE IS NULL)
            begin
               select @sequence = 1

               if (@maxdblen < 16)
                  begin
                     select @maxtextlen = 61
                  end
               else if (@maxdblen < 20)
                  begin
                     select @maxtextlen = 57
                  end
               else
                  begin
                     select @maxtextlen = 46
                  end

               while (@sequence < 6)
                  begin
                     insert into #db (name, txt, sortname, seq)
                        select ' ', txt, sortname, (@sequence + 1)
                        from #db
                        where char_length (txt) > @maxtextlen

                     update #db
                     set txt = substring (db1.txt, 1, @maxtextlen)
                     from #db db1, #db db2
                     where db1.seq      = @sequence
                       and db2.sortname = db1.sortname
                       and db2.seq      = @sequence + 1

                     update #db
                     set txt = substring (db1.txt, 1, @maxtextlen + 1 - charindex (',', reverse (db1.txt)))
                     from #db db1, #db db2
                     where db1.seq      = @sequence
                       and db2.sortname = db1.sortname
                       and db2.seq      = @sequence + 1

                     -- Note that "db2" is updated in the following

                     update #db
                     set txt = substring (db2.txt, 2 + char_length (db1.txt), 180)
                     from #db db2, #db db1
                     where db1.seq      = @sequence
                       and db2.sortname = db1.sortname
                       and db2.seq      = @sequence + 1

                     select @sequence = @sequence + 1
                  end

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

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

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

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

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

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

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

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

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

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

               print ' '

               if (exists (select *
                           from master.dbo.sysdatabases
                           where name like (@DATABASE  +  '%')))
                  begin
                     print '    DATABASES LIKE <%1!%%> EXIST.', @DATABASE

                     print ' '

                     select 'DATABASE' = name
                     from master.dbo.sysdatabases
                     where name like (@DATABASE  +  '%')
                     order by 'DATABASE'
                  end
            end
         else
            begin
               select @cachename = substring (char_value, 1, 28)
               from master.dbo.sysattributes
               where class       = 3
                 and attribute   = 0
                 and object_type = 'D'
                 and object      = @dbid

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

               print ' '

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

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

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

   if (exists (select *
               from master.dbo.sysdevices
               where name = @DEVICE))
      begin
         set nocount on
      end

   print ' '

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

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

         print ' '

         return 1
      end

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

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

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

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

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

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

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

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

         print ' '
      end
   else
      begin
         print '    USAGE: sp_deviceUNIX:        All UNIX file names by type & physical device'

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

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

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

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

create table #devicespace
  (unixname   varchar (127)  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,
   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

   -- 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)
   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 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
   where unixname = 'd_master'

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

         print ' '
      end

   select distinct 'DISK'            = 'c'
                                       +  convert (varchar (3), C)
                                       +  't'
                                       +  convert (varchar (3), T),
                   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)
   from #devicespace
   where unixname <> 'd_master'
   group by C, T
   having unixname <> 'd_master'
   order by C, T

   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)

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

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

   select 'UNIX DEVICE'     = substring (unixname, 1, 11),
          'SYBASE DEVICE'   = substring (devname, 1, 14),
          'TOTAL PAGES'     = totpgtext,
          'ALLOCATED PAGES' = alcpgtext,
          'FREE PAGES'      = frepgtext
   from #devicespace
   order by C, T, S

   print ' '
go
sp_procxmode sp_devicespaceagg, unchained
go

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

   print '    ALL DEVICE SPACE INFORMATION WILL BE LISTED.'

   print ' '

   -- 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,
           master.dbo.spt_values vS
      where d.cntrltype  = 0
        and d.low       <= u.vstart + u.size
        and d.high      >= u.vstart + u.size - 1
        and vS.number    = (u.segmap & 7)
        and vS.type      = 'S'

   -- Free Low

   insert into #devicespace (unixname, devname, dbname, status, firstpage,
                             totpgs)
      select d.phyname,
             d.name,
             '-------FREE-------',
             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,
             '-------FREE-------',
             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,
             '-------FREE-------',
             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,
             '-------FREE-------',
             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 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
   where unixname = 'd_master'

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

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

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

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

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

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

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

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

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

   print ' '

   -- 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,
           master.dbo.spt_values vS
      where d.cntrltype  = 0
        and d.name       = @DEVICE
        and d.low       <= u.vstart + u.size
        and d.high      >= u.vstart + u.size - 1
        and vS.number    = (u.segmap & 7)
        and vS.type      = 'S'

   -- Free Low

   insert into #devicespace (dbname, status, firstpage, totpgs)
      select '--------FREE--------',
             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 '--------FREE--------',
             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 '--------FREE--------',
             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 '--------FREE--------',
             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 'DATABASE'       = substring (dbname, 1, 20),
          'FRAGMENT PAGES' = substring (convert (char (17),
                                                 convert (money,
                                                          totpgs),
                                                 1),
                                        1,
                                        14),
          'COMMENT'        = comment
   from #devicespace
   order by firstpage
go
sp_procxmode sp_devicespacedvc, unchained
go

drop table #devicespace
go


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

   print ' '

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

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

         print ' '

         return 1
      end

   declare @blocksize double precision,
           @kb        double precision,
           @mb        double precision,
           @capacity  varchar (8),
           @tapesz    double precision,
           @type      smallint,
           @units     char (3)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

         select @type = type
         from #devstat

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

               select DEVICE         = @DEVICE,
                      TYPE           = convert (tinyint, type),
                      NUMBER         = num,
                      'TOTAL PAGES'  = substring (convert (char (14),
                                                           convert (money,
                                                                    tpages),
                                                           1),
                                                  1,
                                                  11),
                      'ALLOC PAGES'  = substring (convert (char (14),
                                                           convert (money,
                                                                    apages),
                                                           1),
                                                  1,
                                                  11)
               from #devstat
            end
         else if ((@type > 2)  and  (@type < 9))
            begin
               select @blocksize = vE4.number
               from sybsystemprocs.dbo.usptvalues vE4
               where vE4.type = 'E4'

               select @blocksize = isnull (@blocksize, 32768),
                      @tapesz    = tpages,
                      @kb        = 1024,
                      @mb        = 1048576
               from #devstat

               select @tapesz = round ((@blocksize * @tapesz) / @mb, 0)

               if (@tapesz >= @kb)
                  begin
                     select @tapesz = @tapesz / @kb,
                            @units  = ' GB'
                  end
               else
                  begin
                     select @units = ' MB'
                  end

               select @capacity = ltrim (convert (varchar (8), str (@tapesz, 8, 0)))

               select @capacity = space (5 - char_length (@capacity))
                                  +  @capacity
                                  +  @units

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

         print ' '

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

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

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

   print ' '

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

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

         print ' '

         return 1
      end

   create table #fragments
      (sort1     varchar (30)   not null,            -- db name
       sort2     int            not null,            -- 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         tinyint        null,                -- controller #
       T         tinyint        null,                -- target #
       S         tinyint        null)                -- slice #

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

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

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

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

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

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

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

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

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

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

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

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

         print ' '
      end
go
sp_addcomment sp_fragments, 11, 'Lists device 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,
      S        tinyint        null)

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

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

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

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

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

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

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

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

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

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

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

create table #logins
  (suid        smallint                          not null,
   lname       varchar (30)                      not null,
   rname       varchar (32)  default ' '         not null,
   pwddate     datetime                          null,
   dbname      varchar (30)  default ' '         not null,
   locked      char (7)      default '     No'   not null,
   badpwd      char (8)      default '      No'  not null,
   status      smallint      default 0           not null,
   rolecount   smallint      default 0           not null,
   actvcount   smallint      default 0           not null,
   rlgncount   smallint      default 0           not null,
   sort        varchar (61)  default ' '         not null)
go

create procedure sp_logins_like (@PARM varchar (30))  as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print '                        PASSWORD                  USER  ACTIVE REMOTE'

   insert into #logins (suid, lname, pwddate, dbname)
      select suid, name, pwdate, dbname
      from master.dbo.syslogins
      where name like (@PARM  +  '%')

   update #logins
   set rolecount = (select count (lr.suid)
                    from master.dbo.sysloginroles lr
                    where lr.suid = l.suid
                      and lr.srid > 31),
       actvcount = (select count (lr.suid)
                    from master.dbo.sysloginroles lr
                    where lr.suid   = l.suid
                      and lr.srid   > 31
                      and lr.status > 0),
       rlgncount = (select count (rl.suid)
                    from master.dbo.sysremotelogins rl
                    where rl.suid = l.suid)
   from #logins l

   select 'LOGIN NAME' = substring (lname, 1, 22),
          '  DATE '    = convert (char (8), pwddate, 11),
          'DEFAULT DB' = substring (dbname, 1, 15),
          ROLES        = rolecount,
          DFLTS        = actvcount,
          LOGINS       = rlgncount
   from #logins
   order by lname
go
sp_procxmode sp_logins_like, unchained
go

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

   declare  @actvtext      varchar (78),
            @maxloginname  tinyint,
            @maxrolename   tinyint

   insert into #logins (suid, lname, rname, status, sort)
      select l.suid, l.name, sr.name, lr.status, l.name + ' ' + sr.name
      from master.dbo.syslogins l,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr
      where sr.srid      > 31
        and sr.password IS NULL
        and lr.srid      = sr.srid
        and l.suid       = lr.suid

   insert into #logins (suid, lname, rname, status, sort)
      select l.suid, l.name, sr.name + ' (PWD)', lr.status, l.name + ' ' + sr.name
      from master.dbo.syslogins l,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr
      where sr.srid      > 31
        and sr.password IS NOT NULL
        and lr.srid      = sr.srid
        and l.suid       = lr.suid

   if (exists (select *
               from #logins))
      begin
         update #logins
         set lname = ' '
         from #logins u1
         where exists (select *
                       from #logins u2
                       where u2.lname = u1.lname
                         and u2.rname = u1.rname
                         and u2.rname > (select min (rname)
                                         from #logins u3
                                         where u3.lname = u2.lname))

         select @maxloginname = max (char_length (lname)),
                @maxrolename  = max (char_length (rname))
         from #logins

         if ((@maxloginname < 15)  and  (@maxrolename < 15))
            begin
               select @actvtext = space (32) + 'ACTIVE'

               print @actvtext

               select 'LOGIN NAME' = substring (l.lname, 1, 14),
                      'USER ROLE'  = substring (l.rname, 1, 14),
                      'DEFAULT?'   = substring (vB.name, 1, 8)
               from #logins l,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = l.status
               order by l.sort
            end
         else if ((@maxloginname < 15)  and  (@maxrolename < 21))
            begin
               select @actvtext = space (38) + 'ACTIVE'

               print @actvtext

               select 'LOGIN NAME' = substring (l.lname, 1, 14),
                      'USER ROLE'  = substring (l.rname, 1, 20),
                      'DEFAULT?'   = substring (vB.name, 1, 8)
               from #logins l,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = l.status
               order by l.sort
            end
         else if (@maxloginname < 15)
            begin
               select @actvtext = space (61) + 'ACTIVE'

               print @actvtext

               select 'LOGIN NAME' = substring (l.lname, 1, 14),
                      'USER ROLE'  = l.rname,
                      'DEFAULT?'   = substring (vB.name, 1, 8)
               from #logins l,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = l.status
               order by l.sort
            end
         else if ((@maxloginname < 21)  and  (@maxrolename < 15))
            begin
               select @actvtext = space (38) + 'ACTIVE'

               print @actvtext

               select 'LOGIN NAME' = substring (l.lname, 1, 20),
                      'USER ROLE'  = substring (l.rname, 1, 14),
                      'DEFAULT?'   = substring (vB.name, 1, 8)
               from #logins l,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = l.status
               order by l.sort
            end
         else if ((@maxloginname < 21)  and  (@maxrolename < 21))
            begin
               select @actvtext = space (44) + 'ACTIVE'

               print @actvtext

               select 'LOGIN NAME' = substring (l.lname, 1, 20),
                      'USER ROLE'  = substring (l.rname, 1, 20),
                      'DEFAULT?'   = substring (vB.name, 1, 8)
               from #logins l,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = l.status
               order by l.sort
            end
         else if (@maxloginname < 21)
            begin
               select @actvtext = space (56) + 'ACTIVE'

               print @actvtext

               select 'LOGIN NAME' = substring (l.lname, 1, 20),
                      'USER ROLE'  = l.rname,
                      'DEFAULT?'   = substring (vB.name, 1, 8)
               from #logins l,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = l.status
               order by l.sort
            end
         else if (@maxrolename < 15)
            begin
               select @actvtext = space (48) + 'ACTIVE'

               print @actvtext

               select 'LOGIN NAME' = l.lname,
                      'USER ROLE'  = substring (l.rname, 1,14),
                      'DEFAULT?'   = substring (vB.name, 1, 8)
               from #logins l,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = l.status
               order by l.sort
            end
         else if (@maxrolename < 21)
            begin
               select @actvtext = space (54) + 'ACTIVE'

               print @actvtext

               select 'LOGIN NAME' = l.lname,
                      'USER ROLE'  = substring (l.rname, 1,20),
                      'DEFAULT?'   = substring (vB.name, 1, 8)
               from #logins l,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = l.status
               order by l.sort
            end
         else
            begin
               select @actvtext = space (66) + 'ACTIVE'

               print @actvtext

               select 'LOGIN NAME' = l.lname,
                      'USER ROLE'  = l.rname,
                      'DEFAULT?'   = substring (vB.name, 1, 8)
               from #logins l,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = l.status
               order by l.sort
            end
      end
   else
      begin
         print '    THERE ARE NO USER ROLES FOR ANY LOGIN.'

         print ' '
      end
go
sp_procxmode sp_logins_roles, unchained
go

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

   if (exists (select *
               from master.dbo.syslogins
               where status > 0))
      begin
         insert into #logins (suid, lname, pwddate, status)
            select suid, name, pwdate, status
            from master.dbo.syslogins
            where status > 0

         update #logins
         set badpwd = '     Yes'
         where (status & 1) = 1

         update #logins
         set locked = '    Yes'
         where (status & 2) = 2

         update #logins
         set pwddate = NULL
         where (status & 4) <> 4

         select 'LOGIN NAME'   = lname,
                'LOCKED?'      = locked,
                'BAD PWD?'     = badpwd,
                'PWD EXPIRED?' = space (4) + isnull (convert (char (8), pwddate, 11), '      No')
         from #logins
         order by lname
      end
   else
      begin
         print '    THERE ARE NO LOGINS WITH LOCKED/EXPIRED/BAD ACCOUNTS.'

         print ' '
      end
go
sp_procxmode sp_logins_status, unchained
go

drop table #logins
go


sp_addcomment sp_logins, 11, 'Lists logins with various scope/sort options:'
go
sp_addcomment sp_logins, 11, '   NULL:       all logins in name order (default)'
go
sp_addcomment sp_logins, 11, '   PREFIX:     all logins like PREFIX% in login name order'
go
sp_addcomment sp_logins, 11, '   "ROLES":    only logins with user roles in login/role name order'
go
sp_addcomment sp_logins, 11, '   "STATUS":   all logins with locked/expired/bad accounts'
go
sp_addcomment sp_logins, 11, '   "USAGE":    all logins in CPU and I/O usage order'
go
sp_procxmode sp_logins, anymode
go

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

create table #users
  (uname    varchar (30)  default ' '  not null,
   grname   varchar (32)  default ' '  not null,
   lname    varchar (32)               not null,
   status   smallint      default 0    not null,
   sort     varchar (61)  default ' '  not null)
go

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

   declare  @dbname  varchar (30)

   insert into #users
      select u.name, g.name, l.name + ' $', 0, u.name + ' ' + l.name
      from master.dbo.syslogins l,
           sysusers g,
           sysusers u
      where l.suid  = u.suid
        and l.name <> u.name
        and u.name <> 'dbo'
        and g.uid   = u.gid

   insert into #users
      select u.name, g.name, l.name + ' #', 0, u.name + ' ' + l.name
      from master.dbo.syslogins l,
           sysalternates a,
           sysusers g,
           sysusers u
      where a.suid = l.suid
        and u.suid = a.altsuid
        and g.uid  = u.gid

   if (exists (select *
               from #users))
      begin
         print '    "#" INDICATES AN ALIAS AND "$" INDICATES A PSEUDONYM.'

         print ' '

         update #users
         set uname  = ' ',
             grname = ' '
         from #users u
         where exists (select u2.lname
                       from #users u2
                       where u2.uname = u.uname
                         and u2.lname = u.lname
                         and u2.lname > (select min (u3.lname)
                                         from #users u3
                                         where u3.uname = u2.uname))

         select 'USER NAME'  = substring (uname, 1, 25),
                'USER GROUP' = substring (grname, 1, 25),
                'LOGIN NAME' = substring (lname, 1, 25)
         from #users
         order by sort
      end
   else
      begin
         select @dbname = db_name ()

         print '    THERE ARE NO ALIASES OR PSEUDONYMS IN DATABASE <%1!>.',
               @dbname

         print ' '
      end
go
sp_procxmode sp_users_alias, unchained
go

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

   insert into #users
      select 'guest', g.name, '-', 0, 'guest '
      from master.dbo.sysdatabases d,
           sysusers g,
           sysusers u
      where d.dbid = 1
        and u.name = 'guest'
        and g.uid  = u.gid

   insert into #users
      select u.name, g.name, l.name, 0, u.name + ' ' + l.name
      from master.dbo.syslogins l,
           sysusers g,
           sysusers u
      where l.suid = u.suid
        and ((l.name = u.name)  or  (u.name = 'dbo'))
        and g.uid  = u.gid

   insert into #users
      select u.name, g.name, l.name + ' $', 0, u.name + ' ' + l.name
      from master.dbo.syslogins l,
           sysusers g,
           sysusers u
      where l.suid  = u.suid
        and l.name <> u.name
        and u.name <> 'dbo'
        and g.uid   = u.gid

   insert into #users
      select u.name, g.name, l.name + ' #', 0, u.name + ' ' + l.name
      from master.dbo.syslogins l,
           sysalternates a,
           sysusers g,
           sysusers u
      where a.suid = l.suid
        and u.suid = a.altsuid
        and g.uid  = u.gid

   if (exists (select *
               from #users
               where lname like '% #'
                  or lname like '% $'))
      begin
         print '    "#" INDICATES AN ALIAS AND "$" INDICATES A PSEUDONYM.'

         print ' '

         update #users
         set uname  = ' ',
             grname = ' '
         from #users u
         where exists (select u2.lname
                       from #users u2
                       where u2.uname = u.uname
                         and u2.lname = u.lname
                         and u2.lname > (select min (u3.lname)
                                         from #users u3
                                         where u3.uname = u2.uname))
      end

   select 'USER NAME'  = substring (uname, 1, 25),
          'USER GROUP' = substring (grname, 1, 25),
          'LOGIN NAME' = substring (lname, 1, 25)
   from #users
   order by sort
go
sp_procxmode sp_users_all, unchained
go

create procedure sp_users_like (@PARM  varchar (30))
                 with recompile as

   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   if ((exists (select *
                from master.dbo.syslogins
                where name like @PARM + '%'))  or
       (exists (select *
                from sysusers
                where name like @PARM + '%')))
      begin
         insert into #users
            select u.name, g.name, '-', 0, ' '
            from master.dbo.sysdatabases d,
                 sysusers g,
                 sysusers u
            where d.dbid    = 1
              and u.name like @PARM + '%'
              and u.name    = 'guest'
              and g.uid     = u.gid

         insert into #users
            select u.name, g.name, l.name, 0, ' '
            from master.dbo.syslogins l,
                 sysusers g,
                 sysusers u
            where ((l.name like @PARM + '%')  or  (u.name like @PARM + '%'))
              and u.suid = l.suid
              and ((u.name = l.name)  or  (u.name = 'dbo'))
              and g.uid  = u.gid

         insert into #users
            select u.name + ' $', g.name, l.name, 0, ' '
            from master.dbo.syslogins l,
                 sysusers g,
                 sysusers u
            where ((l.name like @PARM + '%')  or  (u.name like @PARM + '%'))
              and u.suid  = l.suid
              and u.name <> l.name
              and u.name <> 'dbo'
              and g.uid   = u.gid

         insert into #users
            select u.name + ' #', g.name, l.name, 0, ' '
            from master.dbo.syslogins l,
                 sysalternates a,
                 sysusers g,
                 sysusers u
            where ((l.name like @PARM + '%')  or  (u.name like @PARM + '%'))
              and a.suid    = l.suid
              and u.suid    = a.altsuid
              and g.uid     = u.gid

         if (exists (select *
                     from #users
                     where uname like '% #'
                        or uname like '% $'))
            begin
               print '    "#" INDICATES AN ALIAS AND "$" INDICATES A PSEUDONYM.'

               print ' '
            end

         insert into #users
            select '-', '-', l.name, 0, ' '
            from master.dbo.syslogins l
            where l.name like @PARM + '%'
              and not exists (select a.*
                              from sysalternates a
                              where a.suid = l.suid)
              and not exists (select u.*
                              from sysusers u
                              where u.suid = l.suid)

         select 'USER NAME'  = substring (uname, 1, 25),
                'USER GROUP' = substring (grname, 1, 25),
                'LOGIN NAME' = substring (lname, 1, 25)
         from #users
         order by lname
      end
   else
      begin
         print '    THERE ARE NO USERS/LOGINS WHOSE NAME STARTS WITH <%1!>.',
               @PARM

         print ' '
      end
go
sp_procxmode sp_users_like, unchained
go

create procedure sp_users_roles
                 with recompile as

   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare  @actvtext     varchar (72),
            @maxrolename  tinyint,
            @maxusername  tinyint

   insert into #users
      select u.name, sr.name, ' ', lr.status, u.name + ' ' + sr.name
      from master.dbo.syslogins l,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr,
           sysroles r,
           sysusers u
      where sr.srid      > 31
        and sr.password IS NULL
        and r.id         = sr.srid
        and lr.srid      = sr.srid
        and l.suid       = lr.suid
        and u.suid       = l.suid

   insert into #users
      select u.name, sr.name + ' #', ' ', lr.status, u.name + ' ' + sr.name
      from master.dbo.syslogins l,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr,
           sysroles r,
           sysusers u
      where sr.srid      > 31
        and sr.password IS NOT NULL
        and r.id         = sr.srid
        and lr.srid      = sr.srid
        and l.suid       = lr.suid
        and u.suid       = l.suid

   if (exists (select *
               from #users))
      begin
         update #users
         set uname = ' '
         from #users u1
         where exists (select *
                       from #users u2
                       where u2.uname = u1.uname
                         and u2.grname = u1.grname
                         and u2.grname > (select min (grname)
                                         from #users u3
                                         where u3.uname = u2.uname))

         select @maxrolename = max (char_length (grname)),
                @maxusername = max (char_length (uname))
         from #users

         if ((@maxusername < 15)  and  (@maxrolename < 15))
            begin
               select @actvtext = space (32) + 'ACTIVE'

               print @actvtext

               select 'USER NAME' = substring (u.uname, 1, 14),
                      'USER ROLE' = substring (u.grname, 1, 14),
                      'DEFAULT?'  = substring (vB.name, 1, 8)
               from #users u,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = u.status
               order by sort
            end
         else if ((@maxusername < 15)  and  (@maxrolename < 21))
            begin
               select @actvtext = space (38) + 'ACTIVE'

               print @actvtext

               select 'USER NAME' = substring (u.uname, 1, 14),
                      'USER ROLE' = substring (u.grname, 1, 20),
                      'DEFAULT?'  = substring (vB.name, 1, 8)
               from #users u,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = u.status
               order by sort
            end
         else if (@maxusername < 15)
            begin
               select @actvtext = space (60) + 'ACTIVE'

               print @actvtext

               select 'USER NAME' = substring (u.uname, 1, 14),
                      'USER ROLE' = u.grname,
                      'DEFAULT?'  = substring (vB.name, 1, 8)
               from #users u,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = u.status
               order by sort
            end
         else if ((@maxusername < 21)  and  (@maxrolename < 15))
            begin
               select @actvtext = space (32) + 'ACTIVE'

               print @actvtext

               select 'USER NAME' = substring (u.uname, 1, 20),
                      'USER ROLE' = substring (u.grname, 1, 14),
                      'DEFAULT?'  = substring (vB.name, 1, 8)
               from #users u,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = u.status
               order by sort
            end
         else if ((@maxusername < 21)  and  (@maxrolename < 21))
            begin
               select @actvtext = space (44) + 'ACTIVE'

               print @actvtext

               select 'USER NAME' = substring (u.uname, 1, 20),
                      'USER ROLE' = substring (u.grname, 1, 20),
                      'DEFAULT?'  = substring (vB.name, 1, 8)
               from #users u,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = u.status
               order by sort
            end
         else if (@maxusername < 21)
            begin
               select @actvtext = space (56) + 'ACTIVE'

               print @actvtext

               select 'USER NAME' = substring (u.uname, 1, 20),
                      'USER ROLE' = u.grname,
                      'DEFAULT?'  = substring (vB.name, 1, 8)
               from #users u,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = u.status
               order by sort
            end
         else if (@maxrolename < 15)
            begin
               select @actvtext = space (48) + 'ACTIVE'

               print @actvtext

               select 'USER NAME' = u.uname,
                      'USER ROLE' = substring (u.grname, 1, 14),
                      'DEFAULT?'  = substring (vB.name, 1, 8)
               from #users u,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = u.status
               order by sort
            end
         else if (@maxrolename < 21)
            begin
               select @actvtext = space (54) + 'ACTIVE'

               print @actvtext

               select 'USER NAME' = u.uname,
                      'USER ROLE' = substring (u.grname, 1, 20),
                      'DEFAULT?'  = substring (vB.name, 1, 8)
               from #users u,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = u.status
               order by sort
            end
         else
            begin
               select @actvtext = space (66) + 'ACTIVE'

               print @actvtext

               select 'USER NAME' = u.uname,
                      'USER ROLE' = u.grname,
                      'DEFAULT?'  = substring (vB.name, 1, 8)
               from #users u,
                    sybsystemprocs.dbo.usptvalues vB
               where vB.type   = 'B'
                 and vB.number = u.status
               order by sort
            end
      end
   else
      begin
         print '    THERE ARE NO USER ROLES FOR ANY USER.'

         print ' '
      end
go
sp_procxmode sp_users_roles, unchained
go

drop table #users
go


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

         print ' '

         return 1
      end

   create table #users
     (uname    varchar (30)  default ' '  not null,
      grname   varchar (32)  default ' '  not null,
      lname    varchar (32)               not null,
      status   smallint      default 0    not null,
      sort     varchar (61)  default ' '  not null)

   if (user_id () = 1)
      begin
         print '    UPDATING STATISTICS ON SYSTEM TABLES.'

         print ' '

         update statistics sysalternates

         update statistics sysroles

         update statistics sysusers
      end

   if (upper (@PARM) = 'ALIAS')
      begin
         execute sp_users_alias
      end
   else if (upper (@PARM) = 'ROLES')
      begin
         execute sp_users_roles
      end
   else if (@PARM IS NULL)
      begin
         execute sp_users_all
      end
   else
      begin
         execute sp_users_like @PARM
      end
go
sp_addcomment sp_users, 11, 'Lists users with various scope/sort options:'
go
sp_addcomment sp_users, 11, '   (NULL):  all users in name order (default)'
go
sp_addcomment sp_users, 11, '   "ALIAS": all users with an alias or pseudonym'
go
sp_addcomment sp_users, 11, '   PREFIX:  all users/logins like PREFIX% in user/login name order'
go
sp_addcomment sp_users, 11, '   "ROLES": all users that have user roles'
go
sp_procxmode sp_users, anymode
go

dump transaction sybsystemprocs with truncate_only
go
