use sybsystemprocs
go

sp_dropprocedure sp_add
go
sp_dropprocedure sp_formatdate
go
sp_dropprocedure sp_date
go
sp_dropprocedure sp_duration
go
sp_dropprocedure sp_excld_prnt
go
sp_dropprocedure sp_globalexec
go
sp_dropprocedure sp_like_string
go
sp_dropprocedure sp_me
go
sp_dropprocedure sp_server
go
--sp_dropprocedure sp_sybprocs
--go
sp_dropprocedure sp_units
go
sp_dropprocedure sp_utilprocs
go
sp_dropprocedure sp_version             -- Renamed as sp_server
go
sp_dropprocedure sp_exclude_object
go

set quoted_identifier on
go

create procedure sp_add (@NUMBER1   double precision,
                         @NUMBER2   double precision,
                         @NUMBER3   double precision = NULL,
                         @NUMBER4   double precision = NULL,
                         @NUMBER5   double precision = NULL,
                         @NUMBER6   double precision = NULL,
                         @NUMBER7   double precision = NULL,
                         @NUMBER8   double precision = NULL,
                         @NUMBER9   double precision = NULL,
                         @NUMBER10  double precision = NULL,
                         @NUMBER11  double precision = NULL,
                         @NUMBER12  double precision = NULL)  as

   print ' '

   declare @result  double precision

   select @result = @NUMBER1
                    +  @NUMBER2
                    +  isnull (@NUMBER3, 0)
                    +  isnull (@NUMBER4, 0)
                    +  isnull (@NUMBER5, 0)
                    +  isnull (@NUMBER6, 0)
                    +  isnull (@NUMBER7, 0)
                    +  isnull (@NUMBER8, 0)
                    +  isnull (@NUMBER9, 0)
                    +  isnull (@NUMBER10, 0)
                    +  isnull (@NUMBER11, 0)
                    +  isnull (@NUMBER12, 0)

   select 'SUM' = convert (char (22), convert (money, @result), 1)

   commit work    -- OTHERWISE THERE MAY BE A PROBLEM WITH CHAINED MODE!
go
sp_addcomment sp_add, 'Performs addition on a maximum of 12 NUMBERs'
go
sp_procxmode sp_add, anymode
go

create procedure sp_formatdate (@FORMATED_DATE  varchar (32)        output,
                                @INPUT_DATE     datetime    = NULL)  as
   set ansinull                      on
   set nocount                       on
   set string_rtruncation            on

   declare @i              int,
           @format_string  varchar (32),
           @today          datetime

   select @today = coalesce (@INPUT_DATE, getdate ())

   select @i = datepart (month, @today)

   select @format_string = case  @i
                              when 1   then 'January'
                              when 2   then 'February'
                              when 3   then 'March'
                              when 4   then 'April'
                              when 5   then 'May'
                              when 6   then 'June'
                              when 7   then 'July'
                              when 8   then 'August'
                              when 9   then 'September'
                              when 10  then 'October'
                              when 11  then 'November'
                              when 12  then 'December'
                           end

   select @format_string = @format_string
                          +  ' '
                          +  convert (varchar (2), datepart (day, @today))
                          +  ', '
                          +  convert (char (4), datepart (year, @today))
                          +  ' ('
                          +  convert (char (8), @today, 8)

   if (datepart (hour, @today) < 12)
      begin
         select @format_string = @format_string  +  ' AM)'
      end
   else
      begin
         select @format_string = @format_string  +  ' PM)'
      end

  select @FORMATED_DATE = @format_string
go
sp_addcomment sp_formatdate, 'Returns a FORMATED DATE from a DATETIME (default is NULL)'
go

create procedure sp_date (@FORMAT_FLAG  varchar (1) = NULL) as

   --  THE DATE FORMAT OPTIONS WHEN @FORMAT_FLAG IS NON-NULL IS BASED UPON
   --  sp_date WRITTEN BY SIMON WALKER, THE SQL WORKSHOP LTD.

   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_date CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @format_string  varchar (32),
           @i              int,
           @today          datetime

   if (@FORMAT_FLAG IS NULL)
      begin
         execute  sp_formatdate  @FORMATED_DATE = @format_string output

         select 'CURRENT DATE/TIME' = @format_string
      end
   else
      begin
         print '    "FORMAT = convert (char (26), getdate (), OPTION)"'

         print ' '

         select @i     = 0,
                @today = getdate ()

         select OPTION1 = convert (tinyint, @i),
                FORMAT1 = convert (char (26), @today, @i),
                OPTION2 = convert (tinyint, @i + 100),
                FORMAT2 = convert (char (26), @today, @i + 100)
         into #date

         while (@i < 12)
            begin
               select @i = @i + 1

               insert into #date
                  values (@i,
                          convert (char (26), @today, @i),
                          @i + 100,
                          convert (char (26), @today, @i + 100))
            end

         insert into #date
            values (13, convert (char (26), @today, 13), 113, 'N/A')

         insert into #date
            values (14, convert (char (26), @today, 14), 114, 'N/A')

         insert into #date
            values (15, convert (char (26), @today, 15), 115, 'N/A')

         select 'OPTION'  = OPTION1,
                'FORMAT'  = FORMAT1,
                'OPTION ' = OPTION2,
                'FORMAT ' = FORMAT2
         from #date
         order by OPTION1
      end
go
sp_addcomment sp_date, 'Prints current date/time in Month DD, CCYY (HH:MM:SS AM/PM) format'
go
sp_addcomment sp_date, '   if FORMAT_FLAG is non-null then display all date formats'
go
sp_procxmode sp_date, anymode
go

create procedure sp_duration (@TIMESTART  datetime,
                              @TIMEEND    datetime,
                              @DURATION   varchar (14) output) as
   --
   --  RETURN A DURATION IN AN APPROPRIATE DATA-DEPENDENT FORMAT.  ALL
   --  BUT THE FIRST NUMBER WILL BE LEFT ZERO-PADDED.  NOTE THAT THE
   --  MILLISECONDS VALUE IS SOMETIMES SHORT BY 2 MS.
   --     HH:MM:SS
   --     MM:SS
   --     SS.MSS seconds
   --     MSS ms.
   --
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @hh  int,
           @mm  int,
           @ms  int,
           @ss  int

   select @ms       = datediff (ms, @TIMESTART, @TIMEEND),
          @DURATION = NULL

   select @ss = @ms /1000,
          @ms = @ms % 1000

   if (@ss > 0)
      begin
         select @mm = @ss / 60,
                @ss = @ss % 60

         if (@mm > 0)
            begin
               if (@ss > 9)
                  begin
                     select @DURATION = convert (char (2), @ss)
                  end
               else
                  begin
                     select @DURATION = '0'  +  convert (char (1), @ss)
                  end

               select @hh = @mm / 60,
                      @mm = @mm % 60

               if (@mm > 9)
                  begin
                     select @DURATION = convert (char (2), @mm)  +  ':'  +  @DURATION
                  end
               else
                  begin
                     select @DURATION = convert (char (1), @mm)  +  ':'  +  @DURATION
                  end

               if (@hh > 0)
                  begin
                     select @DURATION = convert (varchar (2), @hh)  +  ':'  +  @DURATION
                  end
            end
         else
            begin
               if (@ms > 99)
                  begin
                     select @DURATION = '.'  +  convert (char (3), @ms)
                  end
               else if (@ms > 9)
                  begin
                     select @DURATION = '.0'  +  convert (char (2), @ms)
                  end
               else
                  begin
                     select @DURATION = '.00'  +  convert (char (1), @ms)
                  end

               select @DURATION = convert (varchar (2), @ss)  +  @DURATION  +  ' seconds'
            end
      end
   else
      begin
         select @DURATION = convert (varchar (4), @ms)  +  ' ms.'
      end
go
sp_procxmode sp_duration, anymode
go

create procedure sp_globalexec (@UTILITY_SP  varchar (30),
                                @ARG1        varchar (30) = NULL,
                                @ARG2        varchar (30) = NULL,
                                @ARG3        varchar (30) = NULL) as
   set ansinull                      on
   set close on endtran              on
   set flushmessage                  on
   set string_rtruncation            on

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

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

         print ' '

         return 1
      end

   declare @procstring  varchar (65),
           @dbname      varchar (30),
           @errorchar   varchar ( 4),
           @srvname     varchar (30)

   declare globalexec_cursor cursor for
      select name
      from master.dbo.sysdatabases
      order by name
   for read only

   open globalexec_cursor

   -- DO AN INITIAL EXECUTION TO CHECK THAT THE UTILITY_SP PARAMETER IS OK.
   -- THIS IS NOT ELEGANT BUT ELIMINATES CHECKS WITHIN THE LOOP.  THERE IS
   -- NO @@sqlstatus CHECK BEFORE THE LOOP SINCE THERE IS A MINIMUM OF FOUR
   -- DATABASES ON A SERVER.

   fetch globalexec_cursor into @dbname

   select @procstring = @dbname  +  '.dbo.'  +  @UTILITY_SP

   execute  @procstring  @ARG1, @ARG2, @ARG3

   select @errorchar = convert (varchar (4), @@error)

   if (@errorchar <> '0')
      begin
         print ' '

         print '  sp_globalexec ERROR.  ERROR = %1!.', @errorchar

         print ' '

         print '  UTILITY SP = %1!.', @UTILITY_SP

         print ' '

         print '  ARG1 = %1!, ARG2 = %2!, ARG3 = %3!.', @ARG1, @ARG2, @ARG3

         print ' '

         close globalexec_cursor

         deallocate cursor globalexec_cursor

         return 1
      end

   fetch globalexec_cursor into @dbname

   while (@@sqlstatus = 0)
      begin
         select @procstring = @dbname  +  '.dbo.'  +  @UTILITY_SP

         execute  @procstring  @ARG1, @ARG2, @ARG3

         fetch globalexec_cursor into @dbname
      end

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

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

         return 1
      end

   close globalexec_cursor

   deallocate cursor globalexec_cursor
go
sp_addcomment sp_globalexec, 'Executes UTILITY PROC in every database with optional'
go
sp_addcomment sp_globalexec, 'arguments [ARG1 [, ARG2 [, ARG3]]]'
go
sp_procxmode sp_globalexec, anymode
go

create procedure sp_like_string (@INPUT_STRING  varchar (30),
                                 @LIKE_STRING   varchar (30)  output)  as
   set ansinull                      on
   set nocount                       on
   set string_rtruncation            on

   declare @i  tinyint,
           @j  tinyint

   select @i           = 1,
          @LIKE_STRING = @INPUT_STRING

   select @j = charindex ('_', @LIKE_STRING)

   while (@j <> @i - 1)
      begin
         select @LIKE_STRING = stuff (@LIKE_STRING, @j, 1, '[_]')

         select @i = @j + 3

         select @j = @i - 1 + charindex ('_', substring (@LIKE_STRING, @i, 30))
      end

   select @LIKE_STRING = substring (@LIKE_STRING, 1, 29) + '%'
go
sp_procxmode sp_like_string, anymode
go
sp_existsprocedure sp_like_string
go

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

   print ' '

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

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

         print ' '

         return 1
      end

   declare @dbo_alias_uid  int,
           @dbo_uid        int,
           @idstring       varchar (6),
           @namestring     varchar (40),
           @sa_uid         tinyint,
           @user_uid       int

   select @namestring = suser_name (origsuid),
          @idstring   = convert (varchar (6), origsuid)
   from master.dbo.sysprocesses
   where spid = @@spid

   if (@namestring IS NOT NULL)
      begin
         select @namestring = ltrim (@namestring  +  ' ('  +  @idstring  +  ')')

         print '    THE set proxy COMMAND HAS BEEN INVOKED FOR LOGIN %1!.',
               @namestring

         print ' '
      end

   select @sa_uid = charindex ('sa_role', show_role ())

   select @dbo_uid = 1
   from sysusers
   where suid = suser_id ()
     and uid  = 1

   select @dbo_alias_uid = u.uid
   from sysalternates a, sysusers u
   where a.suid = suser_id ()
     and u.suid = a.altsuid
     and u.uid  = 1

   select @user_uid = uid
   from sysusers
   where suid  = suser_id ()
     and uid   = user_id ()

   select @dbo_uid       = coalesce (@dbo_uid, 0),
          @dbo_alias_uid = coalesce (@dbo_alias_uid, 0),
          @user_uid      = coalesce (@user_uid, 0)

   if ((user_id () <> 1)  and  (@sa_uid + @dbo_uid + @dbo_alias_uid > 0))
      begin
         print '    THE setuser COMMAND HAS BEEN INVOKED.'

         print ' '
      end

   select 'LOGIN (spid, suid)' = substring (suser_name ()
                                            +  ' ('
                                            +  convert (varchar (6), @@spid)
                                            +  ', '
                                            +  convert (varchar (6), suser_id ())
                                            +  ')',
                                            1,
                                            24),
          'DATABASE (dbid)'    = substring (db_name ()
                                            +  ' ('
                                            +  convert (varchar (6), db_id ())
                                            +  ')',
                                            1,
                                            18),
          'USER (uid)'         = substring (user_name ()
                                            +  ' ('
                                            +  convert (varchar (6), user_id ())
                                            +  ')',
                                            1,
                                            18),
          'GROUP (gid)'        = substring (ug.name
                                            +  ' ('
                                            +  convert (varchar (6), ug.gid)
                                            +  ')',
                                            1,
                                            14)
   from sysusers ug,
        sysusers uu
   where uu.uid = user_id ()
     and ug.uid = uu.gid

   print ' '
go
sp_addcomment sp_me, 'Displays contextual user identification'
go
sp_procxmode sp_me, anymode
go

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

   print ' '

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

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

         print ' '

         return 1
      end

   declare @break        tinyint,
           @date_string  varchar (32),
           @today        datetime

   create table #server
      (sort   tinyint    not null,
       field  char (11)  not null,
       value  char (64)  not null)

   execute  sp_formatdate  @FORMATED_DATE = @date_string output

   if (exists (select *
               from master.dbo.sysservers
               where srvid    = 0
                 and srvname <> srvnetname))
      begin
         insert into #server
            select 1, 'Server', substring (@@servername + ' (Network: ' + srvnetname + ')', 1, 64)
            from master.dbo.sysservers
            where srvid    = 0
              and srvname <> srvnetname
      end
   else
      begin
         insert into #server
            values (1, 'Server', substring (@@servername, 1, 64))
      end

   insert into #server
      values (2, 'Date Run', @date_string)

   select @break = charindex (' ', substring (@@version, 51, 255))

   insert into #server
      values (3, 'ASE Version', substring (substring (@@version, 1, 50 + @break), 1, 64))

   if (@break > 0)
      begin
         insert into #server
            values (4, ' ', substring (ltrim (substring (@@version, 51 + @break, 64)), 1, 64))
      end

   insert into #server
      values (5, 'Language', convert (varchar (64), @@language))

   insert into #server
      values (6, 'Page Size', ltrim (substring (convert (char (9), convert (money, @@pagesize), 1), 1, 6)))

   insert into #server
      values (7, 'Hysteresis', ltrim (substring (convert (char (10), convert (money, @@thresh_hysteresis), 1), 1, 7))  +  ' pages')

   select @break = charindex (' ', substring (@@cis_version, 51, 255))

   insert into #server
      values (8, 'CIS Version', substring (substring (@@cis_version, 1, 50 + @break), 1, 64))

   if (@break > 0)
      begin
         insert into #server
            values (9, ' ', substring (ltrim (substring (@@cis_version, 51 + @break, 64)), 1, 64))
      end

   select ATTRIBUTE = field,
          VALUE     = value
   from #server
   order by sort

   print ' '
go
sp_addcomment sp_server, 'Displays high level server attributes'
go
sp_procxmode sp_server, anymode
go

--create procedure sp_sybprocs (@TOPIC varchar (19) = NULL)  as
--   set ansinull                      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_sybprocs CANNOT BE RUN FROM WITHIN A TRANSACTION.'
--
--         print ' '
--
--         return 1
--      end
--
--   declare @like_topic   varchar (12),
--           @upper_topic  varchar (10)
--
--   create table #sybprocs
--      (spname     varchar (23)  not null,
--       sortname   varchar (26)  not null,
--       seq        tinyint       not null,
--       breakpt    tinyint       not null,
--       txt        varchar (129) not null)
--
--   select @like_topic  = '%' + upper (@TOPIC) + '%',
--          @upper_topic = upper (@TOPIC)
--
--   insert into #sybprocs
--      select substring (s1.commandname, 4, 23),
--             substring (s1.commandname, 1, 26),
--             linenum,
--             54,
--             s1.syntax
--      from sybsyntax.dbo.sybsyntax s1
--      where exists (select s2.commandname
--                    from sybsyntax.dbo.sybsyntax s2
--                    where s2.commandname       = s1.commandname
--                      and s2.commandname    like 'sp[_]%'
--                      and upper (s2.syntax) like @like_topic)
--
--   -- TRIM ROW 1
--
--   update #sybprocs
--   set txt = ltrim (substring (txt, 2 + charindex ('-', txt), 129))
--   where seq = 1
--
--   -- REMOVE THE TRAILING SYNTAX DESCRIPTION ROWS
--
--   delete #sybprocs
--   from #sybprocs s1
--   where exists (select s2.spname
--                 from #sybprocs s2
--                 where s2.spname     = s1.spname
--                   and s2.seq       <= s1.seq
--                   and s2.txt     like ' %sp[_]%')
--
--   -- COMPACTIFY THE TEXT INTO ONE ROW
--
--   -- TEMPORARILY CONVERT "master.dbo." TO "^^^^^^^^^^^".
--
--   update #sybprocs
--   set txt = stuff (txt, charindex ('master.dbo.', txt), 11, '^^^^^^^^^^^')
--   where charindex ('master.dbo.', txt) > 0
--
--   update #sybprocs
--   set txt = substring (rtrim (s1.txt) + ' ' + ltrim (s2.txt), 1, 129)
--   from #sybprocs s1,
--        #sybprocs s2
--   where charindex ('.', s1.txt) = 0
--     and s2.spname               = s1.spname
--     and s2.seq                  = 2
--
--   update #sybprocs
--   set txt = substring (rtrim (s1.txt) + ' ' + ltrim (s3.txt), 1, 129)
--   from #sybprocs s1,
--        #sybprocs s3
--   where charindex ('.', s1.txt) = 0
--     and s3.spname               = s1.spname
--     and s3.seq                  = 3
--
--   delete from #sybprocs
--   where seq > 1
--
--   update #sybprocs
--   set txt = substring (txt, 1, 1 + charindex ('.', txt))
--   where charindex ('.', txt) > 0
--
--   -- MAKE SURE THAT TOPIC IS FOUND BEFORE THE END OF THE FIRST SENTENCE
--
--   delete from #sybprocs
--   where charindex (@upper_topic, upper (spname))  = 0
--     and charindex ('.', txt)                     <> 0
--     and charindex ('.', txt)                      < charindex (@upper_topic, upper (txt))
--
--   -- RESTORE THE "master.dbo." TXT.
--
--   update #sybprocs
--   set txt = stuff (txt, charindex ('^^^^^^^^^^^', txt), 11, 'master.dbo.')
--   where charindex ('^^^^^^^^^^^', txt) > 0
--
--   -- ENSURE THAT TOPIC IS IN THE DESCRIPTION INSTEAD OF THE SYNTAX
--
--   delete from #sybprocs
--   where charindex (@upper_topic, upper (spname)) = 0
--     and charindex (@upper_topic, upper (txt))    = 0
--
--   if (exists (select *
--               from #sybprocs))
--      begin
--         -- SPLIT THE TEXT INTO TWO ROWS IF APPROPRIATE
--
--         update #sybprocs
--         set breakpt = 54 - charindex (' ', reverse (substring (txt, 1, 53)))
--         where substring (txt, 53, 1) <> ' '
--
--         insert into #sybprocs
--            select ' ', sortname, 2, 0, ltrim (substring (txt, breakpt, 54))
--            from #sybprocs
--            where breakpt                         <> 1
--              and ltrim (substring (txt, 54, 54)) <> '.'
--
--         update #sybprocs
--         set txt = substring (txt, 1, breakpt)
--         where seq = 1
--
--         select 'STORED PROCEDURE'   = spname,
--                'SYBASE DESCRIPTION' = substring (txt, 1, 53)
--         from #sybprocs
--         order by sortname, seq
--      end
--   else
--      begin
--         print ' '
--
--         print '    THERE ARE NO SYBASE UTILITY PROCEDURES FOR TOPIC %1!.',
--               @TOPIC
--
--         print ' '
--      end
--go
--sp_addcomment sp_sybprocs, 'Lists Sybase utility procedures for TOPIC in name order'
--go
--sp_procxmode sp_sybprocs, anymode
--go

create procedure sp_units (@NUMBER  double precision) as
   set nocount                       on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @giga        double precision,
           @kilo        double precision,
           @mega        double precision,
           @parmstring  varchar (255),
           @pgsize      double precision

   if (@NUMBER > 97656249.0)
      begin
         select @parmstring = ltrim (convert (varchar (255), @NUMBER))

         print '    THE PARAMETER VALUE %1! IS TOO LARGE FOR sp_units TO USE',
               @parmstring

         print ' '

         print '    THE MAXIMUM VALUE IS 97,656,249.'

         print ' '
      end
   else
      begin
         select @kilo   = convert (double precision, 1024),
                @mega   = convert (double precision, 1048576),
                @giga   = convert (double precision, 1073741824),
                @pgsize = convert (double precision, @@pagesize)

         select GB  = @NUMBER / @mega,
                MB  = @NUMBER / @kilo,
                P   = (@NUMBER * @kilo) / @pgsize,
                KB  = @NUMBER
         into #units

         insert into #units
            select (@NUMBER * @pgsize) / @giga,
                   (@NUMBER * @pgsize) / @mega,
                   @NUMBER,
                   (@NUMBER * @pgsize) / @kilo

         insert into #units
            select @NUMBER / @kilo,
                   @NUMBER,
                   (@NUMBER * @mega) / @pgsize,
                   @NUMBER * @kilo

         insert into #units
            select @NUMBER,
                   @NUMBER * @kilo,
                   (@NUMBER * @giga) / @pgsize,
                   @NUMBER * @mega

         select 'GigaBytes (GB)' = convert (char (14), convert (money, GB), 1),
                'MegaBytes (MB)' = convert (char (17), convert (money, MB), 1),
                Pages            = convert (char (21), convert (money, P), 1),
                'KiloBytes (KB)' = convert (char (22), convert (money, KB), 1)
         from #units
      end
go
sp_addcomment sp_units, 'Prints NUMBER in equivalent GB, MB, Page, and KB units'
go
sp_procxmode sp_units, anymode
go

create procedure sp_utilprocs (@TOPIC varchar (30) = '%') 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_utilprocs CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring    varchar (255),
           @dscrplen     tinyint,
           @likestring   varchar ( 30),
           @splen        tinyint,
           @upperstring  varchar ( 30)

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

   if (@TOPIC IS NULL)
      begin
         insert into #utilprocs
            select substring (u.spname, 4, 16), 1, u.txt, 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 1                = (select count (u2.seq)
                                      from sybsystemprocs.dbo.udescriptions u2
                                      where u2.spname = u.spname)

         insert into #utilprocs
            select substring (u.spname + ' *', 4, 16), 1, u.txt, 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 1                < (select count (u2.seq)
                                      from sybsystemprocs.dbo.udescriptions u2
                                      where u2.spname = u.spname)
      end
   else
      begin
         select @upperstring = upper (@TOPIC)

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

         select @likestring = substring ('%' + @likestring, 1, 30)

         insert into #utilprocs
            select substring (u.spname, 4, 16), u.seq, u.txt, u.spname
            from sybsystemprocs.dbo.udescriptions u,
                 sybsystemprocs.dbo.sysobjects o
            where upper (u.spname) like @likestring
              and o.id                = u.objid
              and (o.sysstat & 15)    = 4

         insert into #utilprocs
            select substring (u.spname, 4, 16), u.seq, u.txt, u.spname
            from sybsystemprocs.dbo.udescriptions u,
                 sybsystemprocs.dbo.sysobjects o
            where upper (u.txt) like @likestring
              and o.id             = u.objid
              and (o.sysstat & 15) = 4

         insert into #utilprocs
            select substring (u.spname, 4, 16), u.seq, u.txt, u.spname
            from sybsystemprocs.dbo.udescriptions u
            where exists (select p.spname
                          from #utilprocs p
                          where u.spname  = p.sort
                            and u.seq    <> p.seq)

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

   if (exists (select *
               from #utilprocs))
      begin
         select @splen = max (char_length (spname))
         from #utilprocs

         if (@splen < 9)
            begin
               select @splen = 9
            end

         select @cmdstring = 'select distinct ''PROCEDURE'' = substring (spname, 1,'
                             +  convert (varchar (2), @splen)
                             +  '),'
                             +  'DESCRIPTION = substring (txt, 1,'
                             +  convert (varchar (2), 76 - @splen)
                             +  ')'
                             +  ' from #utilprocs'
                             +  ' order by sort, seq'

         execute  (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO UTILITY PROCEDURES FOR TOPIC %1!.', @TOPIC

         print ' '
      end
go
sp_addcomment sp_utilprocs, 'Lists utility procedures for TOPIC'
go
sp_procxmode sp_utilprocs, anymode
go

create procedure sp_exclude_object (@TYPE        char ( 3),
                                    @TABLENAME   varchar (30),
                                    @COLUMNNAME  varchar (30))  as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @cmdstring    varchar (255),
           @errorchar    varchar (  4),
           @likestring   varchar ( 30),
           @name         varchar ( 30),
           @fetchflag    tinyint,
           @printstring  varchar ( 78),
           @typestring   varchar ( 17)

   declare exclude_cursor cursor for
      select name
      from sybsystemprocs.dbo.usptvalues
      where type   = @TYPE
        and number > -1
   for read only

   select @fetchflag  = 0,
          @typestring = case  @TYPE
                           when  'EXP'  then 'STORED PROCEDURES'
                           when  'EXT'  then 'TABLES'
                           when  'EXV'  then 'VIEWS'
                           else              '?'
                        end

   select @printstring = '    EXCLUDED "LIKE" '  +  @typestring  +  ':'

   open exclude_cursor

   fetch exclude_cursor into @name

   while (@@sqlstatus = 0)
      begin
         execute  sp_like_string  @INPUT_STRING=@name,
                                  @LIKE_STRING=@likestring  output

         select @cmdstring = 'delete from '
                             +  @TABLENAME
                             +  ' where '
                             +  @COLUMNNAME
                             +  ' like '''
                             +  @likestring
                             +  ''''

         execute  (@cmdstring)

         select @fetchflag   = 1,
                @printstring = substring (@printstring  +  ' '  +  @likestring  +  ',', 1, 78)

         fetch exclude_cursor into @name
      end

   if (@@sqlstatus = 2)
      begin
         if (@fetchflag = 1)
            begin
               select @printstring = substring (@printstring, 1, char_length (@printstring) - 1)

               print '%1!', @printstring

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

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

         print ' '

         print '  TYPE = %1!, TABLENAME = %2!, COLUMNNAME = %3!',
               @TYPE, @TABLENAME, @COLUMNNAME
      end

   close exclude_cursor

   deallocate cursor exclude_cursor
go
sp_existsprocedure sp_exclude_object
go
sp_procxmode sp_exclude_object, anymode
go

dump transaction sybsystemprocs with truncate_only
go
