use sybsystemprocs
go

drop procedure sp_columnsx
go
drop procedure sp_depend
go
drop procedure sp_devicespace
go
drop procedure sp_display
go
drop procedure sp_objectagg
go
drop procedure sp_removelogin_user
go
drop procedure sp_removelogin
go
drop procedure sp_showtable
go
drop procedure sp_showtables
go
drop procedure sp_tableinfo
go

set quoted_identifier on
go

create procedure sp_columnsx (@COLUMN1  varchar (30),
                              @COLUMN2  varchar (30) = NULL)  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 ' '

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

         print ' '

         return 1
      end

   if (@COLUMN2 IS NULL)
      begin
         execute sp_columns1 @COLUMN1
      end
   else
      begin
         execute sp_columns2 @COLUMN1, @COLUMN2
      end
go
sp_addcomment sp_columnsx, 17, 'Lists tables/views with COLUMN1 [, COLUMN2]'
go
sp_procxmode sp_columnsx, anymode
go

create procedure sp_depend (@OBJECT varchar (63)) 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 ' '

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

         print ' '

         return 1
      end

   declare @sysstat  tinyint

   select @sysstat = (sysstat & 15)
   from sysobjects
   where id              = object_id (@OBJECT)
     and (sysstat & 15) in (1, 2, 3, 4, 8)

   if (@sysstat IS NULL)
      begin
         print ' '

         print '    OBJECT <%1!> HAS NO REFERENCES OR DOES NOT EXIST.', @OBJECT

         print ' '
      end
   else
      begin
         if (@sysstat in (1, 2, 3, 4))                -- view / table / sp
            begin
               execute sp_depend1 @OBJECT             -- bypass dependx with one argument
            end

         if (@sysstat in (2, 4, 8))                   -- view / sp / trigger
            begin
               execute sp_uses @OBJECT
            end
      end
go
sp_addcomment sp_depend, 17, 'sp_dependx + sp_uses for OBJECT'
go
sp_procxmode sp_depend, anymode
go

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

   print ' '

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

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

         print ' '

         return 1
      end

   create table #devicespace
     (unixname   varchar (127)  default ' '  not null,
      devname    varchar (30)   default ' '  not null,
      dbname     varchar (30)   default ' '  not null,
      status     smallint       default 0    not null,
      firstpage  int            default 0    not null,
      totpgs     int            default 0    not null,
      allocpgs   int            default 0    not null,
      freepgs    int            default 0    not null,
      comment    varchar (21)   default ' '  not null,
      totpgtext  char (15)      default ' '  not null,
      alcpgtext  char (15)      default ' '  not null,
      frepgtext  char (15)      default ' '  not null,
      C          smallint       default 0    not null,     -- Controller
      T          smallint       default 0    not null,     -- Target
      S          smallint       default 0    not null)     -- Slice

   if (@DEVICE IS NULL)
      begin
         execute sp_devicespaceall
      end
   else if (upper (@DEVICE) = 'AGG')
      begin
         execute sp_devicespaceagg
      end
   else if (exists (select *
                    from master.dbo.sysdevices
                    where name      = @DEVICE
                      and cntrltype = 0))
      begin
         execute sp_devicespacedvc @DEVICE
      end
   else if (exists (select *
                     from master.dbo.sysdevices
                     where name       = @DEVICE
                       and cntrltype <> 0))
      begin
         print '    DEVICE <%1!> IS NOT A DISK DEVICE.', @DEVICE

         print ' '
      end
   else
      begin
         print '    USAGE: sp_devicespace:        Space allocation for all devices'

         print '           sp_devicespace "AGG":  Aggregate space allocation'

         print '           sp_devicespace DEVICE: Space allocation for DEVICE'

         print ' '
      end
go
sp_addcomment sp_devicespace, 16, 'Lists space allocation on all devices (options: DEVICE or "AGG")'
go
sp_procxmode sp_devicespace, anymode
go

create procedure sp_display (@OBJECT    varchar (63),
                             @LONG_FLAG char (1)     = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @objectid  int

   select @objectid = id
   from sysobjects
   where id              = object_id (@OBJECT)
     and (sysstat & 15) in (2, 4, 6, 7, 8)

   if (@objectid IS NULL)
      begin
         print '    OBJECT <%1!> DOES NOT EXIST OR HAS NO TEXT.', @OBJECT

         print ' '

         if ((@OBJECT like 'sp[_]%')  and  (db_name () <> 'sybsystemprocs'))
            begin
               print '    THE TEXT MAY BE IN sybsystemprocs.'

               print ' '
            end
      end
   else
      begin
         execute sp_displaydetail @objectid, @LONG_FLAG
      end
go
sp_addcomment sp_display, 1, 'Displays text of an OBJECT without wraparound (option: LONG_FLAG)'
go
sp_procxmode sp_display, anymode
go

create procedure sp_objectagg (@LOGIN varchar (30) = NULL) as
   --
   --  The logic is a little bit tricky since a login can own a datatabase
   --  but may not be a user in the master database.  In this case we'll
   --  simply print an informatory message.  Otherwise . . . we'll do more.
   --
   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 ' '

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

         print ' '

         return 1
      end

   declare @dbowner_cnt  smallint,
           @format_lks   varchar (11),
           @format_pgs   varchar (11),
           @procpgs      int,
           @syslocks     int,
           @uid          smallint

   create table #objectagg
      (owner     varchar (30)                not null,
       type      varchar (37)                not null,
       qty       int                         not null,
       prntqty   char (8)      default ' '   not null,
       cached    char (6)      default null  null,
       sortorder varchar (36)  default ' '   not null)

   select @uid = uid
   from sysusers
   where suid = suser_id (@LOGIN)

   select @dbowner_cnt = -1,
          @procpgs     = used_pgs (id, doampg, ioampg)
   from sysindexes
   where id = object_id ('sysprocedures')

   select @syslocks = value
   from master.dbo.syscurconfigs
   where config = 106

   if ((@@isolation > 0)  and  ((10 * @syslocks) < (12 * @procpgs)))
      begin
         select @format_lks = ltrim (substring (convert (char (14), convert (money, @syslocks), 1), 1, 11)),
                @format_pgs = ltrim (substring (convert (char (14), convert (money, @procpgs), 1), 1, 11))

         print '    THERE ARE %1! SYSTEM LOCKS AND %2! SYSPROCEDURES PAGES.',
               @format_lks, @format_pgs

         print ' '

         print '    RERUN AFTER FIRST SETTING TRANSACTION ISOLATION LEVEL TO 0.'

         print ' '
      end
   else
      begin
         if (db_id () = 1)
            begin
               select @dbowner_cnt = count (*)
               from master.dbo.sysdatabases
               where suid = suser_id (@LOGIN)

               if ((@uid IS NULL)  and  (@dbowner_cnt = 1))
                  begin
                     print '    LOGIN %1! OWNS 1 DATABASE AND OWNS NOTHING ELSE.',
                           @LOGIN

                     print ' '
                  end
               else if ((@uid IS NULL)  and  (@dbowner_cnt > 1))
                  begin
                     print '    LOGIN %1! OWNS %2! DATABASES AND OWNS NOTHING ELSE.',
                           @LOGIN, @dbowner_cnt

                     print ' '
                  end
               else
                  begin
                     execute sp_objectaggmaster @LOGIN
                  end
            end

         if (upper (@LOGIN) = 'BYUSER')
            begin
               execute sp_objectaggbyuser
            end
         else if (@LOGIN IS NULL)
            begin
               execute sp_objectagggeneral
            end
         else if (@uid IS NULL)
            begin
               print '    <%1!> IS NOT A VALID LOGIN.', @LOGIN

               print ' '
            end
         else if ((@uid IS NOT NULL)  or  (@dbowner_cnt = 0))
            begin
               execute sp_objectagguser @uid, @LOGIN
            end
      end
go
sp_addcomment sp_objectagg, 14, 'Displays object type aggregates (options: LOGIN or "BYUSER")'
go
sp_procxmode sp_objectagg, anymode
go

create procedure sp_removelogin_user (@SUIDCHAR  varchar (10))  as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   declare @arg1       varchar (30),
           @dbname     varchar (30),
           @suid       smallint,
           @username   varchar (30)

   select @dbname = db_name (),
          @suid   = convert (smallint, @SUIDCHAR)

   select @username = isnull (name, '?')
   from sysusers
   where suid = @suid

   if ((exists (select *
                from sysalternates
                where suid = @suid))  or
       (exists (select *
                from sysusers
                where suid = @suid)))
      begin
         if ((exists (select o.id
                      from sysobjects o,
                           sysusers u
                      where u.suid = @suid
                        and o.uid  = u.uid))  or
             (exists (select m.error
                      from sysusermessages m,
                           sysusers u
                      where u.suid = @suid
                        and m.uid  = u.uid))  or
             (exists (select p.id
                      from sysobjects o,
                           sysprotects p,
                           sysusers u
                      where u.suid     = @suid
                        and p.grantor  = u.uid
                        and o.id       = p.id
                        and o.uid     <> u.uid))  or
             (exists (select t.usertype
                      from systypes t,
                           sysusers u
                      where u.suid     = @suid
                        and t.uid      = u.uid
                        and t.usertype > 99)))
            begin
               print '    %1! OWNS OBJECTS IN DATABASE %2!.',
                     @username, @dbname

               execute sp_ownedby @username

               print ' '

               if (exists (select *
                           from sysalternates
                           where altsuid = @suid))
                  begin
                     print ' '

                     print '    %1! IS ALIASED.', @username

                     print ' '

                     select ALIAS = suser_name (suid)
                     from sysalternates
                     where altsuid = @suid
                     order by ALIAS

                     print ' '
                  end
            end
         else if (exists (select *
                          from sysalternates
                          where suid = @suid))
            begin
               select @arg1 = suser_name (@suid)

               print '    DROPPING ALIAS %1! FROM DATABASE %2!.',
                     @arg1, @dbname

               print ' '

               execute sp_dropalias arg1
            end
         else if (exists (select *
                          from sysusers
                          where suid = @suid))
            begin
               if (exists (select *
                           from sysalternates
                           where altsuid = @suid))
                  begin
                     print '    %1! IS ALIASED.', @username

                     print ' '

                     select ALIAS = suser_name (suid)
                     from sysalternates
                     where altsuid = @suid
                     order by ALIAS

                     print ' '
                  end

               print '    DROPPING USER %1! FROM DATABASE %2!.',
                     @username, @dbname

               print ' '

               execute sp_dropuser @username

               print ' '
            end
      end
go
sp_procxmode sp_removelogin_user, unchained
go

create procedure sp_removelogin (@LOGIN varchar (30))  as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       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_removelogin> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   print ' '

   declare @cmdstring  varchar (30),
           @errorflag  tinyint,
           @suidchar   varchar (10)

   select @errorflag = 0

   if ((proc_role ('sa_role') = 0)  and  (proc_role ('sso_role') = 0))
      begin
         print '    YOU MUST HAVE EITHER THE SA OR SSO ROLE TO EXECUTE THIS STORED PROCEDURE.'

         print ' '

         select @errorflag = 1
      end

   select @suidchar = convert (varchar (10), suid)
   from master.dbo.syslogins
   where name = @LOGIN

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

         print ' '

         select @errorflag = 1
      end

   if (exists (select *
               from master.dbo.sysdatabases
               where suid = suser_id (@LOGIN)))
      begin
         print '    LOGIN %1! OWNS DATABASES.', @LOGIN

         print ' '

         select @errorflag = 1
      end

    if (@errorflag = 0)
       begin
          select @cmdstring = 'sp_removelogin_user'

          execute sp_globalexec  @cmdstring, @suidchar

          execute sp_droplogin @LOGIN
       end
    else
       begin
         print '    sp_removelogin EXECUTION TERMINATED.'

         print ' '
       end
go
sp_addcomment sp_removelogin, 16, 'Removes a LOGIN from all databases and the server'
go
sp_procxmode sp_removelogin, anymode
go

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

         print ' '

         return 1
      end

   declare @dottable     varchar (30),
           @dotowner     varchar (30),
           @dotposition  tinyint,
           @namecnt      tinyint,
           @tableid      int

   select @tableid = id,
          @namecnt = 0
   from sysobjects
   where id              = object_id (@TABLE)
     and (sysstat & 15) in (1, 2, 3)

   if (@tableid IS NULL)
      begin
         select @namecnt = count (*)
         from sysobjects
         where name            = @TABLE
           and (sysstat & 15) in (1, 2, 3)

         if (@namecnt = 0)
            begin
               select @dotposition = charindex ('.', @TABLE)

               if (@dotposition > 0)
                  begin
                     select @dottable = substring (@TABLE, @dotposition + 1, 30),
                            @dotowner = substring (@TABLE, 1, @dotposition - 1)

                     select @tableid = id
                     from sysobjects
                     where name            = @dottable
                       and uid             = user_id (@dotowner)
                       and (sysstat & 15) in (1, 2, 3)
                  end
            end
         else if (@namecnt = 1)
            begin
               select @tableid = id
               from sysobjects
               where name            = @TABLE
                 and (sysstat & 15) in (1, 2, 3)
            end
         else
            begin
               print '    TABLE/VIEW <%1!> IS NOT UNIQUE.  QUALIFY WITH THE OWNER NAME.',
                     @TABLE

               print ' '
            end
      end

   if ((@tableid IS NULL)  and  (@namecnt < 2))
      begin
         print '    TABLE/VIEW <%1!> DOES NOT EXIST.', @TABLE

         print ' '
      end
   else
      begin
         if (exists (select *
                     from syscolumns
                     where id = @tableid))
            begin
               execute sp_showtable_detail @tableid
            end
         else
            begin
               print '    TABLE/VIEW <%1!> DOES NOT HAVE ANY COLUMNS!', @TABLE

               print ' '
            end
      end
go
sp_addcomment sp_showtable, 14, 'Lists column names and types for TABLE/VIEW'
go
sp_procxmode sp_showtable, anymode
go

create procedure sp_showtables (@PREFIX varchar (30) = 'U') 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_showtables> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @createdate   char ( 8),
           @errorchar    varchar ( 4),
           @maxobjlen    tinyint,
           @objectname   varchar (60),
           @owner        varchar (30),
           @printmssg    varchar (70),
           @printname    varchar (63),
           @status       tinyint,
           @typetext     varchar (17),
           @uidcount     tinyint

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

   declare object_prefix_cursor cursor for
      select name, user_name (uid), convert (char (8), crdate, 11)
      from sysobjects
      where name           like @PREFIX + '%'
        and (sysstat & 15)   in (1, 2, 3)
      order by name, user_name (uid)
   for read only

   if (upper (@PREFIX) in ('S', 'U', 'V'))
      begin
         if (upper (@PREFIX) = 'S')
            begin
               select @typetext = 'S (System Tables)',
                      @status   = 1
            end
         else if (upper (@PREFIX) = 'U')
            begin
               select @typetext = 'U (User Tables)',
                      @status   = 3
            end
         else
            begin
               select @typetext = 'V (Views)',
                      @status   = 2
            end

         open object_type_cursor

         fetch object_type_cursor into @objectname, @owner, @createdate

         if (@@sqlstatus = 2)
            begin
               print '    THERE ARE NO OBJECTS OF TYPE %1!.', @typetext

               print ' '
            end
         else
            begin
               select @maxobjlen = 4 + max (datalength (name + user_name (uid)))
               from sysobjects
               where (sysstat & 15) = @status

               while (@@sqlstatus = 0)
                  begin
                     if (@owner = 'dbo')
                        begin
                           select @uidcount = count (uid)
                           from sysobjects
                           where name            = @objectname
                             and (sysstat & 15) in (1, 2, 3)

                           if (@uidcount = 1)
                              begin
                                 select @printname = @objectname
                              end
                           else
                              begin
                                 select @objectname = @owner + '.' + @objectname,
                                        @printname  = @objectname + ' (' + @owner + ')'
                              end
                        end
                     else
                        begin
                           select @objectname = @owner + '.' + @objectname,
                                  @printname  = @objectname + ' (' + @owner + ')'
                        end

                     select @printmssg = @printname
                                         + space (@maxobjlen - datalength (@printname))
                                         + '(' + @createdate + ')'

                     print ' '

                     print ' '

                     print '    %1!', @printmssg

                     execute sp_showtable @objectname

                     fetch object_type_cursor into @objectname, @owner, @createdate
                  end

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

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

         close object_type_cursor

         deallocate cursor object_type_cursor
      end
   else
      begin
         open object_prefix_cursor

         fetch object_prefix_cursor into @objectname, @owner, @createdate

         if (@@sqlstatus = 2)
            begin
               print '    THERE ARE NO TABLES/VIEWS THAT BEGIN WITH <%1!>.',
                     @PREFIX

               print ' '
            end
         else
            begin
               select @maxobjlen = 4 + max (datalength (name + user_name (uid)))
               from sysobjects
               where name           like @PREFIX + '%'
                 and (sysstat & 15)   in (1, 2, 3)

               while (@@sqlstatus = 0)
                  begin
                     if (@owner = 'dbo')
                        begin
                           select @uidcount = count (uid)
                           from sysobjects
                           where name            = @objectname
                             and (sysstat & 15) in (1, 2, 3)

                           if (@uidcount = 1)
                              begin
                                 select @printname = @objectname
                              end
                           else
                              begin
                                 select @objectname = @owner + '.' + @objectname,
                                        @printname  = @objectname + ' (' + @owner + ')'
                              end
                        end
                     else
                        begin
                           select @objectname = @owner + '.' + @objectname,
                                  @printname  = @objectname + ' (' + @owner + ')'
                        end

                     select @printmssg = @printname
                                         + space (@maxobjlen - datalength (@printname))
                                         + '(' + @createdate + ')'

                     print ' '

                     print ' '

                     print '    %1!', @printmssg

                     execute sp_showtable @objectname

                     fetch object_prefix_cursor into @objectname, @owner, @createdate
                  end

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

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

         close object_prefix_cursor

         deallocate cursor object_prefix_cursor
      end
go
sp_addcomment sp_showtables, 14, 'sp_showtable for all tables/views with following qualifiers:'
go
sp_addcomment sp_showtables, 14, '   (NULL): all user tables and views'
go
sp_addcomment sp_showtables, 14, '   PREFIX: only tables/views that begin with PREFIX'
go
sp_addcomment sp_showtables, 14, '   "S":    System tables only'
go
sp_addcomment sp_showtables, 14, '   "U":    User tables only (default)'
go
sp_addcomment sp_showtables, 14, '   "V":    Views only'
go
sp_procxmode sp_showtables, anymode
go

create procedure sp_tableinfo (@TABLE varchar (63)) as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       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 ' '

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

         print ' '

         return 1
      end

   if (exists (select *
               from sysobjects
               where id              = object_id (@TABLE)
                 and (sysstat & 15) in (1, 3)))
      begin
         execute sp_size @TABLE

         execute sp_showtable @TABLE

         execute sp_indexes @TABLE

         execute sp_grants @TABLE

         execute sp_checks @TABLE

         execute sp_refkeys @TABLE

         execute sp_refkeysx @TABLE

         execute sp_rules @TABLE

         execute sp_triggers @TABLE

         execute sp_depend @TABLE
      end
   else
      begin
         print ' '

         print '    TABLE <%1!> DOES NOT EXIST.', @TABLE

         print ' '
      end
go
sp_addcomment sp_tableinfo, 14, 'All utility info for TABLE'
go
sp_procxmode sp_tableinfo, anymode
go

update statistics udescriptions
go

dump transaction sybsystemprocs with truncate_only
go
