use sybsystemprocs
go

sp_dropprocedure sp_addlogtha
go
sp_dropprocedure sp_currplan
go
sp_dropprocedure sp_dbopt
go
sp_dropprocedure sp_dumplog
go
sp_dropprocedure sp_freespace
go
sp_dropprocedure sp_lastxact
go
sp_dropprocedure sp_lockconfig
go
sp_dropprocedure sp_loggedmsgs
go
sp_dropprocedure sp_new
go
sp_dropprocedure sp_newstats
go
sp_dropprocedure sp_segmentdetail
go
sp_dropprocedure sp_segments
go
sp_dropprocedure sp_strategies
go
sp_dropprocedure sp_thalist_db
go
sp_dropprocedure sp_thalist
go
sp_dropprocedure sp_thaprint
go
sp_dropprocedure sp_thatruncate
go
sp_dropprocedure sp_unused_logins
go
sp_dropprocedure sp_unused
go
sp_dropprocedure sp_xactagg
go

sp_dropprocedure sp_updatestatistics
go

dump transaction sybsystemprocs with truncate_only
go

set quoted_identifier on
go

create procedure sp_addlogtha (@FREE_SPACE  integer,
                               @PROC_NAME   varchar (255)) 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_addlogtha CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @dbname         varchar ( 30),
           @db_suid        smallint,
           @procprintname  varchar (255),
           @retcode        smallint

   print ' '

   select @dbname = db_name ()

   select @db_suid = suid
   from master.dbo.sysdatabases
   where name = @dbname

   if ((suser_id () = @db_suid)  or  (proc_role ('sa_role') = 1))
      begin
         if ((exists (select *
                      from sysobjects
                      where name           = @PROC_NAME
                        and (sysstat & 15) = 4
                        and uid            = 1))  or
             (exists (select *
                      from sybsystemprocs.dbo.sysobjects
                      where @PROC_NAME     like 'sp[_]%'
                        and name              = @PROC_NAME
                        and (sysstat & 15)    = 4)))
            begin
               execute @retcode = sp_addthreshold @dbname=@dbname,
                                                  @segname='logsegment',
                                                  @free_space=@FREE_SPACE,
                                                  @proc_name=@PROC_NAME

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

                     print '    sp_addlogtha FAILED.'

                     return @retcode
                  end
            end
         else
            begin
               select @procprintname = rtrim (@PROC_NAME)

               print '    PROCEDURE %1! DOES NOT EXIST IN EITHER %2! OR sybsystemprocs.',
                     @procprintname, @dbname

               print ' '

               return 1
            end
      end
   else
      begin
         print '    ONLY AN ADMINISTRATOR OR THE DBO CAN EXECUTE sp_addlogtha.'

         print ' '

         return 1
      end
go
sp_addcomment sp_addlogtha, 'Executes sp_addthreshold <current database>, logsegment,'
go
sp_addcomment sp_addlogtha, '   FREE_SPACE, PROC_NAME'
go

create procedure sp_currplan (@LOGIN  varchar (30))  as
   set ansinull                      on
   set close on endtran              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_currplan CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @errorchar   varchar (  4),
           @id          int,
           @objname     varchar ( 30),
           @spid        smallint,
           @spidchar    varchar ( 11)

   print ' '

   declare currplan_cursor cursor for
      select spid,
             id,
             coalesce (object_name (id, dbid),
                       object_name (id, db_id ('sybsystemprocs')),
                       object_name (id, db_id ('master')),
                       object_name (id, db_id ('dbccdb')))
      from master.dbo.sysprocesses
      where suid  = suser_id (@LOGIN)
        and cmd  <> 'AWAITING COMMAND'
        and spid <> @@spid
      order by spid
   for read only

   if (charindex ('sa_role', show_role ()) > 0)
      begin
         if (suser_id (@LOGIN) IS NULL)
            begin
               print '    LOGIN %1! DOES NOT EXIST.', @LOGIN

               return 1
            end
         else
            begin
               print ' '

               execute  sp_date

               print ' '

               open currplan_cursor

               fetch currplan_cursor into @spid, @id, @objname

               if (@@sqlstatus = 2)
                  begin
                     print '    THERE ARE NO ACTIVE PROCESSES FOR LOGIN %1!.', @LOGIN
                  end
               else
                  begin
                     print '    PLANS WILL BE FOR LOGIN %1!.', @LOGIN

                     print ' '

                     while (@@sqlstatus = 0)
                        begin
                           print '=============================================================================='

                           print ' '

                           select @spidchar = convert (varchar (11), @spid)

                           if (@id = 0)
                              begin
                                 print '    EXECUTION OF sp_showplan FOR SPID %1!.',
                                       @spidchar
                              end
                           else
                              begin
                                 print '    EXECUTION OF sp_showplan FOR SPID %1!, OBJECT %2!.',
                                       @spidchar, @objname
                              end

                           print ' '

                           execute  sp_showplan  @spid=@spid,
                                                 @batch_id=NULL,
                                                 @context_id=NULL,
                                                 @stmt_num=NULL

                           print ' '

                           fetch currplan_cursor into @spid, @id, @objname
                        end

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

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

                           return 1
                        end
                  end

               close currplan_cursor

               deallocate cursor currplan_cursor
            end
      end
   else
      begin
         print '    ONLY AN ADMINISTRATOR CAN EXECUTE sp_currplan.'
      end

   print ' '
go
sp_addcomment sp_currplan, 'Executes sp_showplan for all active processes for LOGIN'
go

create procedure sp_dbopt (@OPTNAME   varchar (20),
                           @OPTVALUE  varchar (10)) 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_dbopt CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @dbname   varchar ( 30),
           @db_suid  smallint,
           @retcode  smallint

   print ' '

   select @dbname = db_name ()

   select @db_suid = suid
   from master.dbo.sysdatabases
   where name = @dbname

   if ((suser_id () = @db_suid)  or  (proc_role ('sa_role') = 1))
      begin
         execute @retcode = master.dbo.sp_dboption @dbname=@dbname,
                                                   @optname=@OPTNAME,
                                                   @optvalue=@OPTVALUE

         if (@retcode = 0)
            begin
               checkpoint

               print ' '

               print '    CHECKPOINT EXECUTED IN %1!.', @dbname
            end
         else
            begin
               print ' '

               print '    sp_dboption FAILED.'
            end
      end
   else
      begin
         print '    ONLY THE DATABASE OWNER CAN EXECUTE sp_dbopt.'
      end

   print ' '
go
sp_addcomment sp_dbopt, 'Executes sp_dboption for current database with OPTNAME and OPTVALUE'
go

create procedure sp_dumplog (@TYPE_FLAG  char (1) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @allocpgs   int,
           @dbname     varchar (30),
           @duration   varchar (14),
           @error      int,
           @ownerflag  tinyint,
           @roleflag   tinyint,
           @timeend    datetime,
           @timestart  datetime

   print ' '

   if ((@@tranchained = 0)  and
       (@@trancount = 0)    and
       (exists (select *
                from master.dbo.sysdatabases

                where dbid             = db_id ()
                  and (status & 1024) <> 1024)))
      begin
         select @dbname   = name,
                @roleflag = charindex ('oper_role', show_role ()) + charindex ('sa_role', show_role ())
         from master.dbo.sysdatabases
         where dbid = db_id ()

         select @ownerflag = 1
         from master.dbo.sysdatabases
         where dbid = db_id ()
           and suid = suser_id ()

         if ((@ownerflag = 1)  or  (@roleflag > 0))
            begin
               select @allocpgs  = data_pgs (id, doampg),
                      @timestart = getdate ()
               from sysindexes
               where id = 8

               if (@TYPE_FLAG IS NULL)
                  begin
                     print '    DUMPING TRANSACTION LOG WITH TRUNCATE_ONLY.'

                     dump transaction @dbname with truncate_only

                     select @error = @@error
                  end
               else
                  begin
                     print '    DUMPING TRANSACTION LOG WITH NO_LOG.'

                     dump transaction @dbname with no_log

                     select @error = @@error
                  end

               print ' '

               if (@error > 0)
                  begin
                     print ' '

                     print '    DUMP FAILED WITH MESSAGE %1!.', @error
                  end
               else
                  begin
                     print '    DUMP WAS SUCCESSFUL.'
                  end

            print ' '

            select @allocpgs = @allocpgs - data_pgs (id, doampg)
            from sysindexes
            where id = 8

            select @timeend = getdate ()

            execute  sp_duration  @TIMESTART=@timestart,
                                  @TIMEEND=@timeend,
                                  @DURATION=@duration  output

            select DURATION             = @duration,
                   'PAGES FREED'        = substring (convert (char (14),
                                                              convert (money,
                                                                      @allocpgs),
                                                              1),
                                                     1,
                                                     11)

               print ' '
            end
         else
            begin
               print '    YOU DO NOT HAVE DUMP TRANSACTION PRIVILEGES.'
            end
      end
   else
      begin
         print '    Sp_dumplog CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         print '    @@TRANCHAINED = %1!, @@TRANCOUNT = %2!.',
               @@tranchained, @@trancount
      end

   print ' '
go
sp_addcomment sp_dumplog, 'Dumps log with truncate_only (default) or no_log'
go

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

         print ' '

         return 1
      end

   declare @allocpgs   int,
           @datacnt    varchar (3),
           @dataflag   tinyint,
           @datapgs    int,
           @dbname     varchar (30),
           @freepgs    int,
           @indpgs     int,
           @logcnt     varchar (3),
           @logflag    tinyint,
           @logpgs     int,
           @mixedcnt   varchar (3),
           @mixedflag  tinyint,
           @pctused    tinyint,
           @textpgs    int,
           @totpgs     int

   execute  sp_date

   print ' '

   select @dataflag  = 0,
          @logflag   = 0,
          @mixedflag = 0

   select @dataflag = count (u.dbid)                 -- dedicated data areas?
   from master.dbo.sysdatabases d,
        master.dbo.sysusages u
   where d.dbid = db_id ()
     and u.dbid = d.dbid
     and (u.segmap & 7) in (0, 1, 2, 3, 5, 6)

   select @logflag = count (u.dbid)                  -- dedicated log areas?
   from master.dbo.sysdatabases d,
        master.dbo.sysusages u
   where d.dbid         = db_id ()
     and u.dbid         = d.dbid
     and (u.segmap & 7) = 4

   select @mixedflag = count (u.dbid)                -- mixed data/log areas?
   from master.dbo.sysdatabases d,
        master.dbo.sysusages u
   where d.dbid         = db_id ()
     and u.dbid         = d.dbid
     and (u.segmap & 7) = 7

   if (@mixedflag = 0)
      begin
         select @totpgs = sum (size)
         from master.dbo.sysusages
         where dbid    = db_id ()
           and segmap <> 4

         select @allocpgs = sum (reserved_pgs (id, doampg) + reserved_pgs (id, ioampg))
         from sysindexes
         where id <> 8

         select @datapgs = sum (data_pgs (id, doampg)),
                @indpgs  = sum (data_pgs (id, ioampg))
         from sysindexes
         where id    <> 8
           and indid <> 254
           and indid <> 255

         select @textpgs = sum (data_pgs (id, ioampg))
         from sysindexes
         where id    <> 8
           and indid in (254, 255)

         select @freepgs = @totpgs - @allocpgs,
                @pctused = (100 * @allocpgs) / @totpgs

         select ' %DB USED'   = @pctused,
                'TOTAL PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @totpgs),
                                                    1),
                                           1,
                                           11),
                ' FREE PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @freepgs),
                                                    1),
                                           1,
                                           11),
                'ALLOC PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @allocpgs),
                                                    1),
                                           1,
                                           11),
                '  DATA PGS'  = substring (convert (char (13),
                                                    convert (money,
                                                             @datapgs),
                                                    1),
                                           1,
                                           10),
                ' INDEX PGS'  = substring (convert (char (13),
                                                    convert (money,
                                                             @indpgs),
                                                    1),
                                           1,
                                           10),
                ' TEXT PGS'   = substring (convert (char (12),
                                                    convert (money,
                                                             @textpgs),
                                                    1),
                                           1,
                                           9)

         print ' '

         select @totpgs = sum (u.size),
                @logpgs = data_pgs (i.id, i.doampg)
         from sysindexes i,
              master.dbo.sysusages u
         where i.id     = 8
           and u.dbid   = db_id ()
           and u.segmap = 4
         group by u.dbid
         having i.id     = 8
            and u.dbid   = db_id ()
            and u.segmap = 4

         select @freepgs = @totpgs - @logpgs,
                @pctused = (100 * @logpgs) / @totpgs

         select '%LOG USED'   = @pctused,
                'TOTAL PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @totpgs),
                                                    1),
                                           1,
                                           11),
                ' FREE PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @freepgs),
                                                    1),
                                           1,
                                           11),
                'ALLOC PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @logpgs),
                                                    1),
                                           1,
                                           11)
      end
   else
      begin
        select @datacnt   = substring (convert (char (6), convert (money, @dataflag), 1), 1, 3),
               @logcnt    = substring (convert (char (6), convert (money, @logflag), 1), 1, 3),
               @mixedcnt  = substring (convert (char (6), convert (money, @mixedflag), 1), 1, 3)

        select @datacnt  = ltrim (@datacnt),
               @dbname   = db_name (),
               @logcnt   = ltrim (@logcnt),
               @mixedcnt = ltrim (@mixedcnt)

        if (@dataflag = 0)
           begin
              if (@logflag = 0)
                 begin
                    print '    DATABASE %1! HAS %2! DATA/LOG AREA(S).',
                          @dbname,  @mixedcnt

                    print ' '
                 end
              else
                 begin
                    print '    DATABASE %1! HAS %2! DATA/LOG AND %3! LOG AREA(S).',
                          @dbname, @mixedcnt, @logcnt

                    print ' '

                    print '    THE FOLLOWING DATA DOES NOT INDICATE SEPARATE DATA/LOG LIMITATIONS.'

                    print ' '
                 end
           end
        else
           begin
              if (@logflag = 0)
                 begin
                    print '    DATABASE %1! HAS %2! DATA/LOG AND %3! DATA AREA(S).',
                          @dbname, @mixedcnt, @datacnt

                    print ' '
                 end
              else
                 begin
                    print '    DATABASE %1! HAS %2! DATA/LOG, %3! DATA, AND %4! LOG AREA(S).',
                          @dbname, @mixedcnt, @datacnt, @logcnt

                    print ' '
                 end

              print '    THE FOLLOWING DATA DOES NOT INDICATE SEPARATE DATA/LOG LIMITATIONS.'

              print ' '
           end

        select @totpgs = sum (size)
        from master.dbo.sysusages
        where dbid = db_id ()

        select @allocpgs = sum (reserved_pgs (id, doampg) + reserved_pgs (id, ioampg))
        from sysindexes
        where id <> 8

         select @datapgs = sum (data_pgs (id, doampg)),
                @indpgs  = sum (data_pgs (id, ioampg))
         from sysindexes
         where id    <> 8
           and indid <> 254
           and indid <> 255

         select @textpgs = sum (data_pgs (id, ioampg))
         from sysindexes
         where id    <> 8
           and indid in (254, 255)

        select @logpgs = data_pgs (id, doampg)
        from sysindexes
        where id = 8

        select @allocpgs = @allocpgs + @logpgs

        select @freepgs = @totpgs - @allocpgs,
               @pctused = (100 * @allocpgs) / @totpgs

        select '%USED'       = @pctused,
               'TOTAL PAGES' = substring (convert (char (14),
                                                   convert (money,
                                                            @totpgs),
                                                   1),
                                          1,
                                          11),
               ' FREE PAGES' = substring (convert (char (14),
                                                   convert (money,
                                                            @freepgs),
                                                   1),
                                          1,
                                          11),
               'ALLOC PAGES' = substring (convert (char (14),
                                                   convert (money,
                                                            @allocpgs),
                                                   1),
                                          1,
                                          11),
               ' DATA PAGES' = substring (convert (char (14),
                                                   convert (money,
                                                            @datapgs),
                                                   1),
                                          1,
                                          11),
               'INDEX PAGES' = substring (convert (char (14),
                                                   convert (money,
                                                            @indpgs),
                                                   1),
                                          1,
                                          11),
                ' TEXT PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @textpgs),
                                                    1),
                                           1,
                                           11)
      end
go
sp_addcomment sp_freespace, 'Lists total/free/log space for current database'
go
sp_procxmode sp_freespace, anymode
go

create procedure sp_lastxact (@XACTS  tinyint = 1)  as
   print ' '

   if (db_id () = 2)
      begin
         print '    sp_lastxact CANNOT BE RUN FROM tempdb.'
      end
   else if ((@@tranchained = 0)  and
            (@@trancount = 0)    and
            (exists (select *
                     from master.dbo.sysdatabases
                     where dbid             = db_id ()
                       and (status & 1024) <> 1024)))
      begin
         set ansinull                      on
         set chained                       off              -- Non-ANSI
         set flushmessage                  on
         set nocount                       on
         set string_rtruncation            on
         set transaction isolation level   1

         declare @firstxact  binary (6),
                 @lastxact   binary (6),
                 @tempxact   binary (6),
                 @xactcount  tinyint

         create table #lastxact1
            (opcode  varchar (62)    not null,
             sort1   binary (6)      not null,
             sort2   smallint        not null,
             seq     numeric (7, 0)  identity)

         create table #lastxact2
            (opcode  varchar (62)    not null,
             sort1   binary (6)      not null,
             sort2   smallint        not null,
             seq     numeric (7, 0)  not null)

         select distinct xactid
         into #xact
         from syslogs

         select @firstxact = xactid,
                @lastxact  = xactid,
                @xactcount = 1
         from #xact

         while (@xactcount < @XACTS)
            begin
               select @firstxact = max (xactid)
               from #xact
               where xactid < @firstxact

               select @xactcount = @xactcount + 1
            end

         insert into #lastxact1
            select isnull (substring (vOP.name, 1, 62),
                           'op = ' +  convert (varchar (3), l.op)),
                   xactid,
                   op
            from syslogs l  left join   sybsystemprocs.dbo.usptvalues vOP
              on l.op     = vOP.number
             and vOP.type = 'OP'
            where xactid between @firstxact and @lastxact

         insert into #lastxact2
            select *
            from #lastxact1

         insert into #lastxact2
            select ' ', sort1, -1, min (seq)
            from #lastxact2
            group by sort1

         delete from #lastxact2
         where sort1 = (select min (sort1)
                        from #lastxact2)
           and sort2 = -1

         select OPERATION = opcode
         from #lastxact2
         order by sort1, seq, sort2
      end
   else
      begin
         print '    sp_lastxact CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         print '    @@TRANCHAINED = %1!, @@TRANCOUNT = %2!.', @@tranchained, @@trancount
      end

   print ' '
go
sp_addcomment sp_lastxact, 'Lists composition of last transactions in syslogs (default XACTS is 1)'
go
sp_procxmode sp_lastxact, anymode
go


------------ Temporary tables needed for creation of sp_lockconfig_format ----

create table #lockcfg
  (sort      tinyint          not null,
   type      char (8)         not null,
   uid       smallint         not null,
   name      varchar (65)     not null,
   levelx    varchar ( 5)     not null,
   txt       varchar (55)     not null)
go

create table #lockcfg2
  (name      varchar (65)     not null,
   levelx    varchar ( 5)     not null,
   seq       int              not null,
   ident     numeric (17, 0)  identity)
go

create table #lockcfgtxt
   (txt  varchar (77))
go


create procedure sp_lockconfig_format as
   --
   --  SINCE THIS PROCEDURE IS CALLED SEVERAL TIMES FROM sp_lockconfig AND
   --  THE seq VALUES SHOULD START WITH 0, BUT IDENTITIES ALWAYS INCREMENT, THE
   --  seq VALUES HAVE TO BE RESET SEVERAL TIMES.  "BLANK" ROWS HAVE TO BE
   --  INSERTED SO THAT THE MULTI VIRTUAL TABLE JOIN FOR THE INSERT WORKS
   --  CORRECTLY.
   --
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   declare @cmdstring1  varchar (255),
           @cmdstring2  varchar (255),
           @cmdstring3  varchar (255),
           @cmdstring4  varchar (255),
           @i           tinyint,
           @numcols     tinyint,
           @namelen     tinyint

   update #lockcfg
   set name = l.name  +  ' ('  +  user_name (l.uid)  +  ')'
   from #lockcfg l
   where (l.uid <> 1)
      or (1 < (select count (o.uid)
               from sysobjects o
               where o.name            = l.name
                 and (o.sysstat & 15) in (1, 3)))

   insert into #lockcfg2
      select name, levelx, 0
      from #lockcfg
      order by name

   update #lockcfg2
   set seq = convert (int, ident)

   select @i       = 1,
          @namelen = 1 + max (char_length (name))
   from #lockcfg2

   select @numcols = (77 / @namelen)

   if (@numcols > 5)
      begin
         select @numcols = 5
      end

   select @i = min (seq)
   from #lockcfg2

   update #lockcfg2
   set seq = seq - @i

   select @i = 1 + max (seq)
   from #lockcfg2

   while (((@i % @numcols) <> 0)  or  (@i < @numcols))
      begin
         insert into #lockcfg2
            values (' ', ' ', 0)

         select @i = @i + 1
      end

   update #lockcfg2
   set seq = convert (int, ident)

   select @i = min (seq)
   from #lockcfg2

   update #lockcfg2
   set seq = seq - @i

   select @cmdstring1 = 'insert into #lockcfgtxt select l0.name + space ('
                        +  convert (varchar (2), @namelen)
                        +  '-char_length (l0.name))',
          @cmdstring2 = ' ',
          @cmdstring3 = ' from #lockcfg2 l0',
          @cmdstring4 = ' where (l0.seq % '
                        +  convert (varchar (2), @numcols)
                        +  ') = 0',
          @i          = 1

   if (@numcols > 1)
      begin
         select @cmdstring1 = @cmdstring1
                              +  ' + l1.name  +  space ('
                              +  convert (varchar (2), @namelen)
                              +  '-char_length (l1.name))'

         if (@numcols > 2)
            begin
               select @cmdstring2 = ' + l2.name  +  space ('
                                    +  convert (varchar (2), @namelen)
                                    +  '-char_length (l2.name))'

               if (@numcols > 3)
                  begin
                     select @cmdstring2 = @cmdstring2
                                          +  ' + l3.name  +  space ('
                                          +  convert (varchar (2), @namelen)
                                          +  '-char_length (l3.name))'

                     if (@numcols > 4)
                        begin
                           select @cmdstring2 = @cmdstring2
                                                +  ' + l4.name  +  space ('
                                                +  convert (varchar (2), @namelen)
                                                +  '-char_length (l4.name))'
                        end
                  end
            end
      end

   while (@i < @numcols)
      begin
         select @cmdstring3 = @cmdstring3
                              +  ',#lockcfg2 l'
                              +  convert (char (1), @i)

         select @cmdstring4 = @cmdstring4
                              +  ' and (l'
                              +  convert (char (1), @i)
                              +  '.seq % '
                              +  convert (varchar (2), @numcols)
                              +  ')='
                              +  convert (char (1), @i)
                              +  ' and l'
                              +  convert (char (1), @i)
                              +  '.seq=1+l'
                              +  convert (char (1), @i-1)
                              +  '.seq'

         select @i = @i + 1
      end

   execute  (@cmdstring1 + @cmdstring2 + @cmdstring3 + @cmdstring4)

   select 'TABLES' = txt
   from #lockcfgtxt
go
sp_existsprocedure sp_lockconfig
go

drop table #lockcfg, #lockcfg2, #lockcfgtxt
go


create procedure sp_lockconfig (@SYS_FLAG  char (1) = NULL) 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_lockconfig CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @allcount   varchar (  7),
           @cmdstring  varchar (255),
           @dpcount    varchar (  7),
           @drcount    varchar (  7),
           @namelen    tinyint,
           @sysval     smallint,
           @tabtext    varchar ( 12),
           @textlen    tinyint

   create table #lockcfg
     (sort      tinyint          not null,
      type      char (8)         not null,
      uid       smallint         not null,
      name      varchar (65)     not null,
      levelx    varchar ( 5)     not null,
      txt       varchar (55)     not null)

   create table #lockcfg2
     (name      varchar (65)     not null,
      levelx    varchar ( 5)     not null,
      seq       int              not null,
      ident     numeric (17, 0)  identity)

   create table #lockcfgtxt
      (txt  varchar (77))

   insert into #lockcfg
      select 1,
             'Table',
             o.uid,
             object_name (a.object),
             'page',
             substring (a.char_value, 1, 55)
      from sysattributes a,
           sysobjects o
      where a.class       = 5
        and a.attribute   = 0
        and a.object_type = 'T'
        and o.id          = a.object

   insert into #lockcfg
      select 1,
             'Table',
             o.uid,
             object_name (a.object),
             'row',
             substring (a.char_value, 1, 55)
      from sysattributes a,
           sysobjects o
      where a.class       = 5
        and a.attribute   = 0
        and a.object_type = 'T'
        and o.id          = a.object

   insert into #lockcfg
      select 2,
             'Database',
             0,
             db_name (),
             'page',
             substring (char_value, 1, 55)
      from master.dbo.sysattributes
      where class       = 5
        and attribute   = 0
        and object_type = 'D'
        and object      = db_id ()

   insert into #lockcfg
      select 2,
             'Database',
             1,
             db_name (),
             'row',
             substring (char_value, 1, 55)
      from master.dbo.sysattributes
      where class       = 5
        and attribute   = 1
        and object_type = 'D'
        and object      = db_id ()

   insert into #lockcfg
      select 3,
             'Server',
             1,
             'default lock scheme',
             '-',
             substring (c.value2, 1, 10)
      from master.dbo.sysconfigures f,
           master.dbo.syscurconfigs c
      where f.name    = 'lock scheme'
        and f.parent <> 19
        and f.config <> 19
        and c.config  = f.config

   insert into #lockcfg
      select 3,
             'Server',
             1,
             '-',
             'page',
             'PCT = '
             +  convert (varchar (11), pc.value)
             +  ', LWM = '
             +  convert (varchar (11), lc.value)
             +  ', HWM = '
             +  convert (varchar (11), hc.value)
      from master.dbo.sysconfigures pf,
           master.dbo.sysconfigures lf,
           master.dbo.sysconfigures hf,
           master.dbo.syscurconfigs pc,
           master.dbo.syscurconfigs lc,
           master.dbo.syscurconfigs hc
      where pf.config  = pc.config
        and pf.name    = 'page lock promotion PCT'
        and pf.parent <> 19
        and pf.config <> 19
        and lf.config  = lc.config
        and lf.name    = 'page lock promotion LWM'
        and lf.parent <> 19
        and lf.config <> 19
        and hf.config  = hc.config
        and hf.name    = 'page lock promotion HWM'
        and hf.parent <> 19
        and hf.config <> 19

   insert into #lockcfg
      select 3,
             'Server',
             1,
             '-',
             'row',
             'PCT = '
             +  convert (varchar (11), pc.value)
             +  ', LWM = '
             +  convert (varchar (11), lc.value)
             +  ', HWM = '
             +  convert (varchar (11), hc.value)
      from master.dbo.sysconfigures pf,
           master.dbo.sysconfigures lf,
           master.dbo.sysconfigures hf,
           master.dbo.syscurconfigs pc,
           master.dbo.syscurconfigs lc,
           master.dbo.syscurconfigs hc
      where pf.config  = pc.config
        and pf.name    = 'row lock promotion PCT'
        and pf.parent <> 19
        and pf.config <> 19
        and lf.config  = lc.config
        and lf.name    = 'row lock promotion LWM'
        and lf.parent <> 19
        and lf.config <> 19
        and hf.config  = hc.config
        and hf.name    = 'row lock promotion HWM'
        and hf.parent <> 19
        and hf.config <> 19

   update #lockcfg
   set name = l.name  +  ' ('  +  user_name (l.uid)  +  ')'
   from #lockcfg l
   where l.type = 'Table'
     and ((l.uid <> 1)  or
          (1 < (select count (o.uid)
                from sysobjects o
                where o.name            = l.name
                  and (o.sysstat & 15) in (1, 3))))

   select @namelen = max (char_length (name)),
          @textlen = max (char_length (txt))
   from #lockcfg

   select @cmdstring = 'select TYPE = type,'
                       +  'OBJECT = substring (name, 1,'
                       +  convert (varchar (2), @namelen)
                       +  '),'
                       +  '''LEVEL'' = levelx,'
                       +  '''LOCK DATA'' = substring (txt, 1,'
                       +  convert (varchar (2), @textlen)
                       +  ')'
                       +  ' from #lockcfg'
                       +  ' order by sort, name, levelx'

   execute  (@cmdstring)

   print ' '

   if (@SYS_FLAG IS NULL)
      begin
         select @sysval  = 3,
                @tabtext = 'USER'
      end
   else
      begin
         select @sysval  = 1,
                @tabtext = 'USER/SYSTEM'
      end

   select @allcount = ltrim (substring (convert (char (10),
                                                 convert (money,
                                                          count (*)),
                                                 1),
                                        1,
                                        7))
   from sysobjects
   where (sysstat & 15)     in (@sysval, 3)
     and (sysstat2 & 16384) <> 16384
     and (sysstat2 & 32768) <> 32768

   select @dpcount = ltrim (substring (convert (char (10),
                                                convert (money,
                                                         count (*)),
                                                1),
                                       1,
                                       7))
   from sysobjects
   where (sysstat & 15)     in (@sysval, 3)
     and (sysstat2 & 16384)  = 16384

   select @drcount = ltrim (substring (convert (char (10),
                                                convert (money,
                                                         count (*)),
                                                1),
                                       1,
                                       7))
   from sysobjects
   where (sysstat & 15)     in (@sysval, 3)
     and (sysstat2 & 32768)  = 32768

   if ((@allcount <> '0')  and  (@dpcount = '0')  and  (@drcount = '0'))
      begin
         print '    ALL %1! TABLES USE ALLPAGES LOCKING.', @tabtext
      end
   else if ((@allcount = '0')  and  (@dpcount <> '0')  and  (@drcount = '0'))
      begin
         print '    ALL %1! TABLES USE DATAPAGES LOCKING.', @tabtext
      end
   else if ((@allcount = '0')  and  (@dpcount = '0')  and  (@drcount <> '0'))
      begin
         print '    ALL %1! TABLES USE DATAROWS LOCKING.', @tabtext
      end
   else
      begin
         if (@allcount = '0')
            begin
               print '    THERE ARE NO %1! TABLES WITH ALLPAGES LOCKING.', @tabtext
            end
         else
            begin
               print '    THERE ARE %1! %2! TABLES WITH ALLPAGES LOCKING.',
                     @allcount, @tabtext

               print ' '

               truncate table #lockcfg

               insert into #lockcfg
                  select 1, ' ', uid, name, ' ', ' '
                  from sysobjects
                  where (sysstat & 15)      in (@sysval, 3)
                    and (sysstat2 & 16384)  <> 16384
                    and (sysstat2 & 32768)  <> 32768

               execute  sp_lockconfig_format
            end

         print ' '

         if (@dpcount = '0')
            begin
               print '    THERE ARE NO %1! TABLES WITH DATAPAGES LOCKING.',
                     @tabtext
            end
         else
            begin
               print '    THERE ARE %1! %2! TABLES WITH DATAPAGES LOCKING.',
                     @dpcount, @tabtext

               print ' '

               truncate table #lockcfg

               truncate table #lockcfg2

               truncate table #lockcfgtxt

               insert into #lockcfg
                  select 1, ' ', uid, name, ' ', ' '
                  from sysobjects
                  where (sysstat & 15)     in (@sysval, 3)
                    and (sysstat2 & 16384)  = 16384

               execute  sp_lockconfig_format
            end

         print ' '

         if (@drcount = '0')
            begin
               print '    THERE ARE NO %1! TABLES WITH DATAROWS LOCKING.',
                     @tabtext
            end
         else
            begin
               print '    THERE ARE %1! %2! TABLES WITH DATAROWS LOCKING.',
                     @drcount, @tabtext

               print ' '

               truncate table #lockcfg

               truncate table #lockcfg2

               truncate table #lockcfgtxt

               insert into #lockcfg
                  select 1, ' ', uid, name, ' ', ' '
                  from sysobjects
                  where (sysstat & 15)     in (@sysval, 3)
                    and (sysstat2 & 32768)  = 32768

               execute  sp_lockconfig_format
            end
      end

   print ' '
go
sp_addcomment sp_lockconfig, 'Lists data for lock promotions and index locking schemes'
go
sp_addcomment sp_lockconfig, '   if SYS_FLAG is non-null include system tables'
go
sp_procxmode sp_lockconfig, anymode
go

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

         print ' '

         return 1
      end

   declare @logvalue  int,
           @maxerror  int

   select @logvalue = hextoint ('0x80')

   select @maxerror = max (error)
   from sysusermessages
   where (dlevel & @logvalue) = @logvalue

   if (db_id () = 1)
      begin
         if (@maxerror IS NULL)
            begin
               select @maxerror = max (error)
               from master.dbo.sysmessages
               where severity             < 19
                 and (dlevel & @logvalue) = @logvalue
            end

         if (@maxerror IS NULL)
            begin
               print '    ALL NON-FATAL SYSTEM MESSAGES AND USER MESSAGES ARE UNLOGGED.'

               print ' '
            end
         else
            begin
               print '    THERE ARE LOGGED NON-FATAL SYSTEM MESSAGES AND/OR USER MESSAGES.'

               print ' '

              if (@maxerror > 32766)
                 begin
                        select 'ERROR #'   = 0,
                               'SVR'       = convert (tinyint, 0),
                               DESCRIPTION = space (61)
                        where 1 = 2
                     union
                        select error,
                               convert (tinyint, severity),
                               substring (description, 1, 61)
                        from master.dbo.sysmessages
                        where severity             < 19
                          and (dlevel & @logvalue) = @logvalue
                     union
                        select error,
                               convert (tinyint, 16),
                               substring (description, 1, 61)
                        from sysusermessages
                        where (dlevel & @logvalue) = @logvalue
                     order by 'ERROR #'
                 end
              else
                 begin
                       select 'ERROR #'   = convert (smallint, 0),
                              'SVR'       = convert (tinyint, 0),
                              DESCRIPTION = space (65)
                       where 1 = 2
                    union
                       select convert (smallint, error),
                              convert (tinyint, severity),
                              substring (description, 1, 65)
                       from master.dbo.sysmessages
                       where severity             < 19
                         and (dlevel & @logvalue) = @logvalue
                    union
                       select convert (smallint, error),
                              convert (tinyint, 16),
                              substring (description, 1, 65)
                       from sysusermessages
                       where (dlevel & @logvalue) = @logvalue
                    order by 'ERROR #'
                 end
            end
      end
   else
      begin
         if (@maxerror IS NULL)
            begin
               print '    ALL USER MESSAGES ARE UNLOGGED.'

               print ' '
            end
         else
            begin
               print '    THERE ARE LOGGED USER MESSAGES.'

               print ' '

              if (@maxerror > 32766)
                 begin
                    select 'ERROR #'   = error,
                           DESCRIPTION = substring (description, 1, 65)
                    from sysusermessages
                    where (dlevel & @logvalue) = @logvalue
                    order by error
                 end
              else
                 begin
                    select 'ERROR #'   = convert (smallint, error),
                           DESCRIPTION = substring (description, 1, 69)
                    from sysusermessages
                    where (dlevel & @logvalue) = @logvalue
                    order by error
                 end
            end
      end
go
sp_addcomment sp_loggedmsgs, 'Lists logged user messages / non-fatal system messages'
go
sp_procxmode sp_loggedmsgs, anymode
go

sp_addcomment sp_new, 'Lists objects newer than DAYS (default is 4) days'
go
sp_procxmode sp_new, anymode
go

create procedure sp_newstats (@DAYS tinyint = 4) 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_newstats CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring           varchar (255),
           @i                   tinyint,
           @newest_stats_date   datetime,
           @newest_stats_print  varchar ( 32),
           @rows                varchar (  7),
           @tables              varchar (  7)

   create table #newstats
     (id            int              not null,
      uid           smallint         not null,
      name          varchar (30)     not null,
      prntname      varchar (63)     not null,
      crdate        datetime         not null,
      statid        smallint         not null,
      actual_steps  char (5)         null,
      colidarray    varbinary (100)  null)

   execute  sp_date

   set nocount off

   print ' '

   select @rows   = ltrim (substring (convert (char (10), convert (money, count (id)), 1), 1, 7)),
          @tables = ltrim (substring (convert (char (10), convert (money, count (distinct id)), 1), 1, 7))
   from sysstatistics
   where id       < 100
     and formatid = 100

   print '    THERE ARE %1! STATISTICS ROWS FOR %2! SYSTEM TABLES IN systatistics.',
         @rows, @tables

   print ' '

   select @rows   = ltrim (substring (convert (char (10), convert (money, count (id)), 1), 1, 7)),
          @tables = ltrim (substring (convert (char (10), convert (money, count (distinct id)), 1), 1, 7))
   from sysstatistics
   where id       > 99
     and formatid = 100

   print '    THERE ARE %1! STATISTICS ROWS FOR %2! USER TABLES IN systatistics.',
         @rows, @tables

   print ' '

   insert into #newstats
      select o.id,
             o.uid,
             o.name,
             o.name
             +  ' ('
             +  col_name (s.id, convert (tinyint, substring (s.colidarray, 1, 1))),
             s.moddate,
             s.statid,
             substring (convert (char (8), convert (money, convert (int, s.c4)), 1), 1, 5),
             s.colidarray
      from sysobjects o,
           sysstatistics s
      where datediff (dd, s.moddate, getdate ()) < @DAYS
        and s.formatid                           = 100
        and s.sequence                           = 1
        and o.id                                 = s.id

   select @i = 2

   while (@i < 9)
      begin
         update #newstats
         set prntname = substring (prntname
                                   +  ', '
                                   +  col_name (id, convert (tinyint, substring (colidarray, @i, 1))),
                                   1,
                                   63)
         where @i <= datalength (colidarray)

         select @i = @i + 1
      end

   update #newstats
   set prntname = prntname  +  ')'
   where char_length (prntname)       < 62
      or substring (prntname, 62, 1) <> ','

   update #newstats
   set prntname = substring (user_name (uid)  +  '.'  +  prntname, 1, 63)
   from #newstats n
   where (n.uid <> 1)
      or (1 < (select count (o.id)
               from sysobjects o
               where (o.sysstat & 15) = 3
                 and o.name           = n.name))

   if (exists (select *
               from #newstats))
      begin
         select @i = max (char_length (prntname))
         from #newstats

         if (@i > 56)
            begin
              select @i = 56
            end

         select @cmdstring = 'select ''TABLE (COLUMNS)'' = substring (prntname, 1,'
                             +  convert (varchar (2), @i)
                             +  '),'
                             +  '''CREATE/MODIFY'' = convert (char (8), crdate, 11)'
                             +  '  +  '' '''
                             +  '  +  substring (convert (char (8), crdate, 8), 1, 5),'
                             +  'STEPS = coalesce (actual_steps, ''    -'')'
                             +  ' from #newstats'
                             +  ' order by crdate, prntname'

         execute  (@cmdstring)
      end
   else
      begin
         print '    ALL COLUMN STATISTICS ARE OLDER THAN %1! DAYS.', @DAYS

         print ' '

         select @newest_stats_date = max (moddate)
         from sysstatistics
         where formatid = 100


         if (@newest_stats_date IS NULL)
            begin
               print '    THERE ARE NO COLUMN STATISTICS ROWS.'
            end
         else
            begin
               execute  sp_formatdate  @FORMATED_DATE=@newest_stats_print output,
                                       @INPUT_DATE=@newest_stats_date

               print '    THE NEWEST COLUMN STATISTICS WERE CREATED/MODIFIED ON %1!.',
                     @newest_stats_print
            end

         print ' '
      end
go
sp_addcomment sp_newstats, 'Lists column statistics newer than DAYS (default is 4) days'
go
sp_procxmode sp_newstats, anymode
go

create procedure sp_segmentdetail (@SEGMENT varchar (30) = 'logsegment') as
   set ansinull                      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_segmentdetail CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @segnum  smallint

   print ' '

   select @segnum = segment
   from syssegments
   where name = @SEGMENT

   if (@segnum IS NULL)
      begin
         print '    SEGMENT %1! DOES NOT EXIST.', @SEGMENT

         print ' '
      end
   else
      begin
         print '    DETAILS ARE FOR SEGMENT %1!.', @SEGMENT

         print ' '

         if (exists (select *
                     from systhresholds
                     where segment = @segnum))
            begin
               select THRESHOLD          = substring (convert (char (12),
                                                               convert (money,
                                                                        t.free_space),
                                                               1),
                                                      1,
                                                      9),
                      'LCT?'             = substring (vB.name, 1, 4),
                      'STORED PROCEDURE' = substring (t.proc_name, 1, 30),
                      OWNER              = substring (suser_name (t.suid), 1, 18)
               from systhresholds         t,
                    sybsystemprocs.dbo.usptvalues vB
               where t.segment = @segnum
                 and t.status  = vB.number
                 and vB.type   = 'B'
               order by free_space
            end
         else
            begin
               print '    SEGMENT %1! HAS NO THRESHOLDS.', @SEGMENT
            end

         print ' '

            select DEVICE        = space (14),
                   'DEDICATED?'  = space (9),
                   'TOTAL PAGES' = space (11),
                   'FREE PAGES'  = space (11)
            where 1 = 2
         union all
            select substring (d.name, 1, 14),
                   'Exclusive',
                   substring (convert (char (14), convert (money, u.size), 1),
                              1,
                              11),
                   substring (convert (char (14),
                                       convert (money,
                                                curunreservedpgs (db_id (),
                                                                  u.lstart,
                                                                  u.unreservedpgs)),
                                       1),
                              1,
                              11)
            from syssegments s,
                 master.dbo.sysdevices d,
                 master.dbo.sysusages u
            where s.name                = @SEGMENT
              and d.cntrltype           = 0
              and power (2, s.segment)  = (u.segmap & power (2, s.segment))
              and u.dbid                = db_id ()
              and d.low                <= u.vstart + u.size
              and d.high               >= u.vstart + u.size - 1
              and 1                     = (select count (s2.segment)
                                           from syssegments s2,
                                                master.dbo.sysusages u2
                                           where u.vstart              = u2.vstart
                                             and power (2, s2.segment) = (u2.segmap & power (2, s2.segment)))
         union all
            select substring (d.name, 1, 14),
                   'Shared',
                   substring (convert (char (14), convert (money, u.size), 1),
                              1,
                              11),
                   substring (convert (char (14),
                                       convert (money,
                                                curunreservedpgs (db_id (),
                                                                  u.lstart,
                                                                  u.unreservedpgs)),
                                       1),
                              1,
                              11)
            from syssegments s,
                 master.dbo.sysdevices d,
                 master.dbo.sysusages u
            where s.name                = @SEGMENT
              and d.cntrltype           = 0
              and power (2, s.segment)  = (u.segmap & power (2, s.segment))
              and u.dbid                = db_id ()
              and d.cntrltype           = 0
              and d.low                <= u.vstart + u.size
              and d.high               >= u.vstart + u.size - 1
              and 1                     < (select count (s2.segment)
                                           from syssegments s2,
                                                master.dbo.sysusages u2
                                           where u.vstart              = u2.vstart
                                             and power (2, s2.segment) = (u2.segmap & power (2, s2.segment)))
         order by 'TOTAL PAGES'

         print ' '

         if (exists (select i.*
                     from sysindexes i,
                          syssegments s
                     where i.segment = s.segment
                       and s.name    = @SEGMENT))
            begin
                  select OWNER    = space (12),
                         TYPE     = space (17),
                         'OBJECT' = space (44)
                  where 1 = 2
               union all
                  select substring (user_name (o.uid), 1, 12),
                         substring (vO.name, 1, 17),
                         o.name
                  from sysindexes i,
                       sysobjects o,
                       syssegments s,
                       sybsystemprocs.dbo.usptvalues vO
                  where s.name    = @SEGMENT
                    and i.segment = s.segment
                    and i.id      = o.id
                    and i.indid   = 0
                    and vO.number = convert (int, o.sysstat & 15)
                    and vO.type   = 'O'
               union all
                  select substring (user_name (o.uid), 1, 12),
                         'clustered index',
                         substring (object_name (i.id)  +  '.'  +  i.name,
                                    1,
                                    44)
                  from sysindexes i,
                       sysobjects o,
                       syssegments s
                  where s.name    = @SEGMENT
                    and i.segment = s.segment
                    and i.id      = o.id
                    and i.indid   = 1
               union all
                  select substring (user_name (o.uid), 1, 12),
                         'nonclustered index ',
                         substring (object_name (i.id)  +  '.'  +  i.name,
                                    1,
                                    44)
                  from sysindexes i,
                       sysobjects o,
                       syssegments s
                  where s.name    = @SEGMENT
                    and i.segment = s.segment
                    and i.id      = o.id
                    and i.indid   > 1
               order by 'OBJECT', OWNER
            end
         else
            begin
               print '    SEGMENT %1! IS UNUSED.', @SEGMENT

               print ' '
            end
      end
go
sp_addcomment sp_segmentdetail, 'Lists threshold, device, and object information for SEGMENT'
go
sp_addcomment sp_segmentdetail, '   default SEGMENT is "logsegment"'
go
sp_procxmode sp_segmentdetail, anymode
go

create procedure sp_segments as
   set ansinull                      off              -- Non-ANSI
   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_segments CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring1  varchar (255),
           @cmdstring2  varchar (255),
           @dbname      varchar ( 32),
           @devlen      tinyint,
           @objlen      tinyint,
           @ownerlen    tinyint,
           @pct         double precision,
           @seglen      tinyint,
           @thalen      tinyint,
           @thcount     tinyint

   print ' '

   select @dbname = db_name (),
          @pct    = 100

   create table #segments1
      (segname    varchar (34)  not null,
       th_size    char (8)      not null,
       sp         varchar (30)  null,
       owner      varchar (30)  null)

   create table #segments2
      (segnum     smallint      not null,
       segname    varchar (34)  not null,
       status     smallint      not null,
       objcount   varchar (30)  not null,
       fs_size    char (8)      not null,
       lct        char (3)      not null,
       sp         varchar (30)  null)

   create table #segments3
      (phyname    varchar (30)      not null,
       segname    varchar (30)      not null,
       devname    varchar (30)      not null,
       dedicated  char (9)          not null,
       fpage      int               not null,
       size       int               not null,
       free       int               not null,
       freepct    double precision  not null)

   if (exists (select *
               from systhresholds))
       begin
          if (exists (select *
                      from master.dbo.sysdatabases
                      where dbid          = db_id ()
                        and (status2 & 2) = 2))
             begin
               print '    THERE IS NO FREE-SPACE ACCOUNTING FOR DATABASE%1!.',
                     @dbname

               print ' '
             end

          insert into #segments1
             select s.name,
                    substring (convert (char (10), convert (money, t.free_space), 1), 1, 7),
                    t.proc_name,
                    suser_name (t.suid)
             from syssegments   s,
                  systhresholds t
             where s.segment = t.segment
               and (not exists (select o.*
                                from sysobjects o
                                where o.name           = t.proc_name
                                  and (o.sysstat & 15) = 4))
               and (not exists (select so.*
                                from sybsystemprocs.dbo.sysobjects so
                                where so.name           = t.proc_name
                                  and (so.sysstat & 15) = 4))

          if (exists (select *
                      from #segments1))
             begin
               print '    THERE ARE NON-EXISTENT THRESHOLD PROCEDURES.'

               print ' '

               select @seglen   = max (char_length (segname)),
                      @thalen   = max (char_length (sp)),
                      @ownerlen = max (char_length (owner))
               from #segments1

               select @cmdstring1 = 'select SEGMENT = substring (segname, 1,'
                                    +  convert (varchar (2), @seglen)
                                    +  '),'
                                    +  '''TH SIZE'' = th_size,'
                                    +  '''TH STORED PROCEDURE'' = substring (sp, 1,'
                                    +  convert (varchar (2), @thalen)
                                    +  '),'
                                    +  'OWNER = substring (owner, 1,'
                                    +  convert (varchar (2), @ownerlen)
                                    +  ')'
                                    +  ' from #segments1'
                                    +  ' order by segname, th_size'

               execute  (@cmdstring1)
             end
       end

   insert into #segments2                                -- unused segment
      select segment,
             name,
             status,
             '                     0 objects',
             ' ',
             'no',
             NULL
      from syssegments s
      where (not exists (select i.*
                         from sysindexes i
                         where i.segment = s.segment))

   insert into #segments2                                -- 1 object on segment
      select s.segment,
             s.name,
             s.status,
             substring (space (30 - char_length (i.name)) + i.name, 1, 30),
             ' ',
             'no',
             NULL
      from sysindexes i,
           syssegments s
      where s.segment = i.segment
        and i.indid   = 0
        and 1         = (select count (ii.indid)
                         from sysindexes ii
                         where ii.segment = s.segment)

   insert into #segments2                                -- 1 index on segment
      select s.segment,
             s.name,
             s.status,
             substring (space (30 - char_length (object_name (i.id) + '.' + i.name))
                        + object_name (i.id) + '.' + i.name, 1, 30),
             ' ',
             'no',
             NULL
      from sysindexes i,
           syssegments s
      where s.segment = i.segment
        and i.indid   = 1
        and 1         = (select count (ii.indid)
                         from sysindexes ii
                         where ii.segment = s.segment)

   insert into #segments2                                -- N objects on segment
      select distinct s.segment,
                      s.name,
                      s.status,
                      substring (convert (char (25),
                                          convert (money,
                                                   count (i.indid)),
                                          1),
                                 1,
                                 22)  +  ' objects',
             ' ',
             'no',
             NULL
      from sysindexes i,
           syssegments s
      where s.segment = i.segment
        and 1         < (select count (ii.indid)
                         from sysindexes ii
                         where ii.segment = s.segment)
      group by s.segment

   update #segments2
   set segname = segname + ' *'
   where status = 1

   update #segments2
   set lct = vB.name
   from systhresholds         t,
        sybsystemprocs.dbo.usptvalues vB
   where t.segment = #segments2.segnum
     and t.status  = vB.number
     and vB.type   = 'B'

   select @thcount = max (count(*))
   from systhresholds
   group by segment

   print ' '

   if (@thcount IS NULL)
      begin
         print '    THERE ARE NO THRESHOLD STORED PROCEDURES.'

         print ' '

         select @seglen = max (char_length (segname)),
                @objlen = max (char_length (ltrim (objcount)))
         from #segments2

         select @cmdstring1 = 'select NUMBER = segnum,'
                              +  'SEGMENT = substring (segname, 1,'
                              +  convert (varchar (2), @seglen)
                              +  '),'
                              +  '''OBJECT(S)'' = substring (objcount,'
                              +  convert (varchar (2), 31 - @objlen)
                              +  ','
                              +  convert (varchar (2), @objlen)
                              +  ')'
                              +  ' from #segments2'
                              +  ' order by segname'

         execute  (@cmdstring1)
      end
   else if (@thcount = 1)
      begin
         update #segments2
         set fs_size = substring (convert (char (11), convert (money, t.free_space), 1), 1, 8),
             sp      = t.proc_name
         from systhresholds t
         where t.segment = #segments2.segnum

         select @seglen = max (char_length (segname)),
                @thalen = max (char_length (sp)),
                @objlen = max (char_length (ltrim (objcount)))
         from #segments2

         select @cmdstring1 = 'select NUMBER = segnum,'
                              +  'SEGMENT = substring (segname, 1,'
                              +  convert (varchar (2), @seglen)
                              +  '),'
                              +  '''OBJECT(S)'' = substring (objcount,'
                              +  convert (varchar (2), 31 - @objlen)
                              +  ','
                              +  convert (varchar (2), @objlen)
                              +  '),'
                              +  '''LCT?'' = lct,'
                              +  '''TH SIZE'' = fs_size,'
                              +  '''TH STORED PROCEDURE'' = isnull (substring (sp, 1,'
                              +  convert (varchar (2), @thalen)
                              +  '), ''-'')'
                              +  ' from #segments2'
                              +  ' order by segname'

         execute  (@cmdstring1)
      end
   else
      begin
         update #segments2
         set fs_size = (select substring (convert (char (11), convert (money, count (t.segment)), 1), 1, 8)
                        from systhresholds t
                        where t.segment = #segments2.segnum)

         select @seglen = max (char_length (segname)),
                @objlen = max (char_length (ltrim (objcount)))
         from #segments2

         select @cmdstring1 = 'select NUMBER = segnum,'
                              +  'SEGMENT = substring (segname, 1,'
                              +  convert (varchar (2), @seglen)
                              +  '),'
                              +  '''OBJECT(S)'' = substring (objcount,'
                              +  convert (varchar (2), 31 - @objlen)
                              +  ','
                              +  convert (varchar (2), @objlen)
                              +  '),'
                              +  '''TH COUNT'' = fs_size,'
                              +  '''LCT?'' = lct'
                              +  ' from #segments2'
                              +  ' order by segname'

         execute  (@cmdstring1)
      end

   print ' '

   insert into #segments3
      select isnull (substring (d.phyname, 11, 8), ' '),
             s.name,
             d.name,
             'Shared',
             u.vstart,
             u.size,
             curunreservedpgs (db_id (), u.lstart, u.unreservedpgs),
             0
      from syssegments s,
           master.dbo.sysdevices d,
           master.dbo.sysusages u
      where d.cntrltype           = 0
        and power (2, s.segment)  = (u.segmap & power (2, s.segment))
        and s.status              = 0
        and u.dbid                = db_id ()
        and d.low                <= u.vstart + u.size
        and d.high               >= u.vstart + u.size - 1

   insert into #segments3
      select isnull (substring (d.phyname, 11, 8), ' '),
             s.name  +  ' *',
             d.name,
             'Shared',
             u.vstart,
             u.size,
             curunreservedpgs (db_id (), u.lstart, u.unreservedpgs),
             0
      from syssegments s,
           master.dbo.sysdevices d,
           master.dbo.sysusages u
      where d.cntrltype           = 0
        and power (2, s.segment)  = (u.segmap & power (2, s.segment))
        and s.status              = 1
        and u.dbid                = db_id ()
        and d.low                <= u.vstart + u.size
        and d.high               >= u.vstart + u.size - 1

   update #segments3
   set dedicated = 'Exclusive'
   from #segments3 s
   where 1 = (select count (s2.segment)
              from syssegments s2, master.dbo.sysusages u2
              where s.fpage               = u2.vstart
                and power (2, s2.segment) = (u2.segmap & power (2, s2.segment)))

   update #segments3
   set freepct = (@pct * free) / size

   select @seglen = max (char_length (segname)),
          @devlen = max (char_length (devname))
   from #segments3

   select @cmdstring1 = 'select SEGMENT = substring (segname, 1,'
                        +  convert (varchar (2), @seglen)
                        +  '),'
                        +  'DEVICE = substring (devname, 1,'
                        +  convert (varchar (2), @devlen)
                        +  '),'
                        +  '''DEDICATED?'' = dedicated,'
                        +  '''TOTAL PAGES'' = substring (convert (char (14),'
                        +  'convert (money, size), 1), 1, 11),'

   select @cmdstring2 = '''FREE PAGES'' = substring (convert (char (14),'
                        +  'convert (money, free), 1), 1, 11),'
                        +  '''FREE%'' = str (freepct, 5, 1)'
                        +  ' from #segments3'
                        +  ' order by segname, phyname, fpage'

   execute  (@cmdstring1  +  @cmdstring2)
go
sp_addcomment sp_segments, 'Lists all segments with aggregate and device information'
go
sp_procxmode sp_segments, anymode
go

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

   print ' '

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

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

         print ' '

         return 1
      end

   declare @cachelen   tinyint,
           @cmdstring  varchar (255),
           @objlen     tinyint

   create table #strategies
     (objid      int            not null,
      objname    varchar (60)   not null,
      type       char (10)      not null,
      cachename  varchar (120)  not null,
      indid      tinyint        not null,
      prefetch   tinyint        not null,
      pftext     char (5)       not null,
      mru        tinyint        not null,
      mrutext    char (3)       not null)

   print '    ONLY OBJECTS THAT USE LRU OR DO NOT USE PREFETCH WILL BE LISTED.'

   print ' '

   insert into #strategies
      select o.id,
             o.name,
             'Table ('  +  convert (varchar (3), indid)  +  ')',
             '-',
             i.indid,
             ((i.status2 & 16) / 16),
             '  off',
             ((i.status2 & 32) / 32),
             'off'
      from sysobjects o, sysindexes i
      where (o.sysstat & 15) = 3
        and i.id             = o.id
        and i.indid          < 2

   insert into #strategies
      select o.id,
             o.name,
             'Index ('  +  convert (varchar (3), indid)  +  ')',
             '-',
             i.indid,
             ((i.status2 & 16) / 16),
             '  off',
             ((i.status2 & 32) / 32),
             'off'
      from sysobjects o, sysindexes i
      where (o.sysstat & 15) = 3
        and i.id             = o.id
        and i.indid          > 1
        and i.indid          < 254

   insert into #strategies
      select o.id,
             o.name,
             'Image/Text',
             '-',
             i.indid,
             ((i.status2 & 16) / 16),
             '  off',
             ((i.status2 & 32) / 32),
             'off'
      from sysobjects o, sysindexes i
      where (o.sysstat & 15) = 3
        and i.id             = o.id
        and i.indid          > 253

   delete from #strategies
   where prefetch = 0
     and mru      = 0

   if (exists (select *
               from #strategies))
      begin
         update #strategies
         set objname = s.objname  +  ' ('  +   user_name (o.uid)  +  ')'
         from #strategies s,
              sysobjects o
         where o.id = s.objid
           and ((o.uid <> 1)  or
                (1      < (select count (o2.uid)
                           from sysobjects o2
                           where o2.name           = s.objname
                             and (o2.sysstat & 15) = 3)))

         -- CACHES FOR TABLES WITHOUT CLUSTERED INDEXES

         update #strategies
         set cachename = a.char_value
         from #strategies s, sysattributes a
         where s.objid        <> 8
           and s.indid         = 0
           and a.class         = 3
           and a.attribute     = 0
           and a.object_type   = 'T'
           and a.object        = s.objid
           and a.object_info1  = 0

         -- CACHES FOR NON-CLUSTERED INDEXES

         update #strategies
         set cachename = a.char_value
         from #strategies s, sysattributes a
         where s.indid        > 0
           and s.indid        < 254
           and a.class        = 3
           and a.attribute    = 0
           and a.object_type = 'I'
           and a.object       = s.objid
           and a.object_info1 = s.indid

         -- CACHES FOR TABLES WITH CLUSTERED INDEXES

         update #strategies
         set cachename = isnull (t.char_value, '-')
         from #strategies s, sysattributes t
         where s.indid         = 1
           and t.class         = 3
           and t.attribute     = 0
           and t.object_type   = 'T'
           and t.object        = s.objid

         update #strategies
         set cachename = cachename  +  ' / '  +  isnull (ci.char_value, '-')
         from #strategies s, sysattributes ci
         where s.indid         = 1
           and ci.class        = 3
           and ci.attribute    = 0
           and ci.object_type = 'I'
           and ci.object       = s.objid
           and ci.object_info1 = s.indid

         -- CACHES FOR IMAGE/TEXT COLUMNS

         update #strategies
         set cachename = a.char_value
         from #strategies s, sysattributes a
         where s.indid        > 253
           and a.class        = 3
           and a.attribute    = 0
           and a.object_type = 'I'
           and a.object       = s.objid
           and a.object_info1 = s.indid

         update #strategies
         set pftext = '   on'
         where prefetch = 0

         update #strategies
         set mrutext = ' on'
         where mru = 0

         select @cachelen = max (char_length (cachename)),
                @objlen   = max (char_length (objname))
         from #strategies

         select @cmdstring = 'select OBJECT = substring (objname, 1,'
                             +  convert (varchar (3), @objlen)
                             +  '),'
                             +  'TYPE = type,'
                             +  'PFTCH = pftext,'
                             +  'MRU = mrutext,'
                             +  'CACHE = substring (cachename, 1,'
                             +  convert (varchar (3), @cachelen)
                             +  ')'
                             +  ' from #strategies'
                             +  ' order by objname, indid'

         execute  (@cmdstring)
      end
   else
      begin
         print '    ALL OBJECTS USE MRU AND PREFETCH WHENEVER POSSIBLE.'
      end

   print ' '
go
sp_addcomment sp_strategies, 'Displays all non-default Prefetch and MRU information'
go
sp_procxmode sp_strategies, anymode
go

------------ Temporary table needed for creation of sp_thalist subroutine -----

create table #thalist
   (dbname    varchar ( 65)  not null,
    creator   varchar ( 30)  not null,
    procname  varchar (255)  not null,
    freespace int            not null,
    sort      varchar (255)  not null)
go

create procedure sp_thalist_db as
   set ansinull                      on
   set string_rtruncation            on

   insert into #thalist
      select db_name (),
             coalesce (suser_name (t.suid), '(unknown)'),
             t.proc_name  +  ' '  +  char (32 + (3 * (t.status & 1))),
             t.free_space,
             db_name ()
      from syssegments s,
           systhresholds t
      where s.segment = 2
        and t.segment = 2

   insert into #thalist
      select db_name ()  +  ' ('  +  s.name  +  ')',
             coalesce (suser_name (t.suid), '(unknown)'),
             t.proc_name,
             t.free_space,
             db_name () +  ' ('  +  s.name  +  ')'
      from syssegments s,
           systhresholds t
      where s.segment <> 2
        and t.segment  = s.segment
go
sp_existsprocedure sp_thalist_db
go

drop table #thalist
go

create procedure sp_thalist (@PROCSORT_FLAG  varchar (1) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @cmdstring  varchar (255),
           @dblen      tinyint,
           @ownerlen   tinyint,
           @proclen    tinyint

   create table #thalist
      (dbname    varchar ( 65)  not null,
       creator   varchar ( 30)  not null,
       procname  varchar (255)  not null,
       freespace int            not null,
       sort      varchar (255)  not null)

   if (charindex ('sa_role', show_role ()) > 0)
      begin
         execute  sp_globalexec  @UTILITY_SP='sp_thalist_db'

         if (exists (select *
                     from #thalist))
            begin
               select @dblen    = max (char_length (dbname)),
                      @ownerlen = max (char_length (creator)),
                      @proclen  = max (char_length (procname))
               from #thalist

               if (@dblen < 8)
                  begin
                     select @dblen = 8
                  end

               if (@ownerlen < 7)
                  begin
                     select @ownerlen = 7
                  end

               if (@proclen < 19)
                  begin
                     select @proclen = 19
                  end

               if ((@dblen + @ownerlen + @proclen) > 67)
                  begin
                     while ((@dblen + @ownerlen + @proclen) > 67)
                        begin
                           if (@dblen > 8)
                              begin
                                 select @dblen = @dblen - 1
                              end

                           if (@ownerlen > 7)
                              begin
                                 select @ownerlen = @ownerlen - 1
                              end

                           if (@proclen > 19)
                              begin
                                 select @proclen = @proclen - 1
                              end
                        end

                     select @proclen = 67 - @dblen - @ownerlen
                  end

               if (@PROCSORT_FLAG IS NULL)
                  begin
                     update #thalist
                     set dbname = ' '
                     from #thalist t1
                     where exists (select *
                                   from #thalist t2
                                   where t2.dbname    = t1.dbname
                                     and t2.freespace = t1.freespace
                                     and t2.freespace < (select max (freespace)
                                                         from #thalist t3
                                                         where t3.dbname = t2.dbname))

                     select @cmdstring = 'select ''DATABASE''=substring (dbname, 1,'
                                         +  convert (varchar (2), @dblen)
                                         +  '),'
                                         +  'THRESHOLD=substring (convert (char (12), convert (money, freespace), 1), 1, 9),'
                                         +  'CREATOR=substring (creator, 1,'
                                         +  convert (varchar (2), @ownerlen)
                                         +  '),'
                                         +  '''PROCEDURE (# = LCT)''=substring (procname, 1,'
                                         +  convert (varchar (2), @proclen)
                                         +  ')'
                                         +  ' from #thalist'
                                         +  ' order by sort, freespace desc'

                     execute (@cmdstring)
                  end
               else
                  begin
                     update #thalist
                     set procname = substring (procname, 1, charindex ('#', procname) - 2)
                     where charindex ('#', procname) > 2

                     update #thalist
                     set sort = substring (creator  +  ' '  +  procname  +  ' '  +  dbname, 1, 255)

                     update #thalist
                     set creator  = ' ',
                         procname = ' ',
                         dbname   = ' '
                     from #thalist t1
                     where exists (select *
                                   from #thalist t2
                                   where t2.creator   = t1.creator
                                     and t2.procname  = t1.procname
                                     and t2.dbname    = t1.dbname
                                     and t2.freespace = t1.freespace
                                     and t2.freespace < (select max (freespace)
                                                         from #thalist t3
                                                         where t3.creator  = t2.creator
                                                           and t3.procname = t2.procname
                                                           and t3.dbname   = t2.dbname))

                     update #thalist
                     set creator  = ' ',
                         procname = ' '
                     from #thalist t1
                     where exists (select *
                                   from #thalist t2
                                   where t2.creator  = t1.creator
                                     and t2.procname = t1.procname
                                     and t2.dbname   = t1.dbname
                                     and t2.dbname   > (select min (dbname)
                                                        from #thalist t3
                                                        where t3.creator  = t2.creator
                                                          and t3.procname = t2.procname))

                     update #thalist
                     set creator  = ' '
                     from #thalist t1
                     where exists (select *
                                   from #thalist t2
                                   where t2.creator  = t1.creator
                                     and t2.procname = t1.procname
                                     and t2.procname > (select min (procname)
                                                        from #thalist t3
                                                        where t3.creator = t2.creator))


                     select @cmdstring = +  'select CREATOR=substring(creator,1,'
                                         +  convert (varchar (2), @ownerlen)
                                         +  '),'
                                         +  '''PROCEDURE''=substring(procname,1,'
                                         +  convert (varchar (2), @proclen)
                                         +  '),'
                                         +  '''DATABASE''=substring (dbname,1,'
                                         +  convert (varchar (2), @dblen)
                                         +  '),'
                                         +  'THRESHOLD=substring(convert(char(12),convert(money,freespace),1),1,9)'
                                         +  ' from #thalist'
                                         +  ' order by sort, freespace desc'

                     execute (@cmdstring)
                  end
            end
         else
            begin
               print '    THERE ARE NO THRESHOLDS IN ANY DATABASE.'
            end

         print ' '

         drop table #thalist
      end
   else
      begin
         print '    ONLY AN ADMINISTRATOR CAN EXECUTE sp_thalist.'

         print ' '

         return 1
      end
go
sp_addcomment sp_thalist, 'Lists all ThresHold Actions by database and free space'
go
sp_addcomment sp_thalist, '   if PROCSORT_FLAG is non-null order by owner and procedure name'
go
sp_procxmode sp_thalist, anymode
go

create procedure sp_thaprint (@DBNAME       varchar (30),
                              @SEGMENTNAME  varchar (30),
                              @FREE_SPACE   int,
                              @STATUS       int)  as

   -- IF THIS STORED PROCEDURE IS USER EXECUTED THE PRINT MESSAGES ARE SENT TO
   -- THE CLIENT.  IF SYSTEM INVOKED THE PRINT MESSAGES GO TO THE ERROR LOG.

   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @fsprint  varchar (11)

   print '-----------------------------------------------'

   print 'Sp_thaprint invoked.'

   print '   Database  = %1!,', @DBNAME

   print '   Segment   = %1!,', @SEGMENTNAME

   select @fsprint = ltrim (substring (convert (char (14),
                                                convert (money,
                                                         @FREE_SPACE),
                                                1),
                                       1,
                                       11))

   print '   Threshold = %1! free pages,', @fsprint

   print '   Status    = %1!.', @STATUS

   print '-----------------------------------------------'
go
sp_addcomment sp_thaprint, 'ThresHold Action Stored Procedure (print only)'
go

create procedure sp_thatruncate (@DBNAME       varchar (30),
                                 @SEGMENTNAME  varchar (30),
                                 @FREE_SPACE   int,
                                 @STATUS       int)  as

   -- IF THIS STORED PROCEDURE IS USER EXECUTED THE PRINT MESSAGES ARE SENT TO
   -- THE CLIENT.  IF SYSTEM INVOKED THE PRINT MESSAGES GO TO THE ERROR LOG.

   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @error    int,
           @fsprint  varchar (11),                    -- Free Space (print)
           @lsa      varchar (11),                    -- Log Space After
           @lsb      varchar (11)                     -- Log Space Before

   print '-----------------------------------------------'

   print 'sp_thatruncate invoked.'

   print '   Database  = %1!,', @DBNAME

   print '   Segment   = %1!,', @SEGMENTNAME

   select @fsprint = ltrim (substring (convert (char (14),
                                                convert (money,
                                                         @FREE_SPACE),
                                                1),
                                       1,
                                       11))

   print '   Threshold = %1! free pages,', @fsprint

   print '   Status    = %1!.', @STATUS

   if (exists (select name
               from syssegments
               where name    = @SEGMENTNAME
                 and segment = 2))
      begin
         select @lsb = ltrim (substring (convert (char (14),
                                                  convert (money,
                                                           reserved_pgs (id,
                                                                         doampg)),
                                                  1),
                                         1,
                                         11))
         from sysindexes
         where id = 8

         dump transaction @DBNAME with truncate_only

         select @error = @@error

         if (@error = 0)
            begin
               print 'Dump Transaction with truncate_only succeeded.'

               select @lsa = ltrim (substring (convert (char (14),
                                                        convert (money,
                                                                 reserved_pgs (id,
                                                                               doampg)),
                                                        1),
                                               1,
                                               11))
               from sysindexes
               where id = 8

               print '   Log Pages Used changed from %1! to %2!.', @lsb, @lsa
            end
         else
            begin
               print 'Dump Transaction with truncate_only failed.'

               print '   Error = %1!.', @error
            end
      end

   print '-----------------------------------------------'
go
sp_addcomment sp_thatruncate, 'ThresHold Action Stored Procedure (dump with truncate_only)'
go

------------ Temporary table needed for creation of sp_unused_logins ---------

create table #unusedlogins
   (suid       smallint       not null,
    name       varchar (30)   not null,
    accdate    smalldatetime  not null)
go

create procedure sp_unused_logins  as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   delete #unusedlogins
   from #unusedlogins ul,
        sysusers u
   where ul.suid = u.suid

   delete #unusedlogins
   from #unusedlogins ul,
        sysalternates a
   where ul.suid = a.suid
go
sp_existsprocedure sp_unused_logins
go

drop table #unusedlogins
go


create procedure sp_unused (@OFF_STRING varchar (25) = 'PB,SR,SYS') 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_unused CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @CR           char (1),
           @LF           char (1),
           @LFas         char (4),
           @LFasLF       char (4),
           @as           char (4),
           @asLF         char (4),
           @bitoff       bit,
           @biton        bit,
           @cmdstring    varchar (255),
           @dscrplen     tinyint,
           @now          char (8),
           @objlen       tinyint,
           @pbflag       tinyint,
           @srflag       tinyint,
           @sysflag      tinyint,
           @usertblflag  tinyint,
           @viewflag     tinyint

   create table #systable
     (name        varchar ( 32)  not null,
      type        char (  2)     not null,
      text        varchar (255)  not null)

   create table #unused
     (object      varchar ( 30)  not null,
      type        char ( 13)     not null,
      description varchar ( 45)  not null,
      owner       varchar ( 30)  not null,
      cdate       char (  8)     not null)

   print ' '

   select @CR          = char (13),                      -- Carriage Return
          @LF          = char (10),                      -- Line Feed
          @bitoff      = convert (bit, 0),
          @pbflag      = charindex ('PB', upper (@OFF_STRING)),
          @srflag      = charindex ('SR', upper (@OFF_STRING)),
          @sysflag     = charindex ('SYS', upper (@OFF_STRING)),
          @usertblflag = charindex ('USERTBL', upper (@OFF_STRING)),
          @viewflag    = charindex ('VIEW', upper (@OFF_STRING))

   select @as     = ' AS ',
          @asLF   = ' AS' + @LF,
          @biton  = ~(@bitoff),
          @LFas   = @LF + 'AS ',
          @LFasLF = @LF + 'AS' + @LF

   if (@sysflag = 0)
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    LOADING WORK TABLE FROM sybsystemprocs.        (%1!)', @now

         insert into #systable
            select distinct o.name, 'S', c.text
            from sybsystemprocs.dbo.syscomments c,
                 sysobjects o
            where (o.sysstat & 15) = 1
              and c.text like '%' + o.name + '%'

         delete from #systable
         where text like '%sp[_]' + name + '%'
      end

   if (db_name () = 'master')
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    LOADING WORK TABLE FROM master/sybsystemprocs. (%1!)', @now

         insert into #systable
            select distinct o.name, 'U', c.text
            from master.dbo.sysobjects o,
                 sybsystemprocs.dbo.syscomments c
            where (o.sysstat & 15) = 3
              and o.name like 'spt[_]%'
              and c.text like '%' + o.name + '%'

         delete from #systable
         where text like '%sp[_]' + name + '%'
           and type = 'U'

         select @now = convert (char (8), getdate (), 8)

         print '    CHECKING FOR UNUSED BUFFER POOLS.              (%1!)', @now

         insert into #unused                        -- buffer pools
            select substring (cf.name + ': 4K I/O', 1, 30),
                   'buffer pool',
                   '-',
                   'dbo',
                   '-'
            from master.dbo.sysconfigures cf,
                 master.dbo.syscurconfigs cc
            where cf.comment = '4K I/O Buffer Pool'
              and cf.parent  = 19
              and cc.config  = cf.config
              and cc.comment = cf.name
              and exists (select cf2.*
                          from master.dbo.sysconfigures cf2
                          where cf2.parent   = 19
                            and cf2.name     = cf.name
                            and cf2.comment in ('8K I/O Buffer Pool', '16K I/O Buffer Pool'))

         insert into #unused
            select substring (cf.name + ': 8K I/O', 1, 30),
                   'buffer pool',
                   '-',
                   'dbo',
                   '-'
            from master.dbo.sysconfigures cf,
                 master.dbo.syscurconfigs cc
            where cf.comment = '8K I/O Buffer Pool'
              and cf.parent  = 19
              and cc.config  = cf.config
              and cc.comment = cf.name
              and exists (select cf2.*
                          from master.dbo.sysconfigures cf2
                          where cf2.parent   = 19
                            and cf2.name     = cf.name
                            and cf2.comment  = '16K I/O Buffer Pool')
      end

   create clustered index C on #systable (name)

   select @now = convert (char (8), getdate (), 8)

   print '    CHECKING FOR UNUSED DEFAULTS.                  (%1!)', @now

   insert into #unused                              -- defaults (user-named)
      select o.name,
             'default',
             substring (substring (c.text,
                                   4 + charindex (@as, upper (c.text)),
                                   datalength (c.text) - 3 - charindex (@as, upper (c.text))),
                        1,
                        26),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments c,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)                = 6
        and o.id                            = p.id
        and p.sequence                      = 1
        and (p.status & 4096)              <> 4096
        and o.id                            = c.id
        and c.colid                         = 1
        and c.texttype                      = 0
        and charindex (@as, upper (c.text)) > 0
        and (not exists (select cl.*
                         from syscolumns cl
                         where cl.cdefault = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.tdefault = o.id))

   insert into #unused
      select o.name,
             'default',
             substring (substring (c.text,
                                   4 + charindex (@asLF, upper (c.text)),
                                   datalength (c.text) - 3 - charindex (@asLF, upper (c.text))),
                        1,
                        26),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments c,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)                  = 6
        and o.id                              = p.id
        and p.sequence                        = 1
        and (p.status & 4096)                <> 4096
        and o.id                              = c.id
        and c.colid                           = 1
        and c.texttype                        = 0
        and charindex (@asLF, upper (c.text)) > 0
        and (not exists (select cl.*
                         from syscolumns cl
                         where cl.cdefault = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.tdefault = o.id))

   insert into #unused
      select o.name,
             'default',
             substring (substring (c.text,
                                   4 + charindex (@LFas, upper (c.text)),
                                   datalength (c.text) - 3 - charindex (@LFas, upper (c.text))),
                        1,
                        26),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments c,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)                  = 6
        and o.id                              = p.id
        and p.sequence                        = 1
        and (p.status & 4096)                <> 4096
        and o.id                              = c.id
        and c.colid                           = 1
        and c.texttype                        = 0
        and charindex (@LFas, upper (c.text)) > 0
        and (not exists (select cl.*
                         from syscolumns cl
                         where cl.cdefault = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.tdefault = o.id))

   insert into #unused
      select o.name,
             'default',
             substring (substring (c.text,
                                   4 + charindex (@LFasLF, upper (c.text)),
                                   datalength (c.text) - 3 - charindex (@LFasLF, upper (c.text))),
                        1,
                        26),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments c,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)                    = 6
        and o.id                                = p.id
        and p.sequence                          = 1
        and (p.status & 4096)                  <> 4096
        and o.id                                = c.id
        and c.colid                             = 1
        and c.texttype                          = 0
        and charindex (@LFasLF, upper (c.text)) > 0
        and (not exists (select cl.*
                         from syscolumns cl
                         where cl.cdefault = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.tdefault = o.id))

   insert into #unused                              -- defaults (constant/function)
      select o.name,
             'default',
             substring (c.text, 9, 26),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments c,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)         = 6
        and o.id                     = p.id
        and p.sequence               = 1
        and (p.status & 4096)        = 4096
        and o.id                     = c.id
        and c.colid                  = 1
        and c.texttype               = 0
        and substring (c.text, 1, 8) = 'DEFAULT '
        and (not exists (select cl.*
                         from syscolumns cl
                         where cl.cdefault = o.id))

   select @now = convert (char (8), getdate (), 8)

   print '    CHECKING FOR UNUSED GROUPS.                    (%1!)', @now

   insert into #unused                              -- groups w/o members
      select ug.name,
             'group',
             'no members',
             'dbo',
             '-'
      from sysusers ug
      where ug.uid = ug.gid
        and (not exists (select r.*
                         from sysroles r
                         where r.lrid = ug.uid))
        and (not exists (select uu.*
                         from sysusers uu
                         where ug.uid  = uu.gid
                           and uu.uid  > 0
                           and uu.uid <> uu.gid))

   insert into #unused                              -- groups w/o permissions
      select ug.name,
             'group',
             'no permissions',
             'dbo',
             '-'
      from sysusers ug
      where ug.uid = ug.gid
        and (not exists (select r.*
                         from sysroles r
                         where r.lrid = ug.uid))
        and (not exists (select p.*
                         from sysprotects p
                         where p.uid = ug.uid))

   insert into #unused                              -- groups w/o both
      select object, type, 'no members/permissions', owner, cdate
      from #unused u
      where u.type = 'group'
        and 1 < (select count (u2.object)
                 from #unused u2
                 where u.object = u2.object
                   and u.type   = u2.type)

   delete #unused
   where type         = 'group'
     and description in ('no members', 'no permissions')
     and 1 < (select count (u2.object)
              from #unused u2
              where #unused.object = u2.object
                and #unused.type   = u2.type)

   if ((db_name () = 'master')  and
       ((charindex ('sso_role', show_role ()) > 0)  or
        (charindex ('sa_role', show_role ())  > 0)))
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    CHECKING FOR UNUSED LOGINS.                    (%1!)', @now

         insert into #unused                        -- logins
            select name,
                   'login',
                   'no activity',
                   'dbo',
                   convert (char (8), accdate, 11)
            from master.dbo.syslogins
            where suid   > 2
              and totcpu = 0
              and totio  = 0

         select suid, name, accdate
         into #unusedlogins
         from master.dbo.syslogins
         where suid > 1

         delete #unusedlogins
         from #unusedlogins u,
              master.dbo.sysloginroles lr
         where lr.srid < 32
           and u.suid  = lr.suid

         execute  sp_globalexec  @UTILITY_SP='sp_unused_logins'

         insert into #unused
            select name,
                   'login',
                   'neither user nor system role',
                   'dbo',
                   convert (char (8), accdate, 11)
            from #unusedlogins

         drop table #unusedlogins
      end

   select @now = convert (char (8), getdate (), 8)

   print '    CHECKING FOR UNUSED ROLES.                     (%1!)', @now

   insert into #unused
      select sr.name,
             'role',
             'no users',
             'dbo',
             '-'
      from sysroles r,
           master.dbo.syslogins l,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr
      where sr.srid = r.id                          -- only local roles
        and sr.srid > 31                            -- only user roles
        and lr.srid = sr.srid                       -- login roles with role
        and l.suid  = lr.suid                       -- logins with login role
        and not exists (select u.*                  -- no user in login pool
                        from sysusers u
                        where u.suid = l.suid)

   select @now = convert (char (8), getdate (), 8)

   print '    CHECKING FOR UNUSED RULES.                     (%1!)', @now

   insert into #unused                              -- rules
      select o.name,
             'rule',
             substring (ltrim (substring (cm.text,
                                          4 + charindex (@as, upper (cm.text)),
                                          datalength (cm.text) - 3 - charindex (@as, upper (cm.text)))),
                        1,
                        45),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments cm,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 7
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and o.id               = cm.id
        and cm.colid           = 1
        and cm.texttype        = 0
        and charindex (@as, upper (cm.text)) > 0
        and (not exists (select c.*
                         from syscolumns c
                         where c.domain = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.domain = o.id))

   insert into #unused
      select o.name,
             'rule',
             substring (ltrim (substring (cm.text,
                                          4 + charindex (@asLF, upper (cm.text)),
                                          datalength (cm.text) - 3 - charindex (@asLF, upper (cm.text)))),
                        1,
                        45),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments cm,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 7
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and o.id               = cm.id
        and cm.colid           = 1
        and cm.texttype        = 0
        and charindex (@asLF, upper (cm.text)) > 0
        and (not exists (select c.*
                         from syscolumns c
                         where c.domain = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.domain = o.id))

   insert into #unused
      select o.name,
             'rule',
             substring (ltrim (substring (cm.text,
                                          4 + charindex (@LFas, upper (cm.text)),
                                          datalength (cm.text) - 3 - charindex (@LFas, upper (cm.text)))),
                        1,
                        45),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments cm,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 7
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and o.id               = cm.id
        and cm.colid           = 1
        and cm.texttype        = 0
        and charindex (@LFas, upper (cm.text)) > 0
        and (not exists (select c.*
                         from syscolumns c
                         where c.domain = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.domain = o.id))

   insert into #unused
      select o.name,
             'rule',
             substring (ltrim (substring (cm.text,
                                          4 + charindex (@LFasLF, upper (cm.text)),
                                          datalength (cm.text) - 3 - charindex (@LFasLF, upper (cm.text)))),
                        1,
                        45),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments cm,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 7
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and o.id               = cm.id
        and cm.colid           = 1
        and cm.texttype        = 0
        and charindex (@LFasLF, upper (cm.text)) > 0
        and (not exists (select c.*
                         from syscolumns c
                         where c.domain = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.domain = o.id))

   select @now = convert (char (8), getdate (), 8)

   print '    CHECKING FOR UNUSED SEGMENTS.                  (%1!)', @now

   insert into #unused                              -- exclusive segments
      select s.name,
             'segment',
             'exclusive',
             'dbo',
             '-'
      from syssegments s,
           master.dbo.sysusages u
      where power (2, s.segment) = (u.segmap & power (2, s.segment))
        and u.dbid               = db_id ()
        and 1                    = (select count (s2.segment)
                                    from syssegments s2,
                                         master.dbo.sysusages u2
                                    where u.vstart              = u2.vstart
                                      and power (2, s2.segment) = (u2.segmap & power (2, s2.segment)))
        and (not exists (select i.*
                         from sysindexes i
                         where i.segment = s.segment))

   insert into #unused                              -- shared segments
      select s.name,
             'segment',
             'shared',
             'dbo',
             '-'
      from syssegments s,
           master.dbo.sysusages u
      where power (2, s.segment) = (u.segmap & power (2, s.segment))
        and u.dbid               = db_id ()
        and 1                    < (select count (s2.segment)
                                    from syssegments s2,
                                         master.dbo.sysusages u2
                                    where u.vstart              = u2.vstart
                                      and power (2, s2.segment) = (u2.segmap & power (2, s2.segment)))
        and (not exists (select i.*
                         from sysindexes i
                         where i.segment = s.segment))

   if (@sysflag = 0)
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    CHECKING FOR UNUSED SYSTEM TABLES.             (%1!)', @now

         insert into #unused                              -- system tables
            select o.name,
                   substring (vO.name, 1, 13),
                   '-',
                   isnull (user_name (o.uid), '?'),
                   convert (char (8), o.crdate, 11)
            from sysobjects o,
                 sybsystemprocs.dbo.usptvalues vO
            where (o.sysstat & 15) = 1
              and vO.number        = convert (int, o.sysstat & 15)
              and vO.type          = 'O'
              and (not exists (select s.*
                               from #systable s
                               where s.name           = o.name
                                 and (o.sysstat & 15) = 1))
      end

   if (@usertblflag = 0)
      begin
         select @now = convert (char (8), getdate (), 8)

         if ((@pbflag = 0)  and  (@srflag = 0))
            begin
               print '    CHECKING FOR UNUSED USER TABLES.               (%1!)', @now

               insert into #unused                              -- user tables
                  select o.name,
                         'user table',
                         '-',
                         isnull (user_name (o.uid), '?'),
                         convert (char (8), o.crdate, 11)
                  from sysobjects o
                  where (o.sysstat & 15) = 3
                    and (not exists (select d.*
                                     from sysdepends d
                                     where d.depid = o.id))
                    and (not exists (select s.*
                                     from #systable s
                                     where s.name     = o.name
                                       and s.type     = 'U'
                                       and db_name () = 'master'))
                    and (not exists (select r.*
                                     from sysreferences r
                                     where r.pmrydbid = db_id ()
                                       and r.reftabid = o.id))
            end
         else if (@pbflag = 0)
            begin
               print '    CHECKING FOR UNUSED NON-SUBSCRIPTION TABLES.   (%1!)', @now

               insert into #unused                              -- user tables
                  select o.name,
                         'user table',
                         '-',
                         isnull (user_name (o.uid), '?'),
                         convert (char (8), o.crdate, 11)
                  from sysobjects o
                  where (o.sysstat & 15) = 3
                    and o.name not like 'sr[_]%'
                    and (not exists (select d.*
                                     from sysdepends d
                                     where d.depid = o.id))
                    and (not exists (select s.*
                                     from #systable s
                                     where s.name     = o.name
                                       and s.type     = 'U'
                                       and db_name () = 'master'))
                    and (not exists (select r.*
                                     from sysreferences r
                                     where r.pmrydbid = db_id ()
                                       and r.reftabid = o.id))
            end
         else if (@srflag = 0)
            begin
               print '    CHECKING FOR UNUSED NON-POWERBUILDER TABLES.   (%1!)', @now

               insert into #unused                              -- user tables
                  select o.name,
                         'user table',
                         '-',
                         isnull (user_name (o.uid), '?'),
                         convert (char (8), o.crdate, 11)
                  from sysobjects o
                  where (o.sysstat & 15) = 3
                    and o.name not like 'pb%'
                    and (not exists (select d.*
                                     from sysdepends d
                                     where d.depid = o.id))
                    and (not exists (select s.*
                                     from #systable s
                                     where s.name     = o.name
                                       and s.type     = 'U'
                                       and db_name () = 'master'))
                    and (not exists (select r.*
                                     from sysreferences r
                                     where r.pmrydbid = db_id ()
                                       and r.reftabid = o.id))
            end
         else
            begin
               print '    CHECKING FOR UNUSED NON-PB/SR TABLES.          (%1!)', @now

               insert into #unused                              -- user tables
                  select o.name,
                         'user table',
                         '-',
                         isnull (user_name (o.uid), '?'),
                         convert (char (8), o.crdate, 11)
                  from sysobjects o
                  where (o.sysstat & 15) = 3
                    and o.name not like 'pb%'
                    and o.name not like 'sr[_]%'
                    and (not exists (select d.*
                                     from sysdepends d
                                     where d.depid = o.id))
                    and (not exists (select s.*
                                     from #systable s
                                     where s.name     = o.name
                                       and s.type     = 'U'
                                       and db_name () = 'master'))
                    and (not exists (select r.*
                                     from sysreferences r
                                     where r.pmrydbid = db_id ()
                                       and r.reftabid = o.id))
            end
      end

   select @now = convert (char (8), getdate (), 8)

   print '    CHECKING FOR UNUSED USERTYPES.                 (%1!)', @now

   insert into #unused                              -- usertypes (1)
      select t.name,
             'usertype',
             substring (tb.name, 1, 26),
             isnull (user_name (t.uid), '?'),
             '-'
      from systypes t,
           systypes tb
      where t.usertype   > 100
        and tb.type      = t.type
        and tb.usertype in (5, 6, 7, 11, 12, 15, 16, 17, 19, 20, 21, 22, 23)
        and (not exists (select c.*
                         from syscolumns c
                         where c.usertype = t.usertype))

   insert into #unused                              -- usertypes (2)
      select t.name,
             'usertype',
             substring (tb.name  +  ' ('  +  convert (varchar (3), t.length)  +  ')',
                        1, 26),
             isnull (user_name (t.uid), '?'),
             '-'
      from systypes t,
           systypes tb
      where t.usertype   > 100
        and tb.type      = t.type
        and tb.usertype in (1, 2, 3, 4, 8, 13, 14)
        and (not exists (select c.*
                         from syscolumns c
                         where c.usertype = t.usertype))

   insert into #unused                              -- usertypes (3)
      select t.name,
             'usertype',
             substring (tb.name  +  ' ('  +  convert (varchar (3), t.prec)
                        + ', '  +  convert (varchar (3), t.scale) + ')',
                        1, 26),
             isnull (user_name (t.uid), '?'),
             '-'
      from systypes t,
           systypes tb
      where t.usertype   > 100
        and tb.type      = t.type
        and tb.usertype in (10, 26, 29, 28)
        and (not exists (select c.*
                         from syscolumns c
                         where c.usertype = t.usertype))

   update #unused
   set description = substring (description, 1, 5)
   where description = 'image (16)'
      or description = 'text (16)'

   update #unused
   set description = substring (description + ', identity', 1, 45)
   from systypes t
   where #unused.type    = 'usertype'
     and #unused.object  = t.name
     and t.ident         = 1

   update #unused
   set description = substring (description + ', not null', 1, 45)
   from systypes t
   where #unused.type   = 'usertype'
     and #unused.object = t.name
     and t.allownulls   = @bitoff

   update #unused
   set description = substring (description + ', null', 1, 45)
   from systypes t
   where #unused.type   = 'usertype'
     and #unused.object = t.name
     and t.allownulls   = @biton

   if (@viewflag = 0)
      begin
         select @now = convert (char (8), getdate (), 8)

         if (@srflag = 0)
            begin
               print '    CHECKING FOR UNUSED VIEWS.                     (%1!)', @now

               insert into #unused                              -- views
                  select o.name,
                         'view',
                         '-',
                         isnull (user_name (o.uid), '?'),
                         convert (char (8), o.crdate, 11)
                  from sysobjects o
                  where (o.sysstat & 15) = 2
                    and (not exists (select d.*
                                     from sysdepends d
                                     where d.depid = o.id))
                    and (not exists (select s.*
                                     from #systable s
                                     where s.name     = o.name
                                       and s.type     = 'U'
                                       and db_name () = 'master'))
            end
         else
            begin
               print '    CHECKING FOR UNUSED NON-SUBSCRIPTION VIEWS.    (%1!)', @now

               insert into #unused                              -- views
                  select o.name,
                         'view',
                         '-',
                         isnull (user_name (o.uid), '?'),
                         convert (char (8), o.crdate, 11)
                  from sysobjects o
                  where (o.sysstat & 15) = 2
                    and o.name not like 'sr[_]%'
                    and (not exists (select d.*
                                     from sysdepends d
                                     where d.depid = o.id))
                    and (not exists (select s.*
                                     from #systable s
                                     where s.name     = o.name
                                       and s.type     = 'U'
                                       and db_name () = 'master'))
            end
      end

   print ' '

   if (exists (select *
               from #unused))
      begin
         update #unused
         set description = ltrim (description)

         update #unused
         set object = substring (u.object + ' (' + u.owner + ')', 1, 30)
         from #unused u
         where u.owner <> 'dbo'
            or 1        < (select count (o.uid)
                           from sysobjects o
                           where o.name = u.object)

         update #unused
         set description = substring (description, 2, 45)
         where substring (description, 1, 1) = @LF

         update #unused
         set description = substring (description, 1, charindex (@LF, description) - 1)
         where charindex (@LF, description) > 1

         update #unused
         set description = substring (description, 1, charindex (@CR, description) - 1)
         where charindex (@CR, description) > 1

         select @objlen = max (char_length (object))
         from #unused

         if (@objlen < 6)
            begin
               select @objlen = 6
            end

         select @dscrplen = 53 - @objlen

         select @cmdstring = 'select distinct OBJECT = substring (object, 1,'
                             +  convert (varchar (2), @objlen)
                             +  '),'
                             +  '''OBJECT TYPE'' = type,'
                             +  'DESCRIPTION = substring (description, 1,'
                             +  convert (varchar (2), @dscrplen)
                             +  '),'
                             +  'CREATED = cdate'
                             +  ' from #unused'
                             +  ' order by object, owner'

         execute  (@cmdstring)
      end
   else
      begin
         print '    ALL CHECKED OBJECTS ARE USED.'
      end
go
sp_addcomment sp_unused, 'Checks for unused objects (with multiple options) within'
go
sp_addcomment sp_unused, 'string OFF_STRING:'
go
sp_addcomment sp_unused, '   (NULL):    Maximum checking'
go
sp_addcomment sp_unused, '   "PB":      Power Builder tables not checked (default)'
go
sp_addcomment sp_unused, '   "SR":      Subscription tables/views not checked (default)'
go
sp_addcomment sp_unused, '   "SYS":     System tables not checked (default)'
go
sp_addcomment sp_unused, '   "USERTBL": User tables not checked'
go
sp_addcomment sp_unused, '   "VIEW":    Views not checked'
go
sp_procxmode sp_unused, anymode
go

create procedure sp_xactagg (@DETAIL_FLAG varchar (1) = NULL) as
   print ' '

   if (db_id () = 2)
      begin
         print '    sp_xactagg CANNOT BE RUN FROM tempdb.'
      end
   else if ((@@tranchained = 0)  and
            (@@trancount = 0)    and
            (exists (select *
                     from master.dbo.sysdatabases
                     where dbid             = db_id ()
                       and (status & 1024) <> 1024)))
      begin
         set ansinull                      off              -- Non-ANSI
         set chained                       off              -- Non-ANSI
         set flushmessage                  on
         set nocount                       on
         set string_rtruncation            on
         set transaction isolation level   1

         create table #xactagg1
           (xactid      varbinary (6)    null,
            op          smallint         not null,
            opcount     int              null,
            sort2       varbinary (6)    not null,
            seq         numeric (38, 0)  identity)

         create table #xactagg2
           (xactid      varbinary (6)    null,
            op          smallint         not null,
            opcount     int              null,
            sort2       varbinary (6)    not null,
            seq         numeric (38, 0)  not null)

         if (@DETAIL_FLAG IS NULL)
            begin
               insert into #xactagg2
                  select xactid, op, 0, xactid, 0
                  from syslogs

               insert into #xactagg2
                  select xactid, -2, count (*), xactid, 0
                  from #xactagg2
                  group by xactid

               select 'TRANSACTION COUNT'      = substring (convert (char (21),
                                                                     convert (money,
                                                                              count (*)),
                                                                     1),
                                                            1,
                                                            18),
                      'TRANSACTION OPERATIONS' = substring (convert (char (25),
                                                                     convert (money,
                                                                              opcount),
                                                                     1),
                                                            1,
                                                            22)
               from #xactagg2
               where op = -2
               group by opcount
               order by opcount

               print ' '

               select 'TOTAL TRANSACTIONS' = substring (convert (char (21),
                                                                 convert (money,
                                                                          count (distinct xactid)),
                                                                 1),
                                                        1,
                                                        18),
                      'TOTAL OPERATIONS'   = substring (convert (char (25),
                                                                 convert (money,
                                                                          sum (opcount)),
                                                                 1),
                                                        1,
                                                        22)
               from #xactagg2
               where op = -2

               print ' '

                  select OCCURRENCES = space (18),
                         OPERATION   = space (58)
                  where 1 = 2
               union
                  select substring (convert (char (21),
                                             convert (money,
                                                      count (x.op)),
                                             1),
                                    1,
                                    18),
                         substring (vOP.name, 1, 58)
                  from #xactagg2 x,
                       sybsystemprocs.dbo.usptvalues vOP
                  where x.op       <> -2
                    and vOP.number  = x.op
                    and vOP.type    = 'OP'
                  group by x.op, vOP.name
               union
                  select substring (convert (char (21),
                                              convert (money,
                                                       count (x.op)),
                                              1),
                                     1,
                                     18),
                          'op = ' + convert (varchar (10), x.op)
                  from #xactagg2 x
                  where x.op <> -2
                    and not exists (select vOP.type
                                    from sybsystemprocs.dbo.usptvalues vOP
                                    where vOP.number = x.op
                                      and vOP.type   = 'OP')
                  group by x.op
               order by OCCURRENCES, OPERATION
            end
         else
            begin
               --
               -- SINCE THERE ARE NO INDEXES ON syslogs AND THE HIGHEST xactid
               -- VALUE DOES NOT NECESSARILY INDICATE THE LATEST TRANSACTION
               -- AN IDENTITY COLUMN IS USED TO PRESERVE THE SEQUENCE ORDER.
               --
               insert into #xactagg1
                  select xactid, op, 0, xactid
                  from syslogs

               insert into #xactagg2
                  select xactid, op, count (op), xactid, min (seq)
                  from #xactagg1
                  group by xactid, op

               select xactid, op, opcount, sort2, seq = min (seq)
               into #xactagg3
               from #xactagg2
               group by xactid

               insert into #xactagg3
                  select distinct NULL, -2, NULL, sort2, seq
                  from #xactagg3
                  group by xactid

               delete from #xactagg3
               where xactid IS NULL
                 and op      = -2
                 and sort2   = (select min (xactid)
                                from #xactagg3)

               select 'COUNT'   = isnull (substring (convert (char (10),
                                                              convert (money,
                                                                       x.opcount),
                                                              1),
                                                     1,
                                                     7),
                                          ' '),
                      OPERATION = isnull (substring (vOP.name,
                                                     1,
                                                     58),
                                          '(op = ' + convert (varchar (10), x.op) + ')')
               from #xactagg3 x  left join  sybsystemprocs.dbo.usptvalues vOP
                 on x.op     = vOP.number
                and vOP.type = 'OP'
               order by x.seq, x.sort2, x.op
            end
      end
   else
      begin
         print '    sp_xactagg CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         print '    @@TRANCHAINED = %1!, @@TRANCOUNT = %2!.', @@tranchained, @@trancount
      end

   print ' '
go
sp_addcomment sp_xactagg, 'Lists transaction log aggregates (option: DETAIL_FLAG)'
go
sp_procxmode sp_xactagg, anymode
go


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

use sybsystemprocs
go

create procedure sp_updatestatistics (@PARM       varchar (9) = NULL,
                                      @CONSUMERS  tinyint     = NULL)  as
   set ansinull                      on
   set close on endtran              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_updatestatistics CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring   varchar (99),
           @dbname      varchar (30),
           @errorchar   varchar ( 4),
           @fixtabname  char (30),
           @now         char ( 8),
           @prefix      varchar (27),
           @suffix      varchar (36),
           @tabname     varchar (30),
           @tmp         int,
           @uname       varchar (30)

   print ' '

   declare updatestatistics_cursor cursor for
         select NAME = convert (varchar (30), ' ')
         where 1 = 2
      union
         select convert (char (30), o.name)
         from sysobjects o
         where (upper (@PARM) = 'ALL')
           and (o.sysstat & 15)  = 3
           and o.uid             = user_id ()
      union
         select convert (char (30), o.name)
         from sysobjects o, sysindexes i
         where ((@PARM IS NULL)  or  (upper (@PARM) = 'INDEX'))
           and (o.sysstat & 15) = 3
           and o.uid            = user_id ()
           and i.id             = o.id
           and i.indid          > 0
           and i.indid          < 254
      union
         select convert (char (30), o.name)
         from sysobjects o, syspartitions p
         where upper (@PARM)    = 'PARTITION'
           and (o.sysstat & 15) = 3
           and o.uid            = user_id ()
           and p.id             = o.id
      union
         select convert (char (30), o.name)
         from sysobjects o, sysindexes i
         where upper (@PARM)    = 'SYS'
           and (o.sysstat & 15) = 1
           and i.id             = o.id
           and i.indid          > 0
           and i.indid          < 254
      order by NAME
   for read only

   if (exists (select *
               from master.dbo.sysdatabases
               where dbid            = db_id ()
                 and (status & 1024) = 1024))
      begin
         print '    THE DATABASE IS IN READ-ONLY MODE AND CANNOT BE UPDATED.'

         return 1
      end
   else if ((upper (@PARM) = 'SYS')  and  (user_id () <> 1))
      begin
         print '    ONLY THE DATABASE OWNER CAN EXECUTE UPDATE STATISTICS ON SYSTEM TABLES.'

         return 1
      end
   else if ((@PARM IS NULL)  or  (upper (@PARM) in ('ALL', 'INDEX', 'PARTITION', 'SYS')))
      begin
         select @prefix = case  upper (@PARM)
                             when  'ALL'        then 'update all statistics'
                             when  'INDEX'      then 'update index statistics'
                             when  'PARTITION'  then 'update partition statistics'
                             when  'SYS'        then 'update index statistics'
                             else                    'update statistics'
                          end

         if (@CONSUMERS IS NOT NULL)
            begin
               if (@CONSUMERS = 0)
                  begin
                     print '    THE CONSUMERS VALUE OF 0 WILL BE IGNORED.'

                     print ' '
                  end
               else if (@PARM = 'PARTITION')
                  begin
                     print '    THE CONSUMERS VALUE OF %1! WILL BE IGNORED.',
                           @CONSUMERS

                     print ' '
                  end
               else
                  begin
                     select @suffix = @suffix
                                      +  ' with consumers='
                                      +  convert (varchar (3), @CONSUMERS)
                  end
            end

         print '    COMMANDS OF THE FORM %1! <table>%2! WILL BE EXECUTED.',
               @prefix, @suffix

         print ' '

         open updatestatistics_cursor

         fetch updatestatistics_cursor into @tabname

         if (@@sqlstatus = 2)
            begin
               select @uname = user_name ()

               print '    THERE ARE NO APPROPRIATE TABLES OWNED BY %1!.', @uname
            end
         else
            begin
               while (@@sqlstatus = 0)
                  begin
                     if (valid_name (@tabname) = 0)
                        begin
                           select @tabname = '"'  +  @tabname  +  '"'
                        end

                     select @fixtabname = convert (char (30), @tabname),
                            @now        = convert (char (8), getdate (), 8)

                     print '    UPDATING STATISTICS ON %1! (%2!)', @fixtabname, @now

                     select @cmdstring = @prefix  +  ' '  +  @tabname  +  @suffix

                     execute  (@cmdstring)

                     -- TRANSACTION EQUIVALENT TO sp_recompile WITHOUT MESSAGE.
                     -- THE TRANSACTION NAME rs_logexec IS IMPORTANT.

                     begin transaction rs_logexec

                        update sysobjects
                        set schemacnt = schemacnt + 1
                        where id = object_id (@tabname)

                        select @tmp = schema_inc (object_id (@tabname))

                        if (logexec () <> 1)
                           begin
                              select @dbname = db_name ()

                              raiserror 17756, 'sp_recompile', @dbname

                              rollback transaction rs_logexec

                              return (1)
                           end

                     commit transaction

                     fetch updatestatistics_cursor into @tabname
                  end

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

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

                     return 1
                  end
            end

         close updatestatistics_cursor

         deallocate cursor updatestatistics_cursor
      end
   else
      begin
         print '    THE PARM VALUE %1! IS INVALID.', @PARM

         print ' '

         PRINT '    VALID ARGUMENTS ARE NULL (DEFAULT), "ALL", "INDEX", "PARTITION", AND "SYS".'
      end

   print ' '
go
sp_addcomment sp_updatestatistics, 'Executes update statistics on appropriate tables with options:'
go
sp_addcomment sp_updatestatistics, '   (NULL):      update statistics (default)'
go
sp_addcomment sp_updatestatistics, '   "ALL":       update all statistics'
go
sp_addcomment sp_updatestatistics, '   "INDEX":     update index statistics'
go
sp_addcomment sp_updatestatistics, '   "PARTITION": update partition statistics'
go
sp_addcomment sp_updatestatistics, '   "SYS":       update index statistics (system tables only)'
go
sp_addcomment sp_updatestatistics, 'Additional optional argument: CONSUMERS'
go
sp_procxmode sp_updatestatistics, anymode
go

dump transaction sybsystemprocs with truncate_only
go

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