use sybsystemprocs
go

drop procedure sp_backexec
go
drop procedure sp_backexecall
go
drop procedure sp_roletree
go
drop procedure sp_roletrees
go
drop procedure sp_tree
go
drop procedure sp_trees
go

set quoted_identifier on
go

create procedure sp_backexec (@SP      varchar (30),
                              @FORMAT  tinyint      = 1) as
   set ansinull                      on
   set close on endtran              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_backexec> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @columns    tinyint,        -- number of columns to be displayed
           @currsp     char (32),      -- current sp being processed
           @errorchar  varchar ( 4),   -- print version of @@sqlstatus
           @index      tinyint,        -- indexing variable for column alignment
           @seq        tinyint,        -- current column being processed
           @spmaxlen   tinyint         -- 'width' of columns displayed

   create table #directcall
     (tlevel      tinyint                   not null,
      callee      int                       not null,
      calleename  char (32)                 not null,
      caller      int                       not null,
      callername  varchar (33)              not null)

   create table #texttable
     (seq         tinyint                   not null,
      text        varchar (77)              not null)

   declare sp_cursor cursor for
      select distinct callername
      from #directcall
      order by callername
   for read only

   declare spdirect_cursor cursor for
      select callername
      from #directcall
      where tlevel = 0
      order by callername
   for read only

   declare spindirect_cursor cursor for
      select distinct dc.callername
      from #directcall dc
      where dc.tlevel > 0
        and (exists (select dcx.*
                     from #directcall dcx
                     where dcx.calleename = dc.callername))
      order by dc.callername
   for read only

   declare sphighest_cursor cursor for
      select distinct dc.callername
      from #directcall dc
      where (not exists (select dch.*
                         from #directcall dch
                         where dch.calleename = dc.callername))
      order by dc.callername
   for read only

   if (exists (select object_id (@SP)))
      begin
         if (exists (select *
                     from sysobjects
                     where id             = object_id (@SP)
                       and (sysstat & 15) = 4))
            begin
               insert into #directcall (tlevel, callee, calleename, caller,
                                        callername)
                  select 0, d.depid, @SP, d.id, object_name (d.id)
                  from sysdepends d,
                       sysobjects o
                  where d.depid          = object_id (@SP)
                    and d.id             = o.id
                    and (o.sysstat & 15) = 4

               if (exists (select *
                           from #directcall))
                  begin
                     select @index = 1

                     while (exists (select *
                                    from #directcall
                                    where tlevel  = @index - 1
                                      and callee IS NOT NULL))
                        begin
                           insert into #directcall (tlevel, callee, calleename,
                                                    caller, callername)
                              select distinct @index,
                                              dc.caller,
                                              dc.callername,
                                              d.id,
                                              object_name (d.id)
                              from sysdepends d,
                                   sysobjects o,
                                   #directcall dc
                              where dc.tlevel        = @index - 1
                                and d.depid          = dc.caller
                                and o.id             = d.id
                                and (o.sysstat & 15) = 4

                           select @index = @index + 1
                        end

                     If (@FORMAT in (0, 1))
                        begin
                           If (@FORMAT = 1)
                              begin
                                 print '    NOTE: $ = direct call, @ = highest level stored procedure'

                                 print ' '
                              end

                           -- Mark top level procedures

                           update #directcall
                           set dc.callername = dc.callername + ' @'
                           from #directcall dc
                           where (not exists (select dcp.*
                                              from #directcall dcp
                                              where dcp.calleename = dc.callername))

                           -- Mark direct executes.  Note the redundant and
                           -- confusing Sybase syntax.

                           delete #directcall
                           from #directcall dc
                           where dc.tlevel > 0
                             and (exists (select dcx.*
                                          from #directcall dcx
                                          where dcx.tlevel = 0
                                            and dcx.callername = dc.callername))

                           update #directcall
                           set callername = callername + ' $'
                           where tlevel = 0

                           update #directcall
                           set callername = stuff (callername, charindex ('@ $', callername), 3, '@$')
                           where tlevel                         = 0
                             and charindex ('@ $', callername) > 0

                           -- Determine the maximum stored procedure name length
                           -- and the number of columns to be printed.

                           select @spmaxlen = max (char_length (callername)) + 4
                           from #directcall

                           select @columns = 77 / @spmaxlen

                           select @seq = 0

                           open sp_cursor

                           fetch sp_cursor into @currsp

                           while (@@sqlstatus = 0)
                              begin
                                 select @index = 1

                                 insert into #texttable values (@seq, @currsp)

                                 fetch sp_cursor into @currsp

                                 while ((@@sqlstatus = 0)  and  (@index < @columns))
                                    begin
                                       update #texttable
                                       set text = substring (text
                                                             + space ((@index * @spmaxlen) - char_length (text))
                                                             + @currsp,
                                                             1,
                                                             77)
                                       where seq = @seq

                                       select @index = @index + 1

                                       fetch sp_cursor into @currsp
                                    end

                                 select @seq = @seq + 1
                              end

                           if (@@sqlstatus <> 2)
                              begin
                                 select @errorchar = convert (varchar (4), @@sqlstatus)

                                 print '  CURSOR ERROR.  STATUS = %1!.', @errorchar

                                 return
                              end

                           close sp_cursor

                           select 'EXECUTE BACK CALLS' = text
                           from #texttable
                           order by seq
                        end
                     else if (@FORMAT = 2)
                        begin
                           -- Determine the maximum stored procedure name length
                           -- and the number of columns to be printed.

                           select @spmaxlen = max (char_length (callername)) + 4
                           from #directcall

                           select @columns = 77 / @spmaxlen

                           -- Load/Format #texttable for direct calls

                           select @seq = 0

                           open spdirect_cursor

                           fetch spdirect_cursor into @currsp

                           while (@@sqlstatus = 0)
                              begin
                                 select @index = 1

                                 insert into #texttable values (@seq, @currsp)

                                 fetch spdirect_cursor into @currsp

                                 while ((@@sqlstatus = 0)  and  (@index < @columns))
                                    begin
                                       update #texttable
                                       set text = substring (text
                                                             + space ((@index * @spmaxlen) - char_length (text))
                                                             + @currsp,
                                                             1,
                                                             77)
                                       where seq = @seq

                                       select @index = @index + 1

                                       fetch spdirect_cursor into @currsp
                                    end

                                 select @seq = @seq + 1
                              end

                           if (@@sqlstatus <> 2)
                              begin
                                 select @errorchar = convert (varchar (4), @@sqlstatus)

                                 print '  DIRECT CURSOR ERROR.  STATUS = %1!.', @errorchar

                                 return
                              end

                           close spdirect_cursor

                           select 'DIRECT BACK EXECUTES' = text
                           from #texttable
                           order by seq

                           print ' '

                           delete from #texttable

                           select @seq = 0

                           select @index = 1

                           open spindirect_cursor

                           fetch spindirect_cursor into @currsp

                           while (@@sqlstatus = 0)
                              begin
                                 select @index = 1

                                 insert into #texttable values (@seq, @currsp)

                                 fetch spindirect_cursor into @currsp

                                 while ((@@sqlstatus = 0)  and  (@index < @columns))
                                    begin
                                       update #texttable
                                       set text = substring (text
                                                             + space ((@index * @spmaxlen) - char_length (text))
                                                             + @currsp,
                                                             1,
                                                             77)
                                       where seq = @seq

                                       select @index = @index + 1

                                       fetch spindirect_cursor into @currsp
                                    end

                                 select @seq = @seq + 1
                              end

                           if (@@sqlstatus <> 2)
                              begin
                                 select @errorchar = convert (varchar (4), @@sqlstatus)

                                 print '  INDIRECT CURSOR ERROR.  STATUS = %1!.', @errorchar

                                 return
                              end

                           if (exists (select *
                                       from #texttable))
                              begin
                                 select 'INDIRECT BACK EXECUTES' = text
                                 from #texttable
                                 order by seq
                              end
                           else
                              begin
                                 print ' '

                                 print '    THERE ARE NO INDIRECT BACK EXECUTES (OTHER THAN AT HIGHEST LEVEL).'

                                 print ' '
                              end

                           close spindirect_cursor

                           delete from #texttable

                           select @seq = 0

                           open sphighest_cursor

                           fetch sphighest_cursor into @currsp

                           while (@@sqlstatus = 0)
                              begin
                                 select @index = 1

                                 insert into #texttable values (@seq, @currsp)

                                 fetch sphighest_cursor into @currsp

                                 while ((@@sqlstatus = 0)  and  (@index < @columns))
                                    begin
                                       update #texttable
                                       set text = substring (text
                                                             + space ((@index * @spmaxlen) - char_length (text))
                                                             + @currsp,
                                                             1,
                                                             77)
                                       where seq = @seq

                                       select @index = @index + 1

                                       fetch sphighest_cursor into @currsp
                                    end

                                 select @seq = @seq + 1
                              end

                           if (@@sqlstatus <> 2)
                              begin
                                 select @errorchar = convert (varchar (4), @@sqlstatus)

                                 print '  HIGHEST CURSOR ERROR.  STATUS = %1!.', @errorchar

                                 return
                              end

                           close sphighest_cursor

                           print ' '

                           select 'HIGHEST BACK EXECUTES' = text
                           from #texttable
                           order by seq
                        end
                     else
                        begin
                           print '    FORMAT <%1!> IS INVALID.  VALUE MUST BE 0, 1, OR 2.',
                                 @FORMAT

                           print ' '
                        end
                  end
               else
                  begin
                     print '    <%1!> IS NOT EXECUTED BY ANY STORED PROCEDURE.', @SP

                     print ' '
                  end
            end
         else
            begin
               print '    <%1!> IS NOT A STORED PROCEDURE.', @SP

               print ' '
            end
      end
   else
      begin
         print '    OBJECT <%1!> DOES NOT EXIST.', @SP

         print ' '
      end
go
sp_addcomment sp_backexec, 17, 'Displays calling stored procedures for STORED PROCEDURE with FORMAT option'
go
sp_addcomment sp_backexec, 17, '   FORMAT = 0: the symbol note will not be printed'
go
sp_addcomment sp_backexec, 17, '   FORMAT = 1: the symbol note will be printed (default)'
go
sp_addcomment sp_backexec, 17, '   FORMAT = 2: three sections will be listed:'
go
sp_addcomment sp_backexec, 17, '     (1) directly calling stored procedures'
go
sp_addcomment sp_backexec, 17, '     (2) indirectly calling stored procedures'
go
sp_addcomment sp_backexec, 17, '     (3) highest level calling stored procedures'
go
sp_procxmode sp_backexec, anymode
go

create procedure sp_backexecall as
   set ansinull                      on
   set close on endtran              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_backexecall> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @createdate   char    ( 8),
           @errorchar    varchar ( 4),
           @existsflag   tinyint,
           @maxownsplen  tinyint,
           @owner        varchar (30),
           @ownersp      varchar (63),
           @printmssg    varchar (70),
           @spname       varchar (30),
           @uidcount     tinyint

   declare sp_cursor cursor for
      select distinct o2.name
      from sysdepends d,
           sysobjects o1,
           sysobjects o2
      where (o1.sysstat & 15) = 4
        and d.id              = o1.id
        and d.depid           = o2.id
        and (o2.sysstat & 15) = 4
      order by o2.name
   for read only

   declare owner_cursor cursor for
      select user_name (uid), convert (char (8), crdate, 11)
      from sysobjects
      where name           = @spname
        and (sysstat & 15) = 4
      order by user_name (uid)
   for read only

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

         print ' '

         update statistics sysdepends

         update statistics sysobjects
      end

   select @maxownsplen = 2 + max (datalength (user_name (uid) + name)),
          @existsflag  = 0
   from sysobjects
   where (sysstat & 15) = 4

   open sp_cursor

   fetch sp_cursor into @spname

   if (@@sqlstatus = 0)
     begin
        print '    NOTE: $ = direct call, @ = highest level stored procedure'

        print ' '
     end

   while (@@sqlstatus = 0)
      begin
         select @uidcount   = count (*),
                @existsflag = 1
         from sysobjects
         where name           = @spname
           and (sysstat & 15) = 4

         if (@uidcount = 1)
            begin
               select @createdate = convert (char (8), crdate, 11),
                      @owner      = user_name (uid)
               from sysobjects
               where name           = @spname
                 and (sysstat & 15) = 4

               if (@owner = 'dbo')
                  begin
                     select @ownersp = @spname
                  end
               else
                  begin
                     select @ownersp = @owner + '.' + @spname
                  end

               select @printmssg = @ownersp
                                   + space (@maxownsplen - datalength (@ownersp))
                                   + '(' + @createdate + ')'

               print ' '

               print ' '

               print '    %1!', @printmssg

               execute sp_backexec @ownersp, 0
            end
         else
            begin
               open owner_cursor

               fetch owner_cursor into @owner, @createdate

               while (@@sqlstatus = 0)
                  begin
                     select @ownersp = @owner + '.' + @spname

                     select @printmssg = @ownersp
                                         + space (@maxownsplen - datalength (@owner) - datalength (@spname))
                                         + '(' + @createdate + ')'

                     print ' '

                     print ' '

                     print '    %1!', @printmssg

                     execute sp_backexec @ownersp, 0

                     fetch owner_cursor into @owner, @createdate
                  end

               if (@@sqlstatus <> 2)
                  begin
                     select @errorchar = convert (varchar (4), @@sqlstatus)

                     print '  OWNER CURSOR ERROR.  STATUS = %1!.', @errorchar

                     return
                  end

               close owner_cursor
            end

         fetch sp_cursor into @spname
      end

   if (@@sqlstatus = 2)
      begin
         if (@existsflag = 0)
            begin
               print '    THERE ARE NO SUBROUTINE STORED PROCEDURES.'

               print ' '
            end
      end
   else
      begin
         select @errorchar = convert (varchar (4), @@sqlstatus)

         print '  SP CURSOR ERROR.  STATUS = %1!.', @errorchar
      end

   deallocate cursor owner_cursor

   close sp_cursor

   deallocate cursor sp_cursor
go
sp_addcomment sp_backexecall, 17, 'sp_backexec for all subroutine stored procedures'
go
sp_procxmode sp_backexecall, anymode
go

create procedure sp_roletree (@ROLE varchar (30)) 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_roletree> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @attribclass  smallint,        -- class value for roles
           @attribid     smallint,        -- attribute id for roles
           @level        tinyint,         -- indexing variable for while loops
           @maxlevels    tinyint,         -- maximum number of levels (constant)
           @rolemaxlevel tinyint,         -- number of role levels displayed
           @rolemaxlen   tinyint,         -- width of columns displayed
           @textlen      tinyint          -- width of text output

   create table #directrole
     (tlevel      tinyint                   not null,
      parentid    int                       not null,
      parentname  varchar (30)              not null,
      roleid      int                       not null,
      rolename    varchar (30)              not null)

   create table #roletree
     (level1      char (32)     default ''  not null,
      level2      char (32)     default ''  not null,
      level3      char (32)     default ''  not null,
      level4      char (32)     default ''  not null,
      level5      char (32)     default ''  not null,
      level6      char (32)     default ''  not null,
      level7      char (32)     default ''  not null,
      level8      char (32)     default ''  not null,
      level9      char (32)     default ''  not null,
      level1x     varchar (32)  default ''  not null,
      level2x     varchar (32)  default ''  not null,
      level3x     varchar (32)  default ''  not null,
      level4x     varchar (32)  default ''  not null,
      level5x     varchar (32)  default ''  not null,
      level6x     varchar (32)  default ''  not null,
      level7x     varchar (32)  default ''  not null,
      level8x     varchar (32)  default ''  not null,
      level9x     varchar (32)  default ''  not null,
      text        char (77)     default ''  not null)

   create table #sizeparms
     (tlevel      tinyint                   not null,
      maxrolelen  tinyint                   not null)

   select @attribid    = attribute,
          @attribclass = class,
          @maxlevels   = 8,
          @textlen     = 77
   from master.dbo.sysattributes
   where object_type = 'UR'

   select @level = 1
   from master.dbo.syssrvroles
   where name = @ROLE

   if (@attribclass IS NULL)
      begin
         print '    THERE ARE NO NON-SYSTEM ROLES.'

         print ' '
      end
   else if (@level = 1)
      begin
         if (@textlen <> col_length ('#roletree', 'text'))
            begin
               print '    LOCAL VARIABLE @textlen MUST EQUAL THE LENGTH OF #roletree.text'

               print ' '

               print '    ADJUST THE SOURCE CODE AND RECOMPILE.'

               print ' '

               return (1)
            end

         insert into #directrole
            select 0,
                   role_id (@ROLE),
                   @ROLE,
                   object_info1,
                   role_name (object_info1)
            from master.dbo.sysattributes
            where object    = role_id (@ROLE)
              and class     = @attribclass
              and attribute = @attribid

         while (exists (select *
                        from #directrole
                        where @level      < @maxlevels + 2
                          and tlevel      = @level - 1
                          and roleid IS NOT NULL))
            begin
               insert into #directrole
                  select @level,
                         dc.roleid,
                         dc.rolename,
                         a.object_info1,
                         role_name (a.object_info1)
                  from master.dbo.sysattributes a,
                       #directrole dc
                  where dc.tlevel   = @level - 1
                    and a.object    = dc.roleid
                    and a.class     = @attribclass
                    and a.attribute = @attribid

               select @level = @level + 1
            end

         update #directrole
         set rolename = rolename + ' #'
         from master.dbo.syssrvroles sr,
              #directrole dr
         where dr.rolename      = sr.name
           and sr.password IS NOT NULL

         -- Determine the maximum role name length and the number of
         -- levels to be printed.

         select @level = max (tlevel) + 1
         from #directrole

         if (@level >= @maxlevels)
            begin
               print '    <%1!> HAS %2! LEVELS.  sp_roletree WILL ONLY DISPLAY %3! LEVELS.',
                     @ROLE, @level, @maxlevels

               print ' '

               delete from #directrole
               where tlevel >= @maxlevels
            end

         insert into #sizeparms
            select tlevel, max (char_length (rolename))
            from #directrole
            group by tlevel

         select @rolemaxlevel = 1

         while (@rolemaxlevel <= @level)
            begin
               update #sizeparms
               set maxrolelen = (select max (sp2.maxrolelen)
                                 from #sizeparms sp2
                                 where sp2.tlevel <= @rolemaxlevel)
               from #sizeparms sp
               where sp.tlevel = @rolemaxlevel

               select @rolemaxlevel = @rolemaxlevel + 1
            end

         select @rolemaxlevel= max (tlevel)
         from #sizeparms
         where (tlevel * (1 + maxrolelen)) < @textlen + 1

         if (@rolemaxlevel = 0)
            begin
               select @rolemaxlen = 74
            end
         else
            begin
               select @rolemaxlen = (@textlen / @rolemaxlevel) - 1
            end

         if (@rolemaxlen > (select (max (maxrolelen) + 1)
                            from #sizeparms
                            where tlevel = @rolemaxlevel))
            begin
               select @rolemaxlen = (select (max (maxrolelen) + 1)
                                     from #sizeparms
                                     where tlevel = @rolemaxlevel)
            end

         if (@rolemaxlevel > 7)
            begin
               insert into #roletree (level1, level2, level3, level4,
                                      level5, level6, level7, level8,
                                      level9)
                  select dc0.rolename, dc1.rolename, dc2.rolename,
                         dc3.rolename, dc4.rolename, dc5.rolename,
                         dc6.rolename, dc7.rolename, dc8.rolename
                  from #directrole dc0,
                       #directrole dc1,
                       #directrole dc2,
                       #directrole dc3,
                       #directrole dc4,
                       #directrole dc5,
                       #directrole dc6,
                       #directrole dc7,
                       #directrole dc8
                  where dc0.tlevel = 0
                    and dc0.roleid = dc1.parentid
                    and dc1.tlevel = 1
                    and dc1.roleid = dc2.parentid
                    and dc2.tlevel = 2
                    and dc2.roleid = dc3.parentid
                    and dc3.tlevel = 3
                    and dc3.roleid = dc4.parentid
                    and dc4.tlevel = 4
                    and dc4.roleid = dc5.parentid
                    and dc5.tlevel = 5
                    and dc5.roleid = dc6.parentid
                    and dc6.tlevel = 6
                    and dc6.roleid = dc7.parentid
                    and dc7.tlevel = 7
                    and dc7.roleid = dc8.parentid
                    and dc8.tlevel = 8
                    and not (exists (select dc9.*
                                     from #directrole dc9
                                     where dc9.tlevel   = 9
                                       and dc9.parentid = dc8.roleid))
            end

         if (@rolemaxlevel > 6)
            begin
               insert into #roletree (level1, level2, level3, level4,
                                      level5, level6, level7, level8)
                  select dc0.rolename, dc1.rolename, dc2.rolename,
                         dc3.rolename, dc4.rolename, dc5.rolename,
                         dc6.rolename, dc7.rolename
                  from #directrole dc0,
                       #directrole dc1,
                       #directrole dc2,
                       #directrole dc3,
                       #directrole dc4,
                       #directrole dc5,
                       #directrole dc6,
                       #directrole dc7
                  where dc0.tlevel = 0
                    and dc0.roleid = dc1.parentid
                    and dc1.tlevel = 1
                    and dc1.roleid = dc2.parentid
                    and dc2.tlevel = 2
                    and dc2.roleid = dc3.parentid
                    and dc3.tlevel = 3
                    and dc3.roleid = dc4.parentid
                    and dc4.tlevel = 4
                    and dc4.roleid = dc5.parentid
                    and dc5.tlevel = 5
                    and dc5.roleid = dc6.parentid
                    and dc6.tlevel = 6
                    and dc6.roleid = dc7.parentid
                    and dc7.tlevel = 7
                    and not (exists (select dc8.*
                                     from #directrole dc8
                                     where dc8.tlevel   = 8
                                       and dc8.parentid = dc7.roleid))
            end

         if (@rolemaxlevel > 5)
            begin
               insert into #roletree (level1, level2, level3, level4, level5,
                                      level6, level7)
                  select dc0.rolename, dc1.rolename, dc2.rolename,
                         dc3.rolename, dc4.rolename, dc5.rolename,
                         dc6.rolename
                  from #directrole dc0,
                       #directrole dc1,
                       #directrole dc2,
                       #directrole dc3,
                       #directrole dc4,
                       #directrole dc5,
                       #directrole dc6
                  where dc0.tlevel = 0
                    and dc0.roleid = dc1.parentid
                    and dc1.tlevel = 1
                    and dc1.roleid = dc2.parentid
                    and dc2.tlevel = 2
                    and dc2.roleid = dc3.parentid
                    and dc3.tlevel = 3
                    and dc3.roleid = dc4.parentid
                    and dc4.tlevel = 4
                    and dc4.roleid = dc5.parentid
                    and dc5.tlevel = 5
                    and dc5.roleid = dc6.parentid
                    and dc6.tlevel = 6
                    and not (exists (select dc7.*
                                     from #directrole dc7
                                     where dc7.tlevel   = 7
                                       and dc7.parentid = dc6.roleid))
            end

         if (@rolemaxlevel > 4)
            begin
               insert into #roletree (level1, level2, level3, level4, level5,
                                      level6)
                  select dc0.rolename, dc1.rolename, dc2.rolename,
                         dc3.rolename, dc4.rolename, dc5.rolename
                  from #directrole dc0,
                       #directrole dc1,
                       #directrole dc2,
                       #directrole dc3,
                       #directrole dc4,
                       #directrole dc5
                  where dc0.tlevel = 0
                    and dc0.roleid = dc1.parentid
                    and dc1.tlevel = 1
                    and dc1.roleid = dc2.parentid
                    and dc2.tlevel = 2
                    and dc2.roleid = dc3.parentid
                    and dc3.tlevel = 3
                    and dc3.roleid = dc4.parentid
                    and dc4.tlevel = 4
                    and dc4.roleid = dc5.parentid
                    and dc5.tlevel = 5
                    and not (exists (select dc6.*
                                     from #directrole dc6
                                     where dc6.tlevel   = 6
                                       and dc6.parentid = dc5.roleid))
            end

         if (@rolemaxlevel > 3)
            begin
               insert into #roletree (level1, level2, level3, level4, level5)
                  select dc0.rolename, dc1.rolename, dc2.rolename,
                         dc3.rolename, dc4.rolename
                  from #directrole dc0,
                       #directrole dc1,
                       #directrole dc2,
                       #directrole dc3,
                       #directrole dc4
                  where dc0.tlevel = 0
                    and dc0.roleid = dc1.parentid
                    and dc1.tlevel = 1
                    and dc1.roleid = dc2.parentid
                    and dc2.tlevel = 2
                    and dc2.roleid = dc3.parentid
                    and dc3.tlevel = 3
                    and dc3.roleid = dc4.parentid
                    and dc4.tlevel = 4
                    and not (exists (select dc5.*
                                     from #directrole dc5
                                     where dc5.tlevel   = 5
                                       and dc5.parentid = dc4.roleid))
            end

         if (@rolemaxlevel > 2)
            begin
               insert into #roletree (level1, level2, level3, level4)
                  select dc0.rolename, dc1.rolename, dc2.rolename,
                         dc3.rolename
                  from #directrole dc0,
                       #directrole dc1,
                       #directrole dc2,
                       #directrole dc3
                  where dc0.tlevel = 0
                    and dc0.roleid = dc1.parentid
                    and dc1.tlevel = 1
                    and dc1.roleid = dc2.parentid
                    and dc2.tlevel = 2
                    and dc2.roleid = dc3.parentid
                    and dc3.tlevel = 3
                    and not (exists (select dc4.*
                                     from #directrole dc4
                                     where dc4.tlevel   = 4
                                       and dc4.parentid = dc3.roleid))
            end

         if (@rolemaxlevel > 1)
            begin
               insert into #roletree (level1, level2, level3)
                  select dc0.rolename, dc1.rolename, dc2.rolename
                  from #directrole dc0,
                       #directrole dc1,
                       #directrole dc2
                  where dc0.tlevel = 0
                    and dc0.roleid = dc1.parentid
                    and dc1.tlevel = 1
                    and dc1.roleid = dc2.parentid
                    and dc2.tlevel = 2
                    and not (exists (select dc3.*
                                     from #directrole dc3
                                     where dc3.tlevel   = 3
                                       and dc3.parentid = dc2.roleid))
            end

         insert into #roletree (level1, level2)
            select dc0.rolename, dc1.rolename
            from #directrole dc0,
                 #directrole dc1
            where dc0.tlevel = 0
              and dc0.roleid = dc1.parentid
              and dc1.tlevel = 1
              and not (exists (select dc2.*
                               from #directrole dc2
                               where dc2.tlevel   = 2
                                 and dc2.parentid = dc1.roleid))

         insert into #roletree (level1)
            select dc0.rolename
            from #directrole dc0
            where dc0.tlevel = 0
              and not (exists (select dc1.*
                               from #directrole dc1
                               where dc1.tlevel   = 1
                                 and dc1.parentid = dc0.roleid))

         update #roletree
         set level1x = level1,
             level2x = level2,
             level3x = level3,
             level4x = level4,
             level5x = level5,
             level6x = level6,
             level7x = level7,
             level8x = level8,
             level9x = level9

         update #roletree
         set e9.level1x = '',
             e9.level2x = '',
             e9.level3x = '',
             e9.level4x = '',
             e9.level5x = '',
             e9.level6x = '',
             e9.level7x = '',
             e9.level8x = ''
         from #roletree e9,
              #roletree et
         where e9.level1 = et.level1
           and e9.level2 = et.level2
           and e9.level3 = et.level3
           and e9.level4 = et.level4
           and e9.level5 = et.level5
           and e9.level6 = et.level6
           and e9.level7 = et.level7
           and e9.level8 = et.level8
           and e9.level9 > et.level9

         update #roletree
         set e8.level1x = '',
             e8.level2x = '',
             e8.level3x = '',
             e8.level4x = '',
             e8.level5x = '',
             e8.level6x = '',
             e8.level7x = ''
         from #roletree e8,
              #roletree et
         where e8.level1 = et.level1
           and e8.level2 = et.level2
           and e8.level3 = et.level3
           and e8.level4 = et.level4
           and e8.level5 = et.level5
           and e8.level6 = et.level6
           and e8.level7 = et.level7
           and e8.level8 > et.level8

         update #roletree
         set e7.level1x = '',
             e7.level2x = '',
             e7.level3x = '',
             e7.level4x = '',
             e7.level5x = '',
             e7.level6x = ''
         from #roletree e7,
              #roletree et
         where e7.level1 = et.level1
           and e7.level2 = et.level2
           and e7.level3 = et.level3
           and e7.level4 = et.level4
           and e7.level5 = et.level5
           and e7.level6 = et.level6
           and e7.level7 > et.level7

         update #roletree
         set e6.level1x = '',
             e6.level2x = '',
             e6.level3x = '',
             e6.level4x = '',
             e6.level5x = ''
         from #roletree e6,
              #roletree et
         where e6.level1 = et.level1
           and e6.level2 = et.level2
           and e6.level3 = et.level3
           and e6.level4 = et.level4
           and e6.level5 = et.level5
           and e6.level6 > et.level6

         update #roletree
         set e5.level1x = '',
             e5.level2x = '',
             e5.level3x = '',
             e5.level4x = ''
         from #roletree e5,
              #roletree et
         where e5.level1 = et.level1
           and e5.level2 = et.level2
           and e5.level3 = et.level3
           and e5.level4 = et.level4
           and e5.level5 > et.level5

         update #roletree
         set e4.level1x = '',
             e4.level2x = '',
             e4.level3x = ''
         from #roletree e4,
              #roletree et
         where e4.level1 = et.level1
           and e4.level2 = et.level2
           and e4.level3 = et.level3
           and e4.level4 > et.level4

         update #roletree
         set e3.level1x = '',
             e3.level2x = ''
         from #roletree e3,
              #roletree et
         where e3.level1 = et.level1
           and e3.level2 = et.level2
           and e3.level3 > et.level3

         update #roletree
         set e2.level1x = ''
         from #roletree e2,
              #roletree et
         where e2.level1 = et.level1
           and e2.level2 > et.level2

         -- Since Sybase treats an empty space as a single space (!),
         -- the final text formatting becomes a real kludge:

         update #roletree
         set text = level1x
                    + space (@rolemaxlen - char_length (level1x))
                    + level2x
                    + space (@rolemaxlen - char_length (level2x))
                    + level3x
                    + space (@rolemaxlen - char_length (level3x))
                    + level4x
                    + space (@rolemaxlen - char_length (level4x))
                    + level5x
                    + space (@rolemaxlen - char_length (level5x))
                    + level6x
                    + space (@rolemaxlen - char_length (level6x))
                    + level7x
                    + space (@rolemaxlen - char_length (level7x))
                    + level8x
                    + space (@rolemaxlen - char_length (level8x))
                    + level9x
         where level1x  <> ' '

         update #roletree
         set text = space (@rolemaxlen)
                    + level2x
                    + space (@rolemaxlen - char_length (level2x))
                    + level3x
                    + space (@rolemaxlen - char_length (level3x))
                    + level4x
                    + space (@rolemaxlen - char_length (level4x))
                    + level5x
                    + space (@rolemaxlen - char_length (level5x))
                    + level6x
                    + space (@rolemaxlen - char_length (level6x))
                    + level7x
                    + space (@rolemaxlen - char_length (level7x))
                    + level8x
                    + space (@rolemaxlen - char_length (level8x))
                    + level9x
         where level1x  = ' '
           and level2x <> ' '

         update #roletree
         set text = space (2 * @rolemaxlen)
                    + level3x
                    + space (@rolemaxlen - char_length (level3x))
                    + level4x
                    + space (@rolemaxlen - char_length (level4x))
                    + level5x
                    + space (@rolemaxlen - char_length (level5x))
                    + level6x
                    + space (@rolemaxlen - char_length (level6x))
                    + level7x
                    + space (@rolemaxlen - char_length (level7x))
                    + level8x
         where level2x  = ' '
           and level3x <> ' '

         update #roletree
         set text = space (3 * @rolemaxlen)
                    + level4x
                    + space (@rolemaxlen - char_length (level4x))
                    + level5x
                    + space (@rolemaxlen - char_length (level5x))
                    + level6x
                    + space (@rolemaxlen - char_length (level6x))
                    + level7x
                    + space (@rolemaxlen - char_length (level7x))
                    + level8x
                    + space (@rolemaxlen - char_length (level8x))
                    + level9x
         where level3x  = ' '
           and level4x <> ' '

         update #roletree
         set text = space (4 * @rolemaxlen)
                    + level5x
                    + space (@rolemaxlen - char_length (level5x))
                    + level6x
                    + space (@rolemaxlen - char_length (level6x))
                    + level7x
                    + space (@rolemaxlen - char_length (level7x))
                    + level8x
                    + space (@rolemaxlen - char_length (level8x))
                    + level9x
         where level4x  = ' '
           and level5x <> ' '

         update #roletree
         set text = space (5 * @rolemaxlen)
                    + level6x
                    + space (@rolemaxlen - char_length (level6x))
                    + level7x
                    + space (@rolemaxlen - char_length (level7x))
                    + level8x
                    + space (@rolemaxlen - char_length (level8x))
                    + level9x
         where level5x  = ' '
           and level6x <> ' '

         update #roletree
         set text = space (6 * @rolemaxlen)
                    + level7x
                    + space (@rolemaxlen - char_length (level7x))
                    + level8x
                    + space (@rolemaxlen - char_length (level8x))
                    + level9x
         where level6x  = ' '
           and level7x <> ' '

         update #roletree
         set text = space (7 * @rolemaxlen)
                    + level8x
                    + space (@rolemaxlen - char_length (level8x))
                    + level9x
         where level7x  = ' '
           and level8x <> ' '

         update #roletree
         set text = space (8 * @rolemaxlen)
                    + level9x
         where level8x  = ' '
           and level9x <> ' '

         print ' '

         if (exists (select *
                     from #roletree))
            begin
               if (exists (select *
                           from #directrole
                           where charindex (' #', rolename) > 0))
                  begin
                     select 'ROLE TREE (# = password)' = text
                     from #roletree
                     order by level1, level2, level3, level4, level5, level6,
                              level7, level8, level9
                  end
               else
                  begin
                     select 'ROLE TREE' = text
                     from #roletree
                     order by level1, level2, level3, level4, level5, level6,
                              level7, level8, level9
                  end
            end
         else
            begin
               print '    <%1!> DOES NOT HAVE ANY SUBORDINATE ROLES.',
                     @ROLE

               print ' '
            end
      end
   else
      begin
         print '    ROLE <%1!> DOES NOT EXIST.', @ROLE

         print ' '
      end
go
sp_addcomment sp_roletree, 12, 'Displays role membership tree for ROLE'
go
sp_procxmode sp_roletree, anymode
go

create procedure sp_roletrees as
   set ansinull                      on
   set close on endtran              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_roletrees> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @attribclass  smallint,
           @attribid     smallint,
           @errorchar    varchar ( 4),
           @existsflag   tinyint,
           @roleid       int,
           @rolename     varchar (30)

   declare parent_role_cursor cursor for
      select distinct 'ROLE NAME' = role_name (a.object),
                      'ROLE ID'   = a.object
      from master.dbo.sysattributes a
      where a.attribute = @attribid
        and a.class     = @attribclass
        and not exists (select a2.object
                        from master.dbo.sysattributes a2
                        where a2.object_info1 = a.object
                          and a2.attribute    = @attribid
                          and a2.class        = @attribclass)
      order by 'ROLE NAME'
   for read only

   select @attribid    = attribute,
          @attribclass = class,
          @existsflag  = 0
   from master.dbo.sysattributes
   where object_type = 'UR'

   open parent_role_cursor

   fetch parent_role_cursor into @rolename, @roleid

   while (@@sqlstatus = 0)
      begin
         select @existsflag = 1

         print ' '

         if (role_id (@rolename) IS NULL)
            begin
               print '    ROLE ID <%1!> IN master.dbo.sysattributes IS INVALID.',
                     @roleid

               print ' '
            end
         else
            begin
               print '    %1!', @rolename

               execute sp_roletree @rolename
            end

         fetch parent_role_cursor into @rolename, @roleid
      end

   if (@@sqlstatus = 2)
      begin
         if (@existsflag = 0)
            begin
               print '    THERE ARE NO PARENT USER ROLES.'

               print ' '
            end
      end
   else
      begin
         select @errorchar = convert (varchar (4), @@sqlstatus)

         print '  CURSOR ERROR.  STATUS = %1!.', @errorchar
      end

   close parent_role_cursor

   deallocate cursor parent_role_cursor

   print ' '
go
sp_addcomment sp_roletrees, 12, 'sp_roletree for all non-trivial highest level user roles'
go
sp_procxmode sp_roletrees, anymode
go

create procedure sp_tree (@SP varchar (63)) 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_tree> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @id         int,             -- id of @SP
           @level      tinyint,         -- indexing variable for while loops
           @maxlevels  tinyint,         -- maximum number of levels (constant)
           @recursive  tinyint,         -- recursive flag
           @spmaxlevel tinyint,         -- number of call levels displayed
           @spmaxlen   tinyint,         -- width of columns displayed
           @textlen    tinyint,         -- width of text output
           @uid        smallint         -- uid of @SP

   create table #directcall
     (tlevel      tinyint                   not null,
      caller      int                       not null,
      callername  char (63)                 null,
      callee      int                       null,
      calleename  char (63)                 null)

   create table #exectree
     (level1      char (32)     default ''  not null,
      level2      char (32)     default ''  not null,
      level3      char (32)     default ''  not null,
      level4      char (32)     default ''  not null,
      level5      char (32)     default ''  not null,
      level6      char (32)     default ''  not null,
      level7      char (32)     default ''  not null,
      level8      char (32)     default ''  not null,
      level9      char (32)     default ''  not null,
      level1x     varchar (32)  default ''  not null,
      level2x     varchar (32)  default ''  not null,
      level3x     varchar (32)  default ''  not null,
      level4x     varchar (32)  default ''  not null,
      level5x     varchar (32)  default ''  not null,
      level6x     varchar (32)  default ''  not null,
      level7x     varchar (32)  default ''  not null,
      level8x     varchar (32)  default ''  not null,
      level9x     varchar (32)  default ''  not null,
      text        char (77)     default ''  not null)

   create table #sizeparms
     (tlevel      tinyint                   not null,
      spmaxlen    tinyint                   not null)

   select @id        = object_id (@SP),
          @maxlevels = 8,
          @recursive = 0,
          @textlen   = 77

   if (@id IS NULL)
      begin
         print '    OBJECT <%1!> DOES NOT EXIST.', @SP

         print ' '
      end
   else
      begin
         if (@textlen <> col_length ('#exectree', 'text'))
            begin
               print '    LOCAL VARIABLE @textlen MUST EQUAL THE LENGTH OF #exectree.text'

               print ' '

               print '    ADJUST THE SOURCE CODE AND RECOMPILE.'

               print ' '

               return (1)
            end

         select @level = 1,
                @uid   = uid
         from sysobjects
         where id             = @id
           and (sysstat & 15) = 4

         if (@uid IS NULL)
            begin
               print '    <%1!> IS NOT A STORED PROCEDURE.', @SP

               print ' '
            end
         else
            begin
               insert into #directcall (tlevel, caller, callername, callee,
                                        calleename)
                  select 0, d.id, @SP, d.depid, object_name (d.depid)
                  from sysdepends d,
                       sysobjects o
                  where d.id             = object_id (@SP)
                    and d.depid          = o.id
                    and (o.sysstat & 15) = 4

               while (exists (select *
                              from #directcall
                              where @level  < @maxlevels + 2
                                and tlevel  = @level - 1
                                and callee IS NOT NULL))
                  begin
                     insert into #directcall (tlevel, caller, callername,
                                              callee, calleename)
                        select distinct @level,
                                        dc.callee,
                                        dc.calleename,
                                        d.depid,
                                        object_name (d.depid)
                        from sysdepends d,
                             sysobjects o,
                             #directcall dc
                        where dc.tlevel        = @level - 1
                          and d.id             = dc.callee
                          and o.id             = d.depid
                          and (o.sysstat & 15) = 4

                     select @level = @level + 1
                  end

               if (exists (select *
                           from #directcall d1,
                                #directcall d2
                           where d1.caller = d2.callee
                             and d1.callee = d2.caller))
                  begin
                     delete #directcall
                     from #directcall d2,
                          #directcall d1
                     where d2.tlevel > d1.tlevel
                       and d2.caller = d1.callee
                       and d2.callee = d1.caller

                     select @recursive = 1
                  end

               update #directcall
               set callername = substring (callername + ' (' + user_name (o.uid) + ')', 1, 32)
               from #directcall dc,
                    sysobjects o
               where o.id   = caller
                 and o.uid <> @uid

               update #directcall
               set calleename = substring (calleename + ' (' + user_name (o.uid) + ')', 1, 32)
               from #directcall dc,
                    sysobjects o
               where o.id   = callee
                 and o.uid <> @uid

               if (@recursive = 1)
                  begin
                     update #directcall
                     set calleename = substring (d1.calleename + ' #', 1, 32)
                     from #directcall d1,
                          #directcall d2
                     where d1.caller = d2.callee
                       and d1.callee = d2.caller
                  end

               -- Determine the maximum stored procedure name length and the
               -- number of levels to be printed.

               select @level = max (tlevel) + 1
               from #directcall

               if (@level >= @maxlevels)
                  begin
                     print '    <%1!> HAS %2! LEVELS.  sp_tree WILL ONLY DISPLAY %3! LEVELS.',
                           @SP, @level, @maxlevels

                     print ' '

                     delete from #directcall
                     where tlevel >= @maxlevels
                  end

               insert into #sizeparms
                  select tlevel, max (char_length (calleename))
                  from #directcall
                  group by tlevel

               select @spmaxlevel = 1

               while (@spmaxlevel <= @level)
                  begin
                     update #sizeparms
                     set spmaxlen = (select max (sp2.spmaxlen)
                                     from #sizeparms sp2
                                     where sp2.tlevel <= @spmaxlevel)
                     from #sizeparms sp
                     where sp.tlevel = @spmaxlevel

                     select @spmaxlevel = @spmaxlevel + 1
                  end

               select @spmaxlevel= max (tlevel)
               from #sizeparms
               where (tlevel * (1 + spmaxlen)) < @textlen + 1

               if (@spmaxlevel = 0)
                  begin
                     select @spmaxlen = 74
                  end
               else
                  begin
                     select @spmaxlen = (@textlen / @spmaxlevel) - 1
                  end

               if (@spmaxlen > (select (max (spmaxlen) + 1)
                               from #sizeparms
                               where tlevel = @spmaxlevel))
                  begin
                     select @spmaxlen = (select (max (spmaxlen) + 1)
                                         from #sizeparms
                                         where tlevel = @spmaxlevel)
                  end

               if (@spmaxlevel > 7)
                  begin
                     insert into #exectree (level1, level2, level3, level4,
                                            level5, level6, level7, level8,
                                            level9)
                        select dc0.calleename, dc1.calleename, dc2.calleename,
                               dc3.calleename, dc4.calleename, dc5.calleename,
                               dc6.calleename, dc7.calleename, dc8.calleename
                        from #directcall dc0,
                             #directcall dc1,
                             #directcall dc2,
                             #directcall dc3,
                             #directcall dc4,
                             #directcall dc5,
                             #directcall dc6,
                             #directcall dc7,
                             #directcall dc8
                        where dc0.tlevel = 0
                          and dc0.callee = dc1.caller
                          and dc1.tlevel = 1
                          and dc1.callee = dc2.caller
                          and dc2.tlevel = 2
                          and dc2.callee = dc3.caller
                          and dc3.tlevel = 3
                          and dc3.callee = dc4.caller
                          and dc4.tlevel = 4
                          and dc4.callee = dc5.caller
                          and dc5.tlevel = 5
                          and dc5.callee = dc6.caller
                          and dc6.tlevel = 6
                          and dc6.callee = dc7.caller
                          and dc7.tlevel = 7
                          and dc7.callee = dc8.caller
                          and dc8.tlevel = 8
                          and not (exists (select dc9.*
                                           from #directcall dc9
                                           where dc9.tlevel = 9
                                             and dc9.caller = dc8.callee))
                  end

               if (@spmaxlevel > 6)
                  begin
                     insert into #exectree (level1, level2, level3, level4,
                                            level5, level6, level7, level8)
                        select dc0.calleename, dc1.calleename, dc2.calleename,
                               dc3.calleename, dc4.calleename, dc5.calleename,
                               dc6.calleename, dc7.calleename
                        from #directcall dc0,
                             #directcall dc1,
                             #directcall dc2,
                             #directcall dc3,
                             #directcall dc4,
                             #directcall dc5,
                             #directcall dc6,
                             #directcall dc7
                        where dc0.tlevel = 0
                          and dc0.callee = dc1.caller
                          and dc1.tlevel = 1
                          and dc1.callee = dc2.caller
                          and dc2.tlevel = 2
                          and dc2.callee = dc3.caller
                          and dc3.tlevel = 3
                          and dc3.callee = dc4.caller
                          and dc4.tlevel = 4
                          and dc4.callee = dc5.caller
                          and dc5.tlevel = 5
                          and dc5.callee = dc6.caller
                          and dc6.tlevel = 6
                          and dc6.callee = dc7.caller
                          and dc7.tlevel = 7
                          and not (exists (select dc8.*
                                           from #directcall dc8
                                           where dc8.tlevel = 8
                                             and dc8.caller = dc7.callee))
                  end

               if (@spmaxlevel > 5)
                  begin
                     insert into #exectree (level1, level2, level3, level4, level5,
                                            level6, level7)
                        select dc0.calleename, dc1.calleename, dc2.calleename,
                               dc3.calleename, dc4.calleename, dc5.calleename,
                               dc6.calleename
                        from #directcall dc0,
                             #directcall dc1,
                             #directcall dc2,
                             #directcall dc3,
                             #directcall dc4,
                             #directcall dc5,
                             #directcall dc6
                        where dc0.tlevel = 0
                          and dc0.callee = dc1.caller
                          and dc1.tlevel = 1
                          and dc1.callee = dc2.caller
                          and dc2.tlevel = 2
                          and dc2.callee = dc3.caller
                          and dc3.tlevel = 3
                          and dc3.callee = dc4.caller
                          and dc4.tlevel = 4
                          and dc4.callee = dc5.caller
                          and dc5.tlevel = 5
                          and dc5.callee = dc6.caller
                          and dc6.tlevel = 6
                          and not (exists (select dc7.*
                                           from #directcall dc7
                                           where dc7.tlevel = 7
                                             and dc7.caller = dc6.callee))
                  end

               if (@spmaxlevel > 4)
                  begin
                     insert into #exectree (level1, level2, level3, level4, level5,
                                            level6)
                        select dc0.calleename, dc1.calleename, dc2.calleename,
                               dc3.calleename, dc4.calleename, dc5.calleename
                        from #directcall dc0,
                             #directcall dc1,
                             #directcall dc2,
                             #directcall dc3,
                             #directcall dc4,
                             #directcall dc5
                        where dc0.tlevel = 0
                          and dc0.callee = dc1.caller
                          and dc1.tlevel = 1
                          and dc1.callee = dc2.caller
                          and dc2.tlevel = 2
                          and dc2.callee = dc3.caller
                          and dc3.tlevel = 3
                          and dc3.callee = dc4.caller
                          and dc4.tlevel = 4
                          and dc4.callee = dc5.caller
                          and dc5.tlevel = 5
                          and not (exists (select dc6.*
                                           from #directcall dc6
                                           where dc6.tlevel = 6
                                             and dc6.caller = dc5.callee))
                  end

               if (@spmaxlevel > 3)
                  begin
                     insert into #exectree (level1, level2, level3, level4, level5)
                        select dc0.calleename, dc1.calleename, dc2.calleename,
                               dc3.calleename, dc4.calleename
                        from #directcall dc0,
                             #directcall dc1,
                             #directcall dc2,
                             #directcall dc3,
                             #directcall dc4
                        where dc0.tlevel = 0
                          and dc0.callee = dc1.caller
                          and dc1.tlevel = 1
                          and dc1.callee = dc2.caller
                          and dc2.tlevel = 2
                          and dc2.callee = dc3.caller
                          and dc3.tlevel = 3
                          and dc3.callee = dc4.caller
                          and dc4.tlevel = 4
                          and not (exists (select dc5.*
                                           from #directcall dc5
                                           where dc5.tlevel = 5
                                             and dc5.caller = dc4.callee))
                  end

               if (@spmaxlevel > 2)
                  begin
                     insert into #exectree (level1, level2, level3, level4)
                        select dc0.calleename, dc1.calleename, dc2.calleename,
                               dc3.calleename
                        from #directcall dc0,
                             #directcall dc1,
                             #directcall dc2,
                             #directcall dc3
                        where dc0.tlevel = 0
                          and dc0.callee = dc1.caller
                          and dc1.tlevel = 1
                          and dc1.callee = dc2.caller
                          and dc2.tlevel = 2
                          and dc2.callee = dc3.caller
                          and dc3.tlevel = 3
                          and not (exists (select dc4.*
                                           from #directcall dc4
                                           where dc4.tlevel = 4
                                             and dc4.caller = dc3.callee))
                  end

               if (@spmaxlevel > 1)
                  begin
                     insert into #exectree (level1, level2, level3)
                        select dc0.calleename, dc1.calleename, dc2.calleename
                        from #directcall dc0,
                             #directcall dc1,
                             #directcall dc2
                        where dc0.tlevel = 0
                          and dc0.callee = dc1.caller
                          and dc1.tlevel = 1
                          and dc1.callee = dc2.caller
                          and dc2.tlevel = 2
                          and not (exists (select dc3.*
                                           from #directcall dc3
                                           where dc3.tlevel = 3
                                             and dc3.caller = dc2.callee))
                  end

               insert into #exectree (level1, level2)
                  select dc0.calleename, dc1.calleename
                  from #directcall dc0,
                       #directcall dc1
                  where dc0.tlevel = 0
                    and dc0.callee = dc1.caller
                    and dc1.tlevel = 1
                    and not (exists (select dc2.*
                                     from #directcall dc2
                                     where dc2.tlevel = 2
                                       and dc2.caller = dc1.callee))

               insert into #exectree (level1)
                  select dc0.calleename
                  from #directcall dc0
                  where dc0.tlevel = 0
                    and not (exists (select dc1.*
                                     from #directcall dc1
                                     where dc1.tlevel = 1
                                       and dc1.caller = dc0.callee))

               update #exectree
               set level1x = level1,
                   level2x = level2,
                   level3x = level3,
                   level4x = level4,
                   level5x = level5,
                   level6x = level6,
                   level7x = level7,
                   level8x = level8,
                   level9x = level9

               update #exectree
               set e9.level1x = '',
                   e9.level2x = '',
                   e9.level3x = '',
                   e9.level4x = '',
                   e9.level5x = '',
                   e9.level6x = '',
                   e9.level7x = '',
                   e9.level8x = ''
               from #exectree e9,
                    #exectree et
               where e9.level1 = et.level1
                 and e9.level2 = et.level2
                 and e9.level3 = et.level3
                 and e9.level4 = et.level4
                 and e9.level5 = et.level5
                 and e9.level6 = et.level6
                 and e9.level7 = et.level7
                 and e9.level8 = et.level8
                 and e9.level9 > et.level9

               update #exectree
               set e8.level1x = '',
                   e8.level2x = '',
                   e8.level3x = '',
                   e8.level4x = '',
                   e8.level5x = '',
                   e8.level6x = '',
                   e8.level7x = ''
               from #exectree e8,
                    #exectree et
               where e8.level1 = et.level1
                 and e8.level2 = et.level2
                 and e8.level3 = et.level3
                 and e8.level4 = et.level4
                 and e8.level5 = et.level5
                 and e8.level6 = et.level6
                 and e8.level7 = et.level7
                 and e8.level8 > et.level8

               update #exectree
               set e7.level1x = '',
                   e7.level2x = '',
                   e7.level3x = '',
                   e7.level4x = '',
                   e7.level5x = '',
                   e7.level6x = ''
               from #exectree e7,
                    #exectree et
               where e7.level1 = et.level1
                 and e7.level2 = et.level2
                 and e7.level3 = et.level3
                 and e7.level4 = et.level4
                 and e7.level5 = et.level5
                 and e7.level6 = et.level6
                 and e7.level7 > et.level7

               update #exectree
               set e6.level1x = '',
                   e6.level2x = '',
                   e6.level3x = '',
                   e6.level4x = '',
                   e6.level5x = ''
               from #exectree e6,
                    #exectree et
               where e6.level1 = et.level1
                 and e6.level2 = et.level2
                 and e6.level3 = et.level3
                 and e6.level4 = et.level4
                 and e6.level5 = et.level5
                 and e6.level6 > et.level6

               update #exectree
               set e5.level1x = '',
                   e5.level2x = '',
                   e5.level3x = '',
                   e5.level4x = ''
               from #exectree e5,
                    #exectree et
               where e5.level1 = et.level1
                 and e5.level2 = et.level2
                 and e5.level3 = et.level3
                 and e5.level4 = et.level4
                 and e5.level5 > et.level5

               update #exectree
               set e4.level1x = '',
                   e4.level2x = '',
                   e4.level3x = ''
               from #exectree e4,
                    #exectree et
               where e4.level1 = et.level1
                 and e4.level2 = et.level2
                 and e4.level3 = et.level3
                 and e4.level4 > et.level4

               update #exectree
               set e3.level1x = '',
                   e3.level2x = ''
               from #exectree e3,
                    #exectree et
               where e3.level1 = et.level1
                 and e3.level2 = et.level2
                 and e3.level3 > et.level3

               update #exectree
               set e2.level1x = ''
               from #exectree e2,
                    #exectree et
               where e2.level1 = et.level1
                 and e2.level2 > et.level2

               -- Since Sybase treats an empty space as a single space (!),
               -- the final text formatting becomes a real kludge:

               update #exectree
               set text = level1x
                          + space (@spmaxlen - char_length (level1x))
                          + level2x
                          + space (@spmaxlen - char_length (level2x))
                          + level3x
                          + space (@spmaxlen - char_length (level3x))
                          + level4x
                          + space (@spmaxlen - char_length (level4x))
                          + level5x
                          + space (@spmaxlen - char_length (level5x))
                          + level6x
                          + space (@spmaxlen - char_length (level6x))
                          + level7x
                          + space (@spmaxlen - char_length (level7x))
                          + level8x
                          + space (@spmaxlen - char_length (level8x))
                          + level9x
               where level1x  <> ' '

               update #exectree
               set text = space (@spmaxlen)
                          + level2x
                          + space (@spmaxlen - char_length (level2x))
                          + level3x
                          + space (@spmaxlen - char_length (level3x))
                          + level4x
                          + space (@spmaxlen - char_length (level4x))
                          + level5x
                          + space (@spmaxlen - char_length (level5x))
                          + level6x
                          + space (@spmaxlen - char_length (level6x))
                          + level7x
                          + space (@spmaxlen - char_length (level7x))
                          + level8x
                          + space (@spmaxlen - char_length (level8x))
                          + level9x
               where level1x  = ' '
                 and level2x <> ' '

               update #exectree
               set text = space (2 * @spmaxlen)
                          + level3x
                          + space (@spmaxlen - char_length (level3x))
                          + level4x
                          + space (@spmaxlen - char_length (level4x))
                          + level5x
                          + space (@spmaxlen - char_length (level5x))
                          + level6x
                          + space (@spmaxlen - char_length (level6x))
                          + level7x
                          + space (@spmaxlen - char_length (level7x))
                          + level8x
               where level2x  = ' '
                 and level3x <> ' '

               update #exectree
               set text = space (3 * @spmaxlen)
                          + level4x
                          + space (@spmaxlen - char_length (level4x))
                          + level5x
                          + space (@spmaxlen - char_length (level5x))
                          + level6x
                          + space (@spmaxlen - char_length (level6x))
                          + level7x
                          + space (@spmaxlen - char_length (level7x))
                          + level8x
                          + space (@spmaxlen - char_length (level8x))
                          + level9x
               where level3x  = ' '
                 and level4x <> ' '

               update #exectree
               set text = space (4 * @spmaxlen)
                          + level5x
                          + space (@spmaxlen - char_length (level5x))
                          + level6x
                          + space (@spmaxlen - char_length (level6x))
                          + level7x
                          + space (@spmaxlen - char_length (level7x))
                          + level8x
                          + space (@spmaxlen - char_length (level8x))
                          + level9x
               where level4x  = ' '
                 and level5x <> ' '

               update #exectree
               set text = space (5 * @spmaxlen)
                          + level6x
                          + space (@spmaxlen - char_length (level6x))
                          + level7x
                          + space (@spmaxlen - char_length (level7x))
                          + level8x
                          + space (@spmaxlen - char_length (level8x))
                          + level9x
               where level5x  = ' '
                 and level6x <> ' '

               update #exectree
               set text = space (6 * @spmaxlen)
                          + level7x
                          + space (@spmaxlen - char_length (level7x))
                          + level8x
                          + space (@spmaxlen - char_length (level8x))
                          + level9x
               where level6x  = ' '
                 and level7x <> ' '

               update #exectree
               set text = space (7 * @spmaxlen)
                          + level8x
                          + space (@spmaxlen - char_length (level8x))
                          + level9x
               where level7x  = ' '
                 and level8x <> ' '

               update #exectree
               set text = space (8 * @spmaxlen)
                          + level9x
               where level8x  = ' '
                 and level9x <> ' '

               if (exists (select *
                           from #exectree))
                  begin
                     if (@recursive = 1)
                        begin
                           select 'EXECUTE TREE (# => recursion)' = text
                           from #exectree
                           order by level1, level2, level3, level4, level5, level6,
                                    level7, level8, level9
                        end
                     else
                        begin
                           select 'EXECUTE TREE' = text
                           from #exectree
                           order by level1, level2, level3, level4, level5, level6,
                                    level7, level8, level9
                        end
                  end
               else
                  begin
                     print '    <%1!> DOES NOT EXECUTE ANY STORED PROCEDURES.',
                           @SP

                     print ' '
                  end
            end
      end
go
sp_addcomment sp_tree, 17, 'Displays execute tree for STORED PROCEDURE'
go
sp_procxmode sp_tree, anymode
go

create procedure sp_trees as
   set ansinull                      on
   set close on endtran              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_trees> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @createdate   char    ( 8),
           @errorchar    varchar ( 4),
           @existsflag   tinyint,
           @maxownsplen  tinyint,
           @owner        varchar (30),
           @ownersp      varchar (63),
           @printmssg    varchar (70),
           @spname       varchar (30),
           @uidcount     tinyint

   declare sp_cursor cursor for
      select distinct o1.name,
                      user_name (o1.uid),
                      convert (char (8), o1.crdate, 11)
      from sysdepends d,
           sysobjects o1,
           sysobjects o2
      where (o1.sysstat & 15) = 4                     -- SP
        and d.id              = o1.id
        and d.depid           = o2.id
        and (o2.sysstat & 15) = 4                     -- dependent is a SP
        and not exists (select d2.*                   -- SP has no parent SP
                        from sysdepends d2
                        where d2.depid = o1.id)
      order by o1.name, user_name (o1.uid)
   for read only

   select @maxownsplen = 1 + max (datalength (user_name (uid) + name)),
          @existsflag  = 0
   from sysobjects
   where (sysstat & 15) = 4

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

         print ' '

         update statistics sysdepends

         update statistics sysobjects
      end

   open sp_cursor

   fetch sp_cursor into @spname, @owner, @createdate

   while (@@sqlstatus = 0)
      begin
         select @existsflag = 1

         if (@owner = 'dbo')
            begin
               select @uidcount = count (uid)
               from sysobjects
               where name           = @spname
                 and (sysstat & 15) = 4

               if (@uidcount = 1)
                  begin
                     select @ownersp = @spname
                  end
               else
                  begin
                     select @ownersp = @owner + '.' + @spname
                  end
            end
         else
            begin
               select @ownersp = @owner + '.' + @spname
            end

         select @printmssg = @ownersp
                             + space (@maxownsplen - datalength (@ownersp))
                             + '(' + @createdate + ')'

         print ' '

         print ' '

         print '    %1!', @printmssg

         execute sp_tree @ownersp

         fetch sp_cursor into @spname, @owner, @createdate
      end

   if (@@sqlstatus = 2)
      begin
         if (@existsflag = 0)
            begin
               print '    THERE ARE NO STORED PROCEDURES WHICH EXECUTE OTHER STORED PROCEDURES.'

               print ' '
            end
      end
   else
      begin
         select @errorchar = convert (varchar (4), @@sqlstatus)

         print '  SP CURSOR ERROR.  STATUS = %1!.', @errorchar
      end

   close sp_cursor

   deallocate cursor sp_cursor

   print ' '
go
sp_addcomment sp_trees, 17, 'sp_tree for all non-trivial highest level stored procedures'
go
sp_procxmode sp_trees, anymode
go

dump transaction sybsystemprocs with truncate_only
go
