use master
go
sp_configure 'allow updates', 1
go
reconfigure with override
go

use sybsystemprocs
go
drop procedure sp_addcomment
go

set quoted_identifier on
go

create procedure sp_addcomment (@OBJECT   varchar ( 30),
                                @COMMENT  varchar (255)) as
   set ansinull                      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_addcomment CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         return 1
      end

   declare @maxseq  int,
           @objid   int

   if (charindex ('sa_role', show_role ()) > 0)
      begin
         select @objid = id
         from sybsystemprocs.dbo.sysobjects
         where name           = @OBJECT
           and uid            = 1
           and (sysstat & 15) = 4

         if (@objid IS NULL)
            begin
               print '    STORED PROCEDURE %1! WAS NOT FOUND.', @OBJECT

               return 1

               if (exists (select *
                           from sybsystemprocs.dbo.sysobjects
                           where name = @OBJECT
                             and uid  = 1))
                  begin
                     print '    OBJECT %1! IS NOT A STORED PROCEDURE.', @OBJECT

                     return 1
                  end

               if (exists (select *
                           from sybsystemprocs.dbo.sysobjects
                           where name           = @OBJECT
                             and (sysstat & 15) = 4))
                  begin
                     print '    STORED PROCEDURE %1! IS NOT OWNED BY sa.', @OBJECT

                     return 1
                  end
            end
         else
            begin
               select @maxseq = 1 + max (seq)
               from sybsystemprocs.dbo.udescriptions
               where objid = @objid

               if (@maxseq IS NULL)
                  begin
                     print '    COMMENTED STORED PROCEDURE %1! EXISTS.', @OBJECT

                     -- AN EARLIER VERSION OF THE PROC MAY HAVE BEEN DROPPED.
                     -- CLEAN UP UDESCRIPTIONS IN THIS CASE.  THE OBVIOUS CASES
                     -- ARE FOR sp_addcomment, sp_dropprocedure AND
                     -- sp_existsprocedure.

                     delete from sybsystemprocs.dbo.udescriptions
                     where spname = @OBJECT

                     insert into sybsystemprocs.dbo.udescriptions
                        select @OBJECT, 1, @objid, @COMMENT
                  end
               else if (@maxseq < 255)
                  begin
                     insert into sybsystemprocs.dbo.udescriptions
                        select @OBJECT, @maxseq, @objid, @COMMENT
                  end
               else
                  begin
                     print '    TABLE udescriptions HAS NO ROOM FOR THIS COMMENT.'
                  end
            end
      end
   else
      begin
         print '    ONLY AN ADMINISTRATOR CAN EXECUTE sp_addcomment.'
      end
go
sp_procxmode sp_addcomment, anymode
go

use master
go
sp_configure 'allow updates', 0
go
reconfigure with override
go


use sybsystemprocs
go
drop proc sp_dropprocedure
go
drop proc sp_existsprocedure
go

create procedure sp_dropprocedure (@SP  varchar (30))  as
   set ansinull                      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_dropprocedure CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring  varchar (55)

   if (charindex ('.', @SP) > 0)
      begin
         print '    THIS PROCEDURE DOES NOT WORK WITH STORED PROCEDURES OWNED BY ANOTHER USER.'

         return 1
      end
   else if (exists (select *
                    from sysobjects
                    where name           = @SP
                      and (sysstat & 15) = 4
                      and uid            = user_id ()))
      begin
         select @cmdstring = 'drop procedure '  +  @SP

         if ((charindex ('sa_role', show_role ()) > 0)  and
             (db_name () = 'sybsystemprocs')            and
             (exists (select *
                      from sybsystemprocs.dbo.udescriptions
                      where spname = @SP)))
            begin
               delete from sybsystemprocs.dbo.udescriptions
               where spname = @SP
            end

         execute  (@cmdstring)

         print '    STORED PROCEDURE %1! HAS BEEN DROPPED.', @SP
      end
   else
      begin
         print '    STORED PROCEDURE %1! DOES NOT EXIST.', @SP

         return 1
      end
go
sp_addcomment sp_dropprocedure, 'Drops STORED PROCEDURE with an informational message'
go
sp_procxmode sp_dropprocedure, anymode
go

create procedure sp_existsprocedure (@SP  varchar (30))  as
   set ansinull                      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_existsprocedure CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   if (charindex ('.', @SP) > 0)
      begin
         print '    THIS PROCEDURE DOES NOT WORK WITH STORED PROCEDURES OWNED BY ANOTHER USER.'
      end
   else if (exists (select *
                    from sysobjects
                    where name           = @SP
                      and (sysstat & 15) = 4
                      and uid            = user_id ()))
      begin
         print '    STORED PROCEDURE %1! EXISTS.', @SP
      end
   else
      begin
         print '    STORED PROCEDURE %1! DOES NOT EXIST.', @SP
      end
go
sp_addcomment sp_existsprocedure, 'Does STORED PROCEDURE exist?'
go
sp_procxmode sp_existsprocedure, anymode
go
