use sybsystemprocs
go
drop procedure sp_describe
go
drop procedure sp_displaydetail
go
drop procedure sp_findtext
go
drop procedure sp_sybprocs
go
drop procedure sp_sysprocsdetail
go
drop procedure sp_sysprocs
go
drop procedure sp_sysprocs_DBA
go
drop procedure sp_sysprocs_group
go
drop procedure sp_sysprocs_index
go
drop procedure sp_sysprocs_monitor
go
drop procedure sp_sysprocs_object
go
drop procedure sp_sysprocs_protect
go
drop procedure sp_sysprocs_system
go
drop procedure sp_sysprocs_xref
go

set quoted_identifier on
go

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

         print ' '

         return 1
      end

   create table #describe
     (id       int              not null,
      spname   varchar ( 22)    null,
      txt      varchar ( 77)    not null,
      crdate   datetime         null,
      tuples   char (  9)       null,
      fullname varchar ( 32)    not null,
      seq      tinyint          not null)

   if (upper (@PROCEDURE) = 'DATE')
      begin
         insert into #describe
            select o.id,
                   substring (o.name, 4, 22),
                   u.txt,
                   o.crdate,
                   NULL,
                   o.name,
                   0
            from sybsystemprocs.dbo.udescriptions u,
                 sybsystemprocs.dbo.sysobjects o
            where (o.sysstat & 15) = 4
              and o.id             = u.objid
              and u.seq            = 1
              and 1                = (select count (u2.seq)
                                      from sybsystemprocs.dbo.udescriptions u2
                                      where u2.spname = u.spname)

         insert into #describe
            select o.id,
                   substring (o.name + ' ##', 4, 22),
                   u.txt,
                   o.crdate,
                   NULL,
                   o.name,
                   0
            from sybsystemprocs.dbo.udescriptions u,
                 sybsystemprocs.dbo.sysobjects o
            where (o.sysstat & 15) = 4
              and o.id             = u.objid
              and u.seq            = 1
              and 1                < (select count (u2.seq)
                                      from sybsystemprocs.dbo.udescriptions u2
                                      where u2.spname = u.spname)

         select 'PROCEDURE' = substring (spname, 1, 16),
                DESCRIPTION = substring (txt, 1, 51),
                CREATED     = convert (char (8), crdate, 11)
         from #describe
         order by crdate, spname
      end
   else if (upper (@PROCEDURE) = 'DETAIL')
      begin
         insert into #describe
            select u.objid,
                   substring (u.spname, 4, 22),
                   u.txt,
                   NULL,
                   NULL,
                   u.spname,
                   u.seq
            from sybsystemprocs.dbo.udescriptions u,
                 sybsystemprocs.dbo.sysobjects o
            where (o.sysstat & 15) = 4
              and o.id             = u.objid

         update #describe
         set spname = ''
         where seq  > 1

         select 'PROCEDURE' = substring (spname, 1, 16),
                DESCRIPTION = substring (txt, 1, 60)
         from #describe
         order by fullname, seq
      end
   else if (upper (@PROCEDURE) = 'SIZE')
      begin
         insert into #describe
            select distinct u.objid,
                            substring (u.spname, 4, 22),
                            u.txt,
                            NULL,
                            substring (convert (char (12),
                                                convert (money,
                                                         count (p.sequence)),
                                                         1),
                                       1,
                                       9),
                            u.spname,
                            1
            from sybsystemprocs.dbo.udescriptions u,
                 sybsystemprocs.dbo.sysprocedures p
            where u.seq = 1
              and p.id  = u.objid
            group by u.objid
            having u.seq = 1
               and p.id  = u.objid

         update #describe
         set crdate = o.crdate
         from #describe d,
              sybsystemprocs.dbo.sysobjects o
         where d.id = o.id

         update #describe
         set spname = spname + ' ##'
         where 1 < (select max (u.seq)
                    from sybsystemprocs.dbo.udescriptions u
                    where u.objid = #describe.id)

         select 'PROCEDURE' = substring (isnull (spname, ''), 1, 16),
                DESCRIPTION = substring (txt, 1, 41),
                CREATED     = isnull (convert (char (8), crdate, 11), ' '),
                'EXEC ROWS' = isnull (tuples, '        0')
         from #describe
         order by fullname
      end
   else if (exists (select *
                    from sybsystemprocs.dbo.udescriptions
                    where spname = @PROCEDURE))
      begin
         select DESCRIPTION = txt
         from sybsystemprocs.dbo.udescriptions
         where spname = @PROCEDURE
         order by seq
      end
   else if (exists (select *
                    from sybsystemprocs.dbo.udescriptions
                    where spname = 'sp_' + @PROCEDURE))
      begin
         select DESCRIPTION = txt
         from sybsystemprocs.dbo.udescriptions
         where spname = 'sp_' + @PROCEDURE
         order by seq
      end
   else if (@PROCEDURE IS NOT NULL)
      begin
         print '    PROCEDURE <%1!> DOES NOT EXIST.', @PROCEDURE

         print ' '
      end
   else
      begin
         insert into #describe
            select o.id,
                   substring (o.name, 4, 22),
                   u.txt,
                   o.crdate,
                   NULL,
                   o.name,
                   u.seq
            from sybsystemprocs.dbo.udescriptions u,
                 sybsystemprocs.dbo.sysobjects o
            where (o.sysstat & 15) = 4
              and o.id             = u.objid
              and u.seq            = 1
              and 1                = (select count (u2.seq)
                                      from sybsystemprocs.dbo.udescriptions u2
                                      where u2.spname = u.spname)

         insert into #describe
            select o.id,
                   substring (o.name + ' ##', 4, 22),
                   u.txt,
                   o.crdate,
                   NULL,
                   o.name,
                   u.seq
            from sybsystemprocs.dbo.udescriptions u,
                 sybsystemprocs.dbo.sysobjects o
            where (o.sysstat & 15) = 4
              and o.id             = u.objid
              and u.seq            = 1
              and 1                < (select count (u2.seq)
                                      from sybsystemprocs.dbo.udescriptions u2
                                      where u2.spname = u.spname)

         update #describe
         set spname = ''
         where seq > 1

         select 'PROCEDURE' = substring (spname, 1, 16),
                DESCRIPTION = substring (txt, 1, 60)
         from #describe
         order by fullname, seq
      end
go
sp_addcomment sp_describe, 1, 'Describes utility procedures, e.g., "sp_describe sp_sysprocs"'
go
sp_addcomment sp_describe, 1, '   (NULL):    all utility procedures (default)'
go
sp_addcomment sp_describe, 1, '   "DATE":    all utility procedures with date info'
go
sp_addcomment sp_describe, 1, '   "DETAIL":  all utility procedures with detailed parameter info'
go
sp_addcomment sp_describe, 1, '   PROCEDURE: only utility PROCEDURE (leading "sp_" is optional)'
go
sp_addcomment sp_describe, 1, '   "SIZE":    all utility procedures with date and size info'
go
sp_procxmode sp_describe, anymode
go

create procedure sp_displaydetail (@OBJECTID   int,
                                   @LONG_FLAG  char (1) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @CR          char (1),      -- Carriage Return
           @LF          char (1),      -- Line Feed
           @max0row     int,           -- max system type row
           @max0subrow  int,           -- max system type subrow
           @objname     varchar (57),  -- name of @OBJECTID
           @subrow      smallint,      -- subrows by @LF; (also # syscomments rows)
           @sysstat     tinyint,       -- (sysbjects.sysstat & 15)
           @sysstat2    smallint       -- sysobjects.sysstat2

   create table #display
     (row       int            not null,
      subrow    tinyint        not null,
      texttype  tinyint        not null,
      pad       tinyint        not null,
      txt       varchar (255)  null)

   select @CR       = char (13),
          @LF       = char (10),
          @objname  = name,
          @sysstat  = (sysstat & 15),
          @sysstat2 = sysstat2
   from sysobjects
   where id = @OBJECTID

   select @subrow = count (*)
   from syscomments
   where id = @OBJECTID

   if (@subrow > 0)
      begin
         if (@subrow = 1)
            begin
               print '    THERE IS ONE PSEUDO-ROW OF TEXT FOR <%1!>.', @objname

               print ' '
            end
         else
            begin
               print '    THERE ARE %1! PSEUDO-ROWS OF TEXT FOR <%2!>.',
                     @subrow, @objname

               print ' '
            end

         insert into #display
            select (255 * colid2) + colid,
                   0,
                   texttype,
                   datalength (reverse (text)) - datalength (ltrim (reverse (text))),
                   text
            from syscomments
            where id = @OBJECTID

         select @subrow = 0

         while (exists (select *
                        from #display
                        where subrow               = @subrow
                          and charindex (@LF, txt) > 0))
            begin
               insert into #display
                  select row,
                         subrow + 1,
                         texttype,
                         pad,
                         substring (txt, charindex (@LF, txt) + 1, 255)
                  from #display
                  where subrow               = @subrow
                    and charindex (@LF, txt) > 0

               select @subrow = @subrow + 1
            end

         -- Note that if the pseudonymn order is reversed in the following
         -- from clause then the results will change.

         update #display
         set d1.txt = d1.txt
                      + space (d1.pad)
                      + substring (d2.txt, 1, charindex (@LF, d2.txt) - 1)
         from #display d1, #display d2
         where charindex (@LF, d1.txt) = 0
           and d1.row                  = d2.row - 1
           and d1.texttype             = d2.texttype
           and d2.subrow               = 0
           and charindex (@LF, d2.txt) > 0

         -- See the previous comment.

         delete #display
         from #display d2, #display d1
         where charindex (@LF, d1.txt) = 0
           and d1.row                  = d2.row - 1
           and d1.texttype             = d2.texttype
           and d2.subrow               = 0
           and charindex (@LF, d2.txt) > 0

         update #display
         set txt = substring (txt, 1, charindex (@LF, txt) - 1)
         where charindex (@LF, txt) > 0

         while (exists (select *
                        from #display
                        where charindex (@CR, txt) > 0))
            begin
               update #display
               set txt = substring (txt, 1, charindex (@CR, txt) - 1)
                         + substring (txt, charindex (@CR, txt) + 1, 255)
               where charindex (@CR, txt) > 0
            end

         select @max0row = max (row)
         from #display
         where txt      <> ' '
           and texttype  = 0

         delete from #display
         where row      > @max0row
           and texttype = 0

         select @max0subrow = max (subrow)
         from #display
         where row       = @max0row
           and txt      <> ' '
           and texttype  = 0

         delete from #display
         where row      = @max0row
           and subrow   > @max0subrow
           and texttype = 0

         insert into #display
            values ((@max0row + 1), 0, 0, 0, 'go')

         if (@sysstat = 4)
            begin
               if ((@sysstat2 & 32) = 32)
                  begin
                     insert into #display
                        values ((@max0row + 2), 0, 0, 0,
                                'sp_procxmode ' + @objname + ', anymode')

                     insert into #display
                        values ((@max0row + 3), 0, 0, 0, 'go')
                  end
               else if ((@sysstat2 & 16) = 16)
                  begin
                     insert into #display
                        values ((@max0row + 2), 0, 0, 0,
                                'sp_procxmode ' + @objname + ', chained')

                     insert into #display
                        values ((@max0row + 3), 0, 0, 0, 'go')
                  end
            end

         if (exists (select *
                     from #display
                     where texttype = 1))
            begin
               insert into #display
                  select max (row) + 1, 0, 1, 0, replicate ('-', 127)
                  from #display
                  where texttype = 1
            end

         if (@LONG_FLAG IS NULL)
            begin
               select TEXT = isnull (substring (txt, 1, 78), ' ')
               from #display
               order by texttype desc, row asc, subrow asc
            end
         else
            begin
               select TEXT = isnull (substring (txt, 1, 127), ' ')
               from #display
               order by texttype desc, row asc, subrow asc
            end
      end
   else
      begin
         print '    THERE IS NO TEXT FOR <%1!>.', @objname

         print ' '
      end
go
sp_procxmode sp_displaydetail, unchained
go

create procedure sp_findtext (@TEXT          varchar (255),
                              @CASEOFF_FLAG  char (1)      = 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_findtext> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @CR         char (1),
           @HT         char (1),
           @LF         char (1),
           @length     tinyint,
           @maxobjlen  tinyint,
           @oc         varchar (7),
           @textpct    varchar (22),
           @seq        tinyint

   create table #findtext
     (id      int            not null,
      uid     smallint       not null,
      name    varchar (30)   not null,
      colid   tinyint        not null,
      colid2  tinyint        not null,
      seq     tinyint        not null,
      txt     varchar (255)  not null)

   select @CR     = char (13),                        -- Carriage Return
          @HT     = char (9),                         -- Horizontal Tab
          @LF     = char (10),                        -- Line Feed
          @length = char_length (@TEXT),
          @seq    = 2

   if (@TEXT IS NULL)
      begin
         print '    THE TEXT PARAMETER CANNOT BE NULL.'

         print ' '
      end
   else if (@length > 19)
      begin
         print '    SHORTEN THE TEXT TO LESS THAN 20 CHARACTERS AND RETRY.'

         print ' '
      end
   else
      begin
         if (@TEXT in ('--', '/*'))
            begin
               select @length = 0
            end
         else
            begin
               select @length = (50 - @length) / 2
            end

         if (@CASEOFF_FLAG IS NULL)
            begin
               select @textpct = '%' + @TEXT + '%'

               insert into #findtext
                  select id, 0, object_name (id), colid, colid2, 1, text
                  from syscomments
                  where text   like @textpct
                  and texttype    = 0
            end
         else
            begin
               select @textpct = '%' + upper (@TEXT) + '%'

               insert into #findtext
                  select id, 0, object_name (id), colid, colid2, 1, text
                  from syscomments
                  where upper (text) like @textpct
                    and texttype        = 0
            end

         if (@CASEOFF_FLAG IS NULL)
            begin
               update #findtext
               set txt = substring (txt, patindex (@textpct, txt) - @length, 200)
               where patindex (@textpct, txt) > @length

               while (@seq < 6)
                  begin
                     insert into #findtext
                        select id, uid, name, colid, colid2, @seq,
                               substring (txt, 56, 200)
                        from #findtext
                        where seq                         = @seq - 1
                          and substring (txt, 56, 200) like @textpct

                     update #findtext
                     set txt = substring (txt, patindex (@textpct, txt) - @length, 200)
                     where seq                      = @seq
                       and patindex (@textpct, txt) > @length

                     select @seq = @seq + 1
                  end

               delete from #findtext
               where patindex (@textpct, txt) = 0
            end
         else
            begin
               update #findtext
               set txt = substring (txt, patindex (@textpct, upper (txt)) - @length, 200)
               where patindex (@textpct, upper (txt)) > @length

               while (@seq < 6)
                  begin
                     insert into #findtext
                        select id, 0, name, colid, colid2, @seq,
                               substring (txt, 56, 200)
                        from #findtext
                        where seq                                 = @seq - 1
                          and substring (upper (txt), 56, 200) like @textpct

                     update #findtext
                     set txt = substring (txt, patindex (@textpct, upper (txt)) - @length, 200)
                     where seq                              = @seq
                       and patindex (@textpct, upper (txt)) > @length

                     select @seq = @seq + 1
                  end

               delete from #findtext
               where patindex (@textpct, upper (txt)) = 0
            end

         if (@CASEOFF_FLAG IS NULL)
            begin
               insert into #findtext
                  select object_id ('master.dbo.sysusermessages'),
                         0,
                         'sysusermessages: ' + convert (varchar (10), error),
                         0,
                         0,
                         1,
                         description
                  from sysusermessages
                  where description like @textpct

               if (db_id () = 1)
                  begin
                     insert into #findtext
                        select object_id ('master.dbo.sysmessages'),
                               0,
                               'sysmessages: ' + convert (varchar (10), error),
                               0,
                               0,
                               1,
                               description
                        from master.dbo.sysmessages
                        where description like @textpct
                  end
            end
         else
            begin
               insert into #findtext
                  select object_id ('master.dbo.sysusermessages'),
                         0,
                         'sysusermessages: ' + convert (varchar (10), error),
                         0,
                         0,
                         1,
                         description
                  from sysusermessages
                  where upper (description) like @textpct

               if (db_id () = 1)
                  begin
                     insert into #findtext
                        select object_id ('master.dbo.sysmessages'),
                               0,
                               'sysmessages: ' + convert (varchar (10), error),
                               0,
                               0,
                               1,
                               description
                        from master.dbo.sysmessages
                        where upper (description) like @textpct
                  end
            end

         select @oc = convert (char (10), convert (money, count (distinct id)), 1)
         from #findtext

         select @oc = ltrim (substring (@oc, 1, 7))

         print '    THERE ARE %1! OBJECTS CONTAINING <%2!>.', @oc, @TEXT

         print ' '

         if (exists (select *
                     from #findtext))
            begin
               while (exists (select *
                              from #findtext
                              where charindex (@CR, txt) > 0))
                  begin
                     update #findtext
                     set txt = substring (txt, 1, charindex (@CR, txt) - 1)
                               + ' '
                               + substring (txt, charindex (@CR, txt) + 1, 255)
                     where charindex (@CR, txt) > 0
                  end

               while (exists (select *
                              from #findtext
                              where charindex (@HT, txt) > 0))
                  begin
                     update #findtext
                     set txt = substring (txt, 1, charindex (@HT, txt) - 1)
                               + ' '
                               + substring (txt, charindex (@HT, txt) + 1, 255)
                     where charindex (@HT, txt) > 0
                  end

               while (exists (select *
                              from #findtext
                              where charindex (@LF, txt) > 0))
                  begin
                     update #findtext
                     set txt = substring (txt, 1, charindex (@LF, txt) - 1)
                               + ' '
                               + substring (txt, charindex (@LF, txt) + 1, 255)
                     where charindex (@LF, txt) > 0
                  end

               update #findtext
               set uid = o.uid
               from #findtext ft,
                    sysobjects o
               where ft.id = o.id

               update #findtext
               set name = substring (ft.name + ' (' + user_name (ft.uid) + ')', 1, 30)
               from #findtext ft
               where ft.uid <> 1
                  or 1       < (select count (o.uid)
                                from sysobjects o
                                where o.name = ft.name)

               select @maxobjlen = max (char_length (name))
               from #findtext

               if (@maxobjlen < 19)
                  begin
                     select 'OBJECT' = substring (name, 1, 18),
                            'TEXT'   = substring (txt, 1, 58)
                     from #findtext
                     order by name, colid, colid2, seq
                  end
               else if (@maxobjlen < 24)
                  begin
                     select 'OBJECT' = substring (name, 1, 23),
                            'TEXT'   = substring (txt, 1, 53)
                     from #findtext
                     order by name, colid, colid2, seq
                  end
               else
                  begin
                     select 'OBJECT' = substring (name, 1, 27),
                            'TEXT'   = substring (txt, 1, 49)
                     from #findtext
                     order by name, colid, colid2, seq
                  end
            end
         else if (@oc <> '0')
            begin
               print '    THERE IS NO NON-TABLE OBJECT CONTAINING <%1!>.', @TEXT

               print ' '
            end
      end
go
sp_addcomment sp_findtext, 1, 'Lists non-table objects containing (case-sensitive) TEXT'
go
sp_addcomment sp_findtext, 1, '   non-null NOCASE_FLAG provides case-insensitive search'
go
sp_procxmode sp_findtext, anymode
go


create procedure sp_sybprocs (@TEXT varchar (20) = 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_sybprocs> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   if (exists (select *
               from sybsyntax.dbo.sybsyntax
               where module         = 'System Procedure'
                 and commandname like 'sp[_]%' + @TEXT + '%'))
      begin
            select 'STORED PROCEDURE'   = space (17),
                   'SYBASE DESCRIPTION' = space (59)
            where 1 = 2
         union
            select substring (s1.commandname, 4, 17),
                   ltrim (substring (s1.syntax, 2 + charindex ('-', s1.syntax), 59))
            from sybsyntax.dbo.sybsyntax s1
            where s1.module         = 'System Procedure'
              and s1.commandname like 'sp[_]%' + @TEXT + '%'
              and s1.linenum        = 1
              and not exists (select s2.*
                              from sybsyntax.dbo.sybsyntax s2
                              where s2.commandname = s1.commandname
                                and s2.linenum     = 2)
         union
            select substring (s1.commandname, 4, 17),
                   substring (s1.syntax, 2 + charindex ('-', s1.syntax), 59)
            from sybsyntax.dbo.sybsyntax s1,
                 sybsyntax.dbo.sybsyntax s2
            where s1.module         = 'System Procedure'
              and s1.commandname like 'sp[_]%' + @TEXT + '%'
              and s1.linenum        = 1
              and s1.commandname    = s2.commandname
              and s2.linenum        = 2
              and charindex (s2.commandname, s2.syntax) > 0
         union
            select substring (s1.commandname, 4, 17),
                   substring (substring (s1.syntax,
                                         2 + charindex ('-', s1.syntax),
                                         70)
                              + ' '
                              + ltrim (s2.syntax),
                              1,
                              59)
            from sybsyntax.dbo.sybsyntax s1,
                 sybsyntax.dbo.sybsyntax s2
            where s1.module         = 'System Procedure'
              and s1.commandname like 'sp[_]%' + @TEXT + '%'
              and s1.linenum        = 1
              and s1.commandname    = s2.commandname
              and s2.linenum        = 2
              and charindex (s2.commandname, s2.syntax) = 0
         order by 'STORED PROCEDURE'
      end
   else
      begin
         print ' '

         print '    THERE ARE NO SYBASE UTILITY PROCEDURES LIKE sp_%%%1!%%.',
               @TEXT

         print ' '
      end
go
sp_addcomment sp_sybprocs, 1, 'Lists Sybase utility procedures like sp_%TEXT% in name order'
go
sp_procxmode sp_sybprocs, anymode
go

create procedure sp_sysprocsdetail (@CLASS tinyint,
                                    @TEXT  varchar (19) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   create table #procsdetail
     (spname  varchar (16)  not null,
      seq     tinyint       not null,
      txt     varchar (60)  not null,
      sort    varchar (32)  not null)

   if (@TEXT IS NULL)
      begin
         insert into #procsdetail
            select substring (u.spname, 4, 16),
                   1,
                   substring (u.txt, 1, 60),
                   u.spname
            from sybsystemprocs.dbo.udescriptions u,
                 sybsystemprocs.dbo.sysobjects o
            where (o.sysstat & 15) = 4
              and o.id             = u.objid
              and u.seq            = 1
              and u.class          = @CLASS
              and 1                = (select count (u2.seq)
                                      from sybsystemprocs.dbo.udescriptions u2
                                      where u2.spname = u.spname)

         insert into #procsdetail
            select substring (u.spname + ' ##', 4, 16),
                   1,
                   substring (u.txt, 1, 60),
                   u.spname
            from sybsystemprocs.dbo.udescriptions u,
                 sybsystemprocs.dbo.sysobjects o
            where (o.sysstat & 15) = 4
              and o.id             = u.objid
              and u.seq            = 1
              and u.class          = @CLASS
              and 1                < (select count (u2.seq)
                                      from sybsystemprocs.dbo.udescriptions u2
                                      where u2.spname = u.spname)
      end
   else
      begin
         set nocount on

         insert into #procsdetail
            select substring (u.spname, 4, 16),
                   u.seq,
                   substring (u.txt, 1, 60),
                   u.spname
            from sybsystemprocs.dbo.udescriptions u,
                 sybsystemprocs.dbo.sysobjects o
            where u.spname like 'sp[_]%' + lower (@TEXT) + '%'
              and o.id             = u.objid
              and (o.sysstat & 15) = 4

         update #procsdetail
         set spname = ' '
         where seq > 1
      end

   if (exists (select *
               from #procsdetail))
      begin
         select 'PROCEDURE' = spname,
                DESCRIPTION = txt
         from #procsdetail
         order by sort, seq
      end
   else
      begin
         print '    THERE ARE NO UTILITY PROCEDURES LIKE sp_%%%1!%%.', @TEXT

         print ' '
      end
go
sp_procxmode sp_sysprocsdetail, unchained
go

create procedure sp_sysprocs (@TEXT  varchar (19) = 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 ' '

         print '    <sp_sysprocs> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   exec sp_sysprocsdetail 1, @TEXT
go
sp_addcomment sp_sysprocs, 1, 'Lists root (default) utility procedures or utility procedures like sp_%TEXT%'
go
sp_procxmode sp_sysprocs, anymode
go

create procedure sp_sysprocs_DBA 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_sysprocs_DBA> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   exec sp_sysprocsdetail 11
go
sp_addcomment sp_sysprocs_DBA, 1, 'Lists utility procedures mostly used by the DBA'
go
sp_procxmode sp_sysprocs_DBA, anymode
go

create procedure sp_sysprocs_group  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_sysprocs_group> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   exec sp_sysprocsdetail 12
go
sp_addcomment sp_sysprocs_group, 1, 'Lists utility procedures concerning Groups/Members/Roles'
go
sp_procxmode sp_sysprocs_group, anymode
go

create procedure sp_sysprocs_index  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_sysprocs_index> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   exec sp_sysprocsdetail 13
go
sp_addcomment sp_sysprocs_index, 1, 'Lists utility procedures concerning Indexes'
go
sp_procxmode sp_sysprocs_index, anymode
go

create procedure sp_sysprocs_monitor  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_sysprocs_monitor> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   exec sp_sysprocsdetail 18
go
sp_addcomment sp_sysprocs_monitor, 1, 'Lists utility procedures concerning Performance Monitoring'
go
sp_procxmode sp_sysprocs_monitor, anymode
go

create procedure sp_sysprocs_object  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_sysprocs_object> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   exec sp_sysprocsdetail 14
go
sp_addcomment sp_sysprocs_object, 1, 'Lists utility procedures concerning Objects'
go
sp_procxmode sp_sysprocs_object, anymode
go

create procedure sp_sysprocs_protect  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_sysprocs_protect> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   exec sp_sysprocsdetail 15
go
sp_addcomment sp_sysprocs_protect, 1, 'Lists utility procedures concerning Permissions'
go
sp_procxmode sp_sysprocs_protect, anymode
go

create procedure sp_sysprocs_system  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_sysprocs_system> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   exec sp_sysprocsdetail 16
go
sp_addcomment sp_sysprocs_system, 1, 'Lists utility procedures concerning System Interactions'
go
sp_procxmode sp_sysprocs_system, anymode
go

create procedure sp_sysprocs_xref  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_sysprocs_xref> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   exec sp_sysprocsdetail 17
go
sp_addcomment sp_sysprocs_xref, 1, 'Lists utility procedures concerning Object details/interactions'
go
sp_procxmode sp_sysprocs_xref, anymode
go

dump transaction sybsystemprocs with truncate_only
go
