use master
go
drop procedure updt_master_stats
go

create procedure updt_master_stats as
   set flushmessage                  on

   print '    UPDATING STATISTICS ON SYSTEM TABLES IN MASTER DATABASE.'

   print ' '

   update statistics syscharsets

   update statistics sysconfigures

   update statistics syscurconfigs

   update statistics sysdatabases

   update statistics sysdevices

   update statistics syslanguages

   update statistics sysloginroles

   update statistics syslogins

   update statistics sysmessages

   update statistics sysremotelogins

   update statistics sysresourcelimits

   update statistics syssecmechs

   update statistics sysservers

   update statistics syssrvroles

   update statistics systimeranges

   update statistics sysusages
go

use sybsystemprocs
go

drop procedure sp_cstattributes
go
drop procedure sp_cstcolumns
go
drop procedure sp_cstconstraints
go
drop procedure sp_cstindexes
go
drop procedure sp_cstobjects
go
drop procedure sp_cstreferences
go
drop procedure sp_csttypes
go
drop procedure sp_checksystables
go

------------ Temporary tables needed for sp_checksystables subroutines -------

create table #cst
  (fkey  varchar (60)   not null,
   pkey  varchar (60)   not null,
   txt   varchar (255)  not null)

create table #cstwork
  (fkey  varchar (30)  not null,
   pkey  varchar (30)  not null,
   txt1  varchar (30)  not null,
   id1   varchar (30)  null,
   db1   smallint      null,
   txt2  varchar (30)  null,
   id2   varchar (30)  null,
   db2   smallint      null,
   txt3  varchar (30)  null,
   id3   varchar (30)  null,
   db3   smallint      null,
   type  tinyint       not null)
go

set quoted_identifier on
go

create procedure sp_cstattributes as
   set ansinull                      on
   set string_rtruncation            on

   if (db_id () = 1)
      begin
         --
         --  sp_bindexecutelass login, lg, -, -
         --
         insert into #cst
            select 'attributes.object',
                   'attributes.object_cinfo',
                   'LG, suid='  +   convert (varchar (12), object)
            from master.dbo.sysattributes
            where class        = 6
              and attribute    = 0
              and object_type  = 'LG'
              and object      <> suser_id ('"' + object_cinfo + '"')

         --
         --  grant <role> to <parent role>
         --
         insert into #cst
            select 'attributes.object',
                   'srvroles.srid',
                   'UR, parent role id='  +   convert (varchar (12), a.object)
            from master.dbo.sysattributes a
            where a.class       = 8
              and a.attribute   = 2
              and a.object_type = 'UR'
              and not exists (select r.srid
                              from master.dbo.syssrvroles r
                              where r.srid = a.object)

         insert into #cst
            select 'attributes.object_info1',
                   'srvroles.srid',
                   'UR, child role id='  +   convert (varchar (12), a.object_info1)
            from master.dbo.sysattributes a
            where a.class       = 8
              and a.attribute   = 2
              and a.object_type = 'UR'
              and not exists (select r.srid
                              from master.dbo.syssrvroles r
                              where r.srid = a.object_info1)
      end

   --
   --  sp_bindcache cachename, table
   --
   insert into #cst
      select 'attributes.object',
             'objects.id',
             'T, id='  +  convert (varchar (12), object)
      from sysattributes
      where class                 = 3
        and attribute             = 0
        and object_type           = 'T'
        and object_name (object) IS NULL

   --
   --  sp_bindcache cachename, table, index
   --
   insert into #cst
      select 'attributes.object/object_info1',
             'indexes.id/indid',
             'I, id/indid='  +  convert (varchar (12), a.object)  +  '/'
             +  convert (varchar (12), a.object_info1)
      from sysattributes a
      where a.class       = 3
        and a.attribute   = 0
        and a.object_type = 'I'
        and not exists (select i.id
                        from sysindexes i
                        where i.id    = a.object
                          and i.indid = a.object_info1)

   --
   --  sp_bindexecutelass procedure, pr, user, -
   --
   insert into #cst
      select 'attributes.object',
             'attributes.object_info1/cinfo',
             'PR, proc id='  +   convert (varchar (12), object)
      from sysattributes
      where class        = 6
        and attribute    = 0
        and object_type  = 'PR'
        and object      <> object_id ('"'
                                      + user_name (object_info1)
                                      + '.'
                                      + object_cinfo
                                      + '"')

   insert into #cst
      select 'attributes.object',
             'procedures.id',
             'PR, proc id='  +   convert (varchar (12), object)
      from sysattributes a
      where a.class       = 6
        and a.attribute   = 0
        and a.object_type = 'PR'
        and not exists (select p.id
                        from sysprocedures p
                        where p.id       = a.object
                          and p.sequence = 1)
go
sp_procxmode sp_cstattributes, unchained
go

create procedure sp_cstcolumns as
   set ansinull                      on
   set string_rtruncation            on

   insert into #cst
      select 'columns.id',
             'objects.id',
             'id='  +  convert (varchar (12), id)
      from syscolumns
      where object_name (id) IS NULL

   insert into #cst
      select 'columns.cdefault',
             'procedures.id',
             o.name
             +  '.'
             +  c.name
             +  ', dflt='  +  isnull (object_name (c.cdefault), convert (varchar (12), c.cdefault))
      from syscolumns c,
           sysobjects o
      where c.id        = o.id
        and o.uid       = 1
        and c.cdefault <> 0
        and not exists (select p.*
                        from sysprocedures p
                        where p.id = c.cdefault)

   insert into #cst
      select 'columns.cdefault',
             'procedures.id',
             user_name (o.uid)
             +  '.'
             +  o.name
             +  '.'
             +  c.name
             +  ', dflt='  +  isnull (object_name (c.cdefault), convert (varchar (12), c.cdefault))
      from syscolumns c,
           sysobjects o
      where c.id        = o.id
        and o.uid      <> 1
        and c.cdefault <> 0
        and not exists (select p.*
                        from sysprocedures p
                        where p.id = c.cdefault)

   insert into #cst
      select 'columns.domain',
             'procedures.id',
             o.name
             +  '.'
             +  c.name
             +  'dmn='  +  isnull (object_name (c.domain), convert (varchar (12), c.domain))
      from syscolumns c,
           sysobjects o
      where c.id        = o.id
        and o.uid       = 1
        and c.domain <> 0
        and not exists (select p.*
                        from sysprocedures p
                        where p.id = c.domain)

   insert into #cst
      select 'columns.domain',
             'procedures.id',
             user_name (o.uid)
             +  '.'
             +  o.name
             +  '.'
             +  c.name
             +  'dmn='  +  isnull (object_name (c.domain), convert (varchar (12), c.domain))
      from syscolumns c,
           sysobjects o
      where c.id      = o.id
        and o.uid    <> 1
        and c.domain <> 0
        and not exists (select p.*
                        from sysprocedures p
                        where p.id = c.domain)

   insert into #cst
      select 'columns.type',
             'types.type',
             'o='
             +  object_name (c.id)
             +  ', type='
             +  convert (varchar (12), c.type)
      from syscolumns c
      where not exists (select t.*
                        from systypes t
                        where t.type = c.type)

   insert into #cst
      select 'columns.usertype',
             'types.usertype',
             'o='
             +  object_name (c.id)
             +  ', usertype='
             +  convert (varchar (12), c.usertype)
      from syscolumns c
      where not exists (select t.*
                        from systypes t
                        where c.usertype = t.usertype)

   select id,
          uid = convert (smallint, 0),
          mx  = max (colid),
          ct  = count (*)
   into #cstcolid
   from syscolumns
   group by id

   delete #cstcolid
   from #cstcolid c1
   where exists (select c2.id
                 from #cstcolid c2
                 where c2.id = c1.id
                   and c2.ct = c2.mx)

   update #cstcolid
   set uid = o.uid
   from #cstcolid c,
        sysobjects o
   where c.id = o.id

   insert into #cst
      select 'columns.colid',
             object_name (cid.id),
             'missing colid=' + convert (varchar (3), vP.number)
      from #cstcolid cid,
           sybsystemprocs.dbo.usptvalues vP
      where vP.type    = 'P'
        and vP.number >= 1
        and vP.number <= cid.mx
        and cid.uid    = 1
        and 1          = (select count (o.uid)
                          from sysobjects o
                          where o.name            = object_name (cid.id)
                            and (o.sysstat & 15) in (1, 2, 3, 4))
        and not exists (select c.id
                        from syscolumns c
                        where c.id    = cid.id
                          and c.colid = vP.number)

   insert into #cst
      select 'columns.colid',
             object_name (cid.id) + ' (' + user_name (cid.uid) + ')',
             'missing colid=' + convert (varchar (3), vP.number)
      from #cstcolid cid,
           sybsystemprocs.dbo.usptvalues vP
      where vP.type    = 'P'
        and vP.number >= 1
        and vP.number <= cid.mx
        and ((cid.uid <> 1)  or
             (1        < (select count (o.uid)
                          from sysobjects o
                          where o.name            = object_name (cid.id)
                            and (o.sysstat & 15) in (1, 2, 3, 4))))
        and not exists (select c.id
                        from syscolumns c
                        where c.id    = cid.id
                          and c.colid = vP.number)
go
sp_procxmode sp_cstcolumns, unchained
go

create procedure sp_cstconstraints as
   set ansinull                      on
   set string_rtruncation            on

   insert into #cstwork
      select 'constraints.constrid',
             'comments.id',
             'c=',
             convert (varchar (12), c.constrid),
             NULL,
             ', t=',
             convert (varchar (12), c.tableid),
             NULL,
             NULL,
             NULL,
             NULL,
             1
      from sysconstraints c
      where (c.status & 128) = 128
        and not exists (select t.*
                        from syscomments t
                        where t.id = c.constrid)

   insert into #cstwork
      select 'constraints.constrid',
             'objects.id',
             'c=',
             convert (varchar (12), constrid),
             NULL,
             ', t=',
             convert (varchar (12), tableid),
             NULL,
             NULL,
             NULL,
             NULL,
             1
      from sysconstraints
      where object_name (constrid) IS NULL

   insert into #cstwork
      select 'constraints.constrid',
             'references.constrid',
             'c=',
             convert (varchar (12), c.constrid),
             NULL,
             ', t=',
             convert (varchar (12), c.tableid),
             NULL,
             NULL,
             NULL,
             NULL,
             1
      from sysconstraints c
      where (c.status & 64) = 64
        and not exists (select r.*
                        from sysreferences r
                        where r.constrid = c.constrid)

   insert into #cst
      select 'constraints.error',
             'usermessages.error',
             'c='
             +  convert (varchar (12), c.constrid)
             +  ', e='
             +  convert (varchar (12), c.error)
      from sysconstraints c
      where error <> 0
        and not exists (select m.*
                        from sysusermessages m
                        where m.error = c.error)

   insert into #cstwork
      select 'constraints.tableid',
             'objects.id',
             'c=',
             convert (varchar (12), constrid),
             NULL,
             ', t=',
             convert (varchar (12), tableid),
             NULL,
             NULL,
             NULL,
             NULL,
             1
      from sysconstraints
      where object_name (tableid) IS NULL

   insert into #cst
      select 'constraints.tableid/colid',
             'columns.id/colid',
             't='
             +  convert (varchar (12), c.tableid)
             +  ', c='
             +  isnull (col_name (c.tableid, c.colid), convert (varchar (12), c.colid))
      from sysconstraints c
      where c.colid <> 0
        and not exists (select l.*
                        from syscolumns l
                        where l.id    = c.tableid
                          and l.colid = c.colid)
go
sp_procxmode sp_cstconstraints, unchained
go

create procedure sp_cstindexes as
   set ansinull                      on
   set string_rtruncation            on

   insert into #cst
      select 'indexes.id',
             'objects.id',
             't='  +  convert (varchar (12), id)
      from sysindexes
      where object_name (id) IS NULL

   insert into #cst
      select 'indexes.segment',
             'segments.segment',
             'segment='  +  convert (varchar (12), i.segment)
      from sysindexes i
      where not exists (select s.*
                        from syssegments s
                        where s.segment = i.segment)

   insert into #cst
      select 'indexes.status (2048)',
             'objects.sysstat2 (~8)',
             o.name
             +  ', indid='
             +  convert (varchar (12), i.indid)
             +  ', status='
             +  convert (varchar (12), o.sysstat2)
      from sysindexes i,
           sysobjects o
      where (i.status & 2048)  = 2048
        and i.id               = o.id
        and o.uid              = 1
        and (o.sysstat2 & 8)  <> 8

   insert into #cst
      select 'indexes.status (2048)',
             'objects.sysstat2 (~8)',
             user_name (o.uid)
             +  '.'
             +  o.name
             +  ', indid='
             +  convert (varchar (12), i.indid)
             +  ', status='
             +  convert (varchar (12), o.sysstat2)
      from sysindexes i,
           sysobjects o
      where (i.status & 2048)  = 2048
        and i.id               = o.id
        and o.uid             <> 1
        and (o.sysstat2 & 8)  <> 8

   insert into #cst
      select 'indexes.status2 (2)',
             'indexes.status (~2)',
             't='
             +  convert (varchar (12), id)
             +  ', indid='
             +  convert (varchar (12), indid)
             +  ', status2='
             +  convert (varchar (12), status2)
             +  ', status='
             +  convert (varchar (12), status)
      from sysindexes
      where (status2 & 2)     = 2
        and object_name (id) IS NULL
        and (((status & 2) <> 2)  or  (indid = 0))

   insert into #cst
      select 'indexes.status2 (2)',
             'indexes.status (~2)',
             o.name
             +  ', indid='
             +  convert (varchar (12), i.indid)
             +  ', status2='
             +  convert (varchar (12), i.status2)
             +  ', status='
             +  convert (varchar (12), i.status)
      from sysindexes i,
           sysobjects o
      where (i.status2 & 2)  = 2
        and i.id             = o.id
        and o.uid            = 1
        and (((i.status & 2) <> 2)  or  (i.indid = 0))

   insert into #cst
      select 'indexes.status2 (2)',
             'indexes.status (~2)',
             user_name (o.uid)
             +  '.'
             +  o.name
             +  ', indid='
             +  convert (varchar (12), i.indid)
             +  ', status2='
             +  convert (varchar (12), i.status2)
             +  ', status='
             +  convert (varchar (12), i.status)
      from sysindexes i,
           sysobjects o
      where (i.status2 & 2)  = 2
        and i.id             = o.id
        and o.uid           <> 1
        and (((i.status & 2) <> 2)  or  (i.indid = 0))
go
sp_procxmode sp_cstindexes, unchained
go

create procedure sp_cstobjects as
   set ansinull                      on
   set string_rtruncation            on

   insert into #cst
      select 'objects.sysstat2 (1)',
             'references.reftabid',
             't='  +  o.name
      from sysobjects o
      where o.uid            = 1
        and (o.sysstat2 & 1) = 1
        and not exists (select r.*
                        from sysreferences r
                        where r.reftabid = o.id
                          and r.pmrydbid = db_id ())

   insert into #cst
      select 'objects.sysstat2 (1)',
             'references.reftabid',
             't='  +  user_name (o.uid)  +  '.'  +  o.name
      from sysobjects o
      where o.uid            <> 1
        and (o.sysstat2 & 1)  = 1
        and not exists (select r.*
                        from sysreferences r
                        where r.reftabid = o.id
                          and r.pmrydbid = db_id ())

   insert into #cst
      select 'objects.sysstat2 (2)',
             'references.tableid',
             't='  +  o.name
      from sysobjects o
      where o.uid            = 1
        and (o.sysstat2 & 2) = 2
        and not exists (select r.*
                        from sysreferences r
                        where r.tableid = o.id)

   insert into #cst
      select 'objects.sysstat2 (2)',
             'references.tableid',
             't='  +  user_name (o.uid)  +  '.'  +  o.name
      from sysobjects o
      where o.uid            <> 1
        and (o.sysstat2 & 2)  = 2
        and not exists (select r.*
                        from sysreferences r
                        where r.tableid = o.id)

   insert into #cst
      select 'objects.sysstat2 (4)',
             'constraints.tableid',
             't='  +  o.name
      from sysobjects o
      where o.uid            = 1
        and (o.sysstat2 & 4) = 4
        and 2                > (select count (c.tableid)
                                from sysconstraints c
                                where c.tableid = o.id
                                  and (c.status & 128) = 128)

   insert into #cst
      select 'objects.sysstat2 (4)',
             'constraints.tableid',
             't='  +  user_name (o.uid)  +  '.'  +  o.name
      from sysobjects o
      where o.uid            <> 1
        and (o.sysstat2 & 4)  = 4
        and 2                 > (select count (c.tableid)
                                 from sysconstraints c
                                 where c.tableid = o.id
                                   and (c.status & 128) = 128)

   insert into #cst
      select 'objects.sysstat2 (8)',
             'indexes.status2 (2)',
             't='  +  o.name
      from sysobjects o
      where o.uid            = 1
        and (o.sysstat2 & 8) = 8
        and not exists (select i.*
                        from sysindexes i
                        where i.id            = o.id
                          and (i.status2 & 2) = 2)

   insert into #cst
      select 'objects.sysstat2 (8)',
             'indexes.status2 (2)',
             't='  +  user_name (o.uid)  +  '.'  +  o.name
      from sysobjects o
      where o.uid            <> 1
        and (o.sysstat2 & 8)  = 8
        and not exists (select i.*
                        from sysindexes i
                        where i.id            = o.id
                          and (i.status2 & 2) = 2)

   insert into #cst
      select 'objects.sysstat2 (8)',
             'indexes.status (2048)',
             't='  +  o.name
      from sysobjects o
      where o.uid            = 1
        and (o.sysstat2 & 8) = 8
        and not exists (select i.*
                        from sysindexes i
                        where i.id              = o.id
                          and (i.status & 2048) = 2048)

   insert into #cst
      select 'objects.sysstat2 (8)',
             'indexes.status (2048)',
             't='  +  user_name (o.uid)  +  '.'  +  o.name
      from sysobjects o
      where o.uid            <> 1
        and (o.sysstat2 & 8)  = 8
        and not exists (select i.*
                        from sysindexes i
                        where i.id            = o.id
                          and (i.status & 2048) = 2048)

   insert into #cst
      select 'objects.sysstat2 (~512)',
             'no variable length column',
             'o='  +  o.name
      from sysobjects o
      where (o.sysstat  & 15)  in (1, 3)
        and (o.sysstat2 & 512) <> 512
        and not exists (select c.*
                        from syscolumns c
                        where c.id     = o.id
                          and c.offset < 0)

   insert into #cst
      select 'objects.sysstat2 (512)',
             'variable length column',
             't='
             +  o.name
             +  ', c='
             +  c.name
      from syscolumns c,
           sysobjects o
      where (o.sysstat2 & 512) = 512
        and exists (select c2.*
                    from syscolumns c2
                    where c2.id     = c.id
                      and c2.colid  = c.colid
                      and c2.id     = o.id
                      and c2.offset < 0)
go
sp_procxmode sp_cstobjects, unchained
go

create procedure sp_cstreferences as
   set ansinull                      on
   set string_rtruncation            on

   insert into #cstwork
      select 'references.constrid',
             'constraints.constrid',
             'c=',
             convert (varchar (12), r.constrid),
             r.frgndbid,
             't=',
             convert (varchar (12), r.tableid),
             r.frgndbid,
             ', r=',
             convert (varchar (12), r.reftabid),
             r.pmrydbid,
             2
      from sysreferences r
      where object_name (r.tableid, r.frgndbid) IS NULL
        and not exists (select c.*
                        from sysconstraints c
                        where c.constrid      = r.constrid
                          and (c.status & 64) = 64)

   insert into #cst
      select 'references.frgndbid',
             'references.pmrydbid',
             'frgndbid='
             +  convert (varchar (12), frgndbid)
             +  '/'
             +  db_name (r.frgndbid)
             +  ', pmrydbid='
             +  convert (varchar (12), pmrydbid)
             +  '/'
             +  db_name (r.pmrydbid)
      from sysreferences r
      where frgndbid <> db_id ()
        and pmrydbid <> db_id ()

   insert into #cst
      select 'references.frgndbid',
             'references.frgndbname',
             'frgndbid='
             +  isnull (db_name (r.frgndbid), convert (varchar (20), r.frgndbid))
             +  ', frgndbname='
             +  frgndbname
      from sysreferences r
      where db_name (frgndbid) <> frgndbname

   insert into #cst
      select 'references.pmrydbid',
             'references.pmrydbname',
             'pmrydbid='
             +  isnull (db_name (r.pmrydbid), convert (varchar (20), r.pmrydbid))
             +  ', pmrydbname='
             +  pmrydbname
      from sysreferences r
      where db_name (pmrydbid) <> pmrydbname

   insert into #cst
      select 'references.indexid',
             'not an index',
             'indexid='
             +  convert (varchar (3), indexid)
      from sysreferences
      where indexid in (0, 254, 255)

   insert into #cstwork
      select 'references.reftabid',
             'objects.id',
             'c=',
             convert (varchar (12), constrid),
             frgndbid,
             ', r=',
             convert (varchar (12), reftabid),
             pmrydbid,
             ', t=',
             convert (varchar (12), tableid),
             frgndbid,
             2
      from sysreferences
      where object_name (reftabid, pmrydbid) IS NULL

   insert into #cstwork
      select 'references.indexid',
             'indexes.indid ('  +  convert (varchar (3), r.indexid)  +  ')',
             'c=',
             convert (varchar (12), r.constrid),
             r.frgndbid,
             ', r=',
             convert (varchar (12), r.reftabid),
             r.pmrydbid,
             ', t=',
             convert (varchar (12), r.tableid),
             r.frgndbid,
             2
      from sysreferences r
      where r.pmrydbid = db_id ()
        and not exists (select i.*
                        from sysindexes i
                        where i.id            = r.reftabid
                          and i.indid         = r.indexid
                          and (i.status2 & 1) = 1)

   insert into #cst
      select 'references.refkey1',
             'columns.id/colid',
             'c='
             +  isnull (object_name (constrid, pmrydbid),
                        isnull (object_name (constrid, frgndbid),
                                convert (varchar (12), constrid)))
             +  ', r='
             +  isnull (object_name (r.reftabid, r.pmrydbid), convert (varchar (12), r.reftabid))
      from sysreferences r
      where col_name (r.reftabid, 1, r.pmrydbid) IS NULL

   insert into #cst
      select 'references.refkey2',
             'columns.id/colid',
             'c='
             +  isnull (object_name (constrid, pmrydbid),
                        isnull (object_name (constrid, frgndbid),
                                convert (varchar (12), constrid)))
             +  ', r='
             +  isnull (object_name (r.reftabid, r.pmrydbid), convert (varchar (12), r.reftabid))
      from sysreferences r
      where r.keycnt                              > 1
        and col_name (r.reftabid, 2, r.pmrydbid) IS NULL

   insert into #cst
      select 'references.refkey3',
             'columns.id/colid',
             'c='
             +  isnull (object_name (constrid, pmrydbid),
                        isnull (object_name (constrid, frgndbid),
                                convert (varchar (12), constrid)))
             +  ', r='
             +  isnull (object_name (r.reftabid, r.pmrydbid), convert (varchar (12), r.reftabid))
      from sysreferences r
      where r.keycnt                              > 2
        and col_name (r.reftabid, 3, r.pmrydbid) IS NULL

   insert into #cst
      select 'references.refkey4',
             'columns.id/colid',
             'c='
             +  isnull (object_name (constrid, pmrydbid),
                        isnull (object_name (constrid, frgndbid),
                                convert (varchar (12), constrid)))
             +  ', r='
             +  isnull (object_name (r.reftabid, r.pmrydbid), convert (varchar (12), r.reftabid))
      from sysreferences r
      where r.keycnt                              > 3
        and col_name (r.reftabid, 4, r.pmrydbid) IS NULL

   insert into #cstwork
      select 'references.tableid',
             'objects.id',
             'c=',
             convert (varchar (12), constrid),
             frgndbid,
             ', t=',
             convert (varchar (12), tableid),
             frgndbid,
             ', r=',
             convert (varchar (12), reftabid),
             pmrydbid,
             2
      from sysreferences
      where object_name (tableid, frgndbid) IS NULL

   insert into #cst
      select 'references.fokey1',
             'columns.id/colid',
             'c='
             +  isnull (object_name (constrid), convert (varchar (12), constrid))
             +  ', t='
             +  isnull (object_name (r.tableid, r.frgndbid), convert (varchar (12), r.tableid))
      from sysreferences r
      where col_name (r.tableid, 1, r.frgndbid) IS NULL

   insert into #cst
      select 'references.fokey2',
             'columns.id/colid',
             'c='
             +  isnull (object_name (constrid), convert (varchar (12), constrid))
             +  ', t='
             +  isnull (object_name (r.tableid, r.frgndbid), convert (varchar (12), r.tableid))
      from sysreferences r
      where r.keycnt                             > 1
        and col_name (r.tableid, 2, r.frgndbid) IS NULL

   insert into #cst
      select 'references.fokey3',
             'columns.id/colid',
             'c='
             +  isnull (object_name (constrid), convert (varchar (12), constrid))
             +  ', t='
             +  isnull (object_name (r.tableid, r.frgndbid), convert (varchar (12), r.tableid))
      from sysreferences r
      where r.keycnt                             > 2
        and col_name (r.tableid, 3, r.frgndbid) IS NULL

   insert into #cst
      select 'references.fokey4',
             'columns.id/colid',
             'c='
             +  isnull (object_name (constrid), convert (varchar (12), constrid))
             +  ', t='
             +  isnull (object_name (r.tableid, r.frgndbid), convert (varchar (12), r.tableid))
      from sysreferences r
      where r.keycnt                             > 3
        and col_name (r.tableid, 4, r.frgndbid) IS NULL
go
sp_procxmode sp_cstreferences, unchained
go

create procedure sp_csttypes as
   set ansinull                      on
   set string_rtruncation            on

   insert into #cst
      select 'types.default',
             'procedures.id',
             'name='
             +  t.name
             +  ', default='
             +  isnull (object_name (t.tdefault), convert (varchar (12), t.tdefault))
      from systypes t
      where t.tdefault <> 0
        and not exists (select p.*
                        from sysprocedures p
                        where p.id = t.tdefault)

   insert into #cst
      select 'types.domain',
             'procedures.id',
             'name='
             +  t.name
             +  ', domain='
             +  isnull (object_name (t.domain), convert (varchar (12), t.domain))
      from systypes t
      where t.domain <> 0
        and not exists (select p.*
                        from sysprocedures p
                        where p.id = t.domain)

   insert into #cst
      select 'types.uid',
             'users.uid',
             'uid='  +  convert (varchar (12), uid)
      from systypes
      where user_name (uid) IS NULL

   insert into #cst
      select 'types.usertype',
             'non-unique usertype',
             t.name
             +  ', t='
             +  convert (varchar (6), t.type)
             +  ', l='
             +  convert (varchar (6), t.length)
             +  ', v='
             +  convert (varchar (6), t.variable)
             +  ', n='
             +  convert (varchar (6), t.allownulls)
             +  ', d='
             +  convert (varchar (10), t.tdefault)
             +  ', r='
             +  convert (varchar (10), t.domain)
      from systypes t
      where t.usertype > 99
        and 1 < (select count (t2.name)
                 from systypes t2
                 where t2.usertype   > 99
                   and t2.prec       = t.prec
                   and t2.type       = t.type
                   and t2.variable   = t.variable
                   and t2.length     = t.length
                   and t2.allownulls = t.allownulls
                   and t2.tdefault   = t.tdefault
                   and t2.domain     = t.domain)
      group by t.type, t.prec, t.variable, t.length, t.allownulls, t.tdefault, t.domain
      having t.usertype > 99
         and 1 < (select count (t2.name)
                  from systypes t2
                  where t2.usertype   > 99
                    and t2.prec       = t.prec
                    and t2.type       = t.type
                    and t2.variable   = t.variable
                    and t2.length     = t.length
                    and t2.allownulls = t.allownulls
                    and t2.tdefault   = t.tdefault
                    and t2.domain     = t.domain)

   insert into #cst
      select 'types.usertype',
             'non-unique usertype',
             t.name
             +  ', t='
             +  convert (varchar (6), t.type)
             +  ', l='
             +  convert (varchar (6), t.length)
             +  ', v='
             +  convert (varchar (6), t.variable)
             +  ', n='
             +  convert (varchar (6), t.allownulls)
             +  ', d='
             +  convert (varchar (10), t.tdefault)
             +  ', r='
             +  convert (varchar (10), t.domain)
      from systypes t
      where t.usertype > 99
        and 1 < (select count (t2.name)
                 from systypes t2
                 where t.prec       IS NULL
                   and t2.prec      IS NULL
                   and t2.usertype   > 99
                   and t2.type       = t.type
                   and t2.variable   = t.variable
                   and t2.length     = t.length
                   and t2.allownulls = t.allownulls
                   and t2.tdefault   = t.tdefault
                   and t2.domain     = t.domain)
      group by t.type, t.variable, t.length, t.allownulls, t.tdefault, t.domain
      having t.usertype > 99
         and 1 < (select count (t2.name)
                  from systypes t2
                  where t.prec       IS NULL
                    and t2.prec      IS NULL
                    and t2.usertype   > 99
                    and t2.type       = t.type
                    and t2.variable   = t.variable
                    and t2.length     = t.length
                    and t2.allownulls = t.allownulls
                    and t2.tdefault   = t.tdefault
                    and t2.domain     = t.domain)
go
sp_procxmode sp_csttypes, unchained
go


drop table #cst
go
drop table #cstwork
go

create procedure sp_checksystables (@OFF_STRING  varchar (30) = 'DP') 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_checksystables> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @dpflag      tinyint,                    -- sysdepends check?
           @format_lks  varchar (11),
           @format_pgs  varchar (11),
           @lgflag      tinyint,                    -- syslogins check?
           @maxftxt     tinyint,
           @maxptxt     tinyint,
           @now         char (8),
           @procpgs     int,
           @syslocks    int,
           @umflag      tinyint                     -- sysusermessages check?

   create table #cst
     (fkey  varchar (60)   not null,
      pkey  varchar (60)   not null,
      txt   varchar (255)  not null)

   create table #cstwork
     (fkey  varchar (30)  not null,
      pkey  varchar (30)  not null,
      txt1  varchar (30)  not null,
      id1   varchar (30)  null,
      db1   smallint      null,
      txt2  varchar (30)  null,
      id2   varchar (30)  null,
      db2   smallint      null,
      txt3  varchar (30)  null,
      id3   varchar (30)  null,
      db3   smallint      null,
      type  tinyint       not null)

   print ' '

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

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

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

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

         print ' '

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

         print ' '

         return
      end

   if ((db_id () = 1)  and  (proc_role ('sa_role') > 0))
      begin
         execute  master.dbo.updt_master_stats
      end

   select @dpflag = charindex ('DP', upper (@OFF_STRING)),
          @lgflag = charindex ('LG', upper (@OFF_STRING)),
          @now    = convert (char (8), getdate (), 8),
          @umflag = charindex ('UM', upper (@OFF_STRING))

   ---------------------------------------------------------------------------
   print '    CHECKING sysalternates.                        (%1!)', @now

   if (db_id () = 1)
      begin
         insert into #cst
            select 'alternates.suid',
                   'logins.suid',
                   'suid='  +  convert (varchar (12), a.suid)
            from sysalternates a
            where not exists (select l.*
                              from master.dbo.syslogins l
                              where l.suid = a.suid)
      end
   else
      begin
         insert into #cst
            select 'alternates.suid',
                   'master.logins.suid',
                   'suid='  +  convert (varchar (12), a.suid)
            from sysalternates a
            where not exists (select l.*
                              from master.dbo.syslogins l
                              where l.suid = a.suid)
      end

   insert into #cst
      select 'alternates.altsuid',
             'users.suid',
             'altsysuser='  +  isnull (suser_name (altsuid), convert (varchar (12), altsuid))
      from sysalternates
      where suser_name (altsuid) IS NULL

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

   ---------------------------------------------------------------------------
   print '    CHECKING sysattributes                         (%1!)', @now

   execute sp_cstattributes

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

   ---------------------------------------------------------------------------
   print '    CHECKING syscolumns.                           (%1!)', @now

   execute sp_cstcolumns

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

   ---------------------------------------------------------------------------
   print '    CHECKING syscomments.                          (%1!)', @now

   insert into #cst
      select distinct 'comments.id',
                      'objects.id',
                      'id='  +  convert (varchar (12), id)
      from syscomments
      where object_name (id) IS NULL

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

   ---------------------------------------------------------------------------
   print '    CHECKING sysconstraints.                       (%1!)', @now

   execute sp_cstconstraints

   ---------------------------------------------------------------------------
   if (db_id () = 1)
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    CHECKING sysdatabases.                         (%1!)', @now

         insert into #cst
            select 'databases.suid',
                   'logins.suid',
                   'suid='  +   convert (varchar (12), d.suid)
            from master.dbo.sysdatabases d
            where not exists (select l.*
                              from master.dbo.syslogins l
                              where l.suid = d.suid)

         insert into #cst
            select 'databases.status2 (-32768)',                  -- Incorrect Sybase documentation
                   'sysusages',
                   d.name  +  ', no log'
            from master.dbo.sysdatabases d
            where (convert (int, d.status2) & -32768) = -32768
              and not exists (select u.*
                              from master.dbo.sysusages u
                              where u.dbid         = d.dbid
                                and (u.segmap & 7) = 7)

         insert into #cst
            select 'databases.status2 (~-32768)',                  -- Incorrect Sybase documentation
                   'sysusages',
                   d.name  +  ', log'
            from master.dbo.sysdatabases d
            where (convert (int, d.status2) & -32768) <> -32768
              and exists (select u.*
                          from master.dbo.sysusages u
                          where u.dbid         = d.dbid
                            and (u.segmap & 7) = 7)
      end

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

         print '    CHECKING sysdepends.                           (%1!)', @now

         insert into #cst
            select 'depends.depid',
                   'objects.id',
                   object_name (o.id)
                   + ', depid='
                   +  convert (varchar (12), depid)
            from sysdepends d,
                 sysobjects o
            where d.id                   = o.id
              and o.uid                  = 1
              and object_name (d.depid) IS NULL

         insert into #cst
            select 'depends.depid',
                   'objects.id',
                   user_name (o.uid)
                   +  '.'
                   +  object_name (o.id)
                   + ', depid='
                   +  convert (varchar (12), depid)
            from sysdepends d,
                 sysobjects o
            where d.id                   = o.id
              and o.uid                 <> 1
              and object_name (d.depid) IS NULL

         insert into #cst
            select 'depends.id',
                   'objects.id',
                   'id='  +  convert (varchar (12), id)
            from sysdepends
            where object_name (id) IS NULL
      end

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

   ---------------------------------------------------------------------------
   print '    CHECKING sysindexes.                           (%1!)', @now

   execute sp_cstindexes

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

   ---------------------------------------------------------------------------
   print '    CHECKING syskeys.                              (%1!)', @now

   insert into #cst
      select 'keys.id',
             'objects.depid',
             isnull (object_name (id), convert (varchar (12), id))
             +  ', depid='
             +  convert (varchar (12), depid)
      from syskeys
      where depid <> 0                                            -- !!??
        and object_name (depid) IS NULL

   insert into #cst
      select 'keys.id',
             'objects.id',
             'id='  +  convert (varchar (12), id)
      from syskeys
      where object_name (id) IS NULL

   ---------------------------------------------------------------------------
   if (db_id () = 1)
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    CHECKING sysmessages.                          (%1!)', @now

         insert into #cst
            select 'messages.langid',
                   'languages.langid',
                   'langid='  +  convert (varchar (12), m.langid)
            from master.dbo.sysmessages m
            where langid IS NOT NULL
              and not exists (select l.*
                              from master.dbo.syslanguages l
                              where l.langid = m.langid)
      end

   ---------------------------------------------------------------------------
   if (db_id () = 1)
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    CHECKING sysloginroles.                        (%1!)', @now

         insert into #cst
            select 'loginroles.srid',
                   'srvroles.srid',
                   'srid='  +  convert (varchar (12), lr.srid)
            from master.dbo.sysloginroles lr
            where not exists (select r.*
                              from master.dbo.syssrvroles r
                              where r.srid = lr.srid)

         insert into #cst
            select 'loginroles.suid',
                   'logins.suid',
                   'suid='  +  convert (varchar (12), lr.suid)
            from master.dbo.sysloginroles lr
            where not exists (select l.*
                              from master.dbo.syslogins l
                              where l.suid = lr.suid)
      end

   ---------------------------------------------------------------------------
   if ((db_id () = 1)  and  (@lgflag = 0))
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    CHECKING syslogins.                            (%1!)', @now

         insert into #cst
            select 'logins.language',
                   'languages.name',
                   'l='
                   +  l.language
                   +  ', n='
                   +  l.name
            from master.dbo.syslogins l
            where l.language IS NOT NULL
              and not exists (select g.*
                              from master.dbo.syslanguages g
                              where l.language = g.name)
      end

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

   ---------------------------------------------------------------------------
   print '    CHECKING sysobjects.                           (%1!)', @now

   execute sp_cstobjects

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

   ---------------------------------------------------------------------------
   print '    CHECKING sysprocedures.                        (%1!)', @now

   insert into #cst
      select distinct 'procedures.id',
                      'objects.id',
                      'p='  +  convert (varchar (12), id)
      from sysprocedures
      where object_name (id) IS NULL

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

   ---------------------------------------------------------------------------
   print '    CHECKING sysprotects.                          (%1!)', @now

   insert into #cst
      select 'protects.grantor',
             'users.uid',
             'grantor='  +  convert (varchar (12), grantor)
             +  ', o='  +  isnull (object_name (id), convert (varchar (12), id))
      from sysprotects
      where user_name (grantor) IS NULL

   insert into #cst
      select 'protects.id',
             'objects.id',
             'o='
             +  isnull (object_name (id), convert (varchar (12), id))
             +  ', action='
             +  convert (varchar (6), action)
      from sysprotects
      where id               <> 0
        and object_name (id) IS NULL

   insert into #cst
      select 'protects.uid',
             'users.uid',
             'uid='  +  convert (varchar (12), uid)
      from sysprotects
      where user_name (uid) IS NULL

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

   ---------------------------------------------------------------------------
   print '    CHECKING sysreferences.                        (%1!)', @now

   execute sp_cstreferences

   ---------------------------------------------------------------------------
   if (db_id () = 1)
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    CHECKING sysremotelogins.                      (%1!)', @now

         insert into #cst
            select 'remotelogins.remoteserverid',
                   'servers.srvid',
                   'srvid='  +   convert (varchar (12), rl.remoteserverid)
            from master.dbo.sysremotelogins rl
            where not exists (select s.*
                              from master.dbo.sysservers s
                              where s.srvid = rl.remoteserverid)

         insert into #cst
            select 'remotelogins.suid',
                   'logins.suid',
                   'rname='
                   +  rl.remoteusername
                   +  ', suid='
                   +  convert (varchar (12), rl.suid)
            from master.dbo.sysremotelogins rl
            where rl.suid           <> -1
              and rl.remoteusername IS NOT NULL
              and not exists (select l.*
                              from master.dbo.syslogins l
                              where l.suid = rl.suid)
      end

   ---------------------------------------------------------------------------
   if (db_id () = 1)
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    CHECKING sysresourcelimits                     (%1!)', @now

         insert into #cst
            select 'resourcelimits.name',
                   'logins.name',
                   'name='  +  rl.name
            from master.dbo.sysresourcelimits rl
            where not exists (select l.*
                              from master.dbo.syslogins l
                              where l.name = rl.name)

         insert into #cst
            select 'resourcelimits.rangeid',
                   'timeranges.id',
                   'rangeid='  +  convert (varchar (12), rl.rangeid)
            from master.dbo.sysresourcelimits rl
            where not exists (select tr.*
                              from master.dbo.systimeranges tr
                              where tr.id = rl.rangeid)
      end


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

   ---------------------------------------------------------------------------
   print '    CHECKING sysroles.                             (%1!)', @now

   if (db_id () = 1)
      begin
         insert into #cst
            select 'roles.id',
                   'srvroles.srid',
                   'id='  +  convert (varchar (12), r.id)
                   +  ', lrid='  +  convert (varchar (12), r.lrid)
            from sysroles r
            where not exists (select s.*
                              from master.dbo.syssrvroles s
                              where s.srid = r.id)
      end
   else
      begin
         insert into #cst
            select 'roles.id',
                   'master.srvroles.srid',
                   'id='  +  convert (varchar (12), r.id)
                   +  ', lrid='  +  convert (varchar (12), r.lrid)
            from sysroles r
            where not exists (select s.*
                              from master.dbo.syssrvroles s
                              where s.srid = r.id)
      end

   insert into #cst
      select 'roles.lrid',
             'users.uid',
             'id='  +  convert (varchar (12), r.id)
             +  ', lrid='  +  convert (varchar (12), r.lrid)
      from sysroles r
      where not exists (select u.uid
                        from sysusers u
                        where u.uid = r.lrid)

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

   ---------------------------------------------------------------------------
   print '    CHECKING syssegments.                          (%1!)', @now

   if (db_id () = 1)
      begin
         insert into #cst
            select 'segments.segment',
                   'usages.segmap',
                   'segment='  +  convert (varchar (12), s.segment)
            from syssegments s
            where not exists (select u2.segmap
                              from syssegments s2,
                                   master.dbo.sysusages u2
                              where s2.segment            = s.segment
                                and power (2, s2.segment) = (u2.segmap & power (2, s2.segment)))
      end
   else
      begin
         insert into #cst
            select 'segments.segment',
                   'master.usages.segmap',
                   'segment='  +  convert (varchar (12), s.segment)
            from syssegments s
            where not exists (select u2.segmap
                              from syssegments s2,
                                   master.dbo.sysusages u2
                              where s2.segment            = s.segment
                                and power (2, s2.segment) = (u2.segmap & power (2, s2.segment)))
      end

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

   ---------------------------------------------------------------------------
   print '    CHECKING systhresholds.                        (%1!)', @now

   -- Also need to check systhresholds.currauth/sysroles.id (COMPLEX)

   insert into #cst
      select 'thresholds.segment',
             'segments.segment',
             'segment='  +  convert (varchar (12), t.segment)
      from systhresholds t
      where not exists (select s.*
                        from syssegments s
                        where s.segment = t.segment)

   insert into #cst
      select 'thresholds.segment',
             'non-existent procedure',
             'proc_name='  +  t.proc_name
      from systhresholds t
      where not exists (select o.*
                        from sysobjects o
                        where o.name           = t.proc_name
                          and (o.sysstat & 15) = 4)
        and not exists (select o.*
                        from sybsystemprocs.dbo.sysobjects o
                        where (t.proc_name like 'sp[_]%'  or
                               t.proc_name like 'sybsystemprocs.%.sp[_]%')
                          and o.name           = t.proc_name
                          and (o.sysstat & 15) = 4)

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

   ---------------------------------------------------------------------------
   print '    CHECKING systypes.                             (%1!)', @now

   execute sp_csttypes

   ---------------------------------------------------------------------------
   if (db_id () = 1)
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    CHECKING sysusages.                            (%1!)', @now

         insert into #cst
            select 'usages.dbid',
                   'databases.dbid',
                   'dbid='  +   convert (varchar (12), u.dbid)
            from master.dbo.sysusages u
            where not exists (select d.*
                              from master.dbo.sysdatabases d
                              where d.dbid = u.dbid)

         insert into #cst
            select 'usages.vstart',
                   'devices.low/high',
                   'vstart='  +   convert (varchar (12), u.vstart)
            from master.dbo.sysusages u
            where not exists (select d.*
                              from master.dbo.sysdevices d
                              where d.low  <= u.vstart + u.size
                                and d.high >= u.vstart + u.size - 1)
      end

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

   ---------------------------------------------------------------------------
   if (@umflag = 0)
      begin
         print '    CHECKING sysusermessages.                      (%1!)', @now

         if (db_id () = 1)
            begin
               insert into #cst
                  select 'usermessages.langid',
                         'languages.langid',
                         'langid='  +  convert (varchar (12), m.langid)
                  from sysusermessages m
                  where not exists (select l.*
                                    from master.dbo.syslanguages l
                                    where l.langid = m.langid)

               insert into #cst
                  select 'usermessages.uid',
                         'logins.suid',
                         'e='
                         +  convert (varchar (12), m.error)
                         +  ', uid='
                         +  convert (varchar (12), m.uid)
                  from sysusermessages m
                  where not exists (select l.*
                                    from master.dbo.syslogins l
                                    where l.suid = m.uid)          -- l.suid is correct!
            end
         else
            begin
               insert into #cst
                  select 'usermessages.langid',
                         'master.languages.langid',
                         'langid='  +  convert (varchar (12), m.langid)
                  from sysusermessages m
                  where not exists (select l.*
                                    from master.dbo.syslanguages l
                                    where l.langid = m.langid)

               insert into #cst
                  select 'usermessages.uid',
                         'master.logins.suid',
                         'e='
                         +  convert (varchar (12), m.error)
                         +  ', uid='
                         +  convert (varchar (12), m.uid)
                  from sysusermessages m
                  where not exists (select l.*
                                    from master.dbo.syslogins l
                                    where l.suid = m.uid)          -- l.suid is correct!
            end
      end

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

   ---------------------------------------------------------------------------
   print '    CHECKING sysusers.                             (%1!)', @now

   if (db_id () = 1)
      begin
         insert into #cst
            select 'users.suid',
                   'logins.suid',
                   'user='  +  name
            from sysusers
            where uid                > 2
              and uid                < 16384
              and suser_name (suid) IS NULL
      end
   else
      begin
         insert into #cst
            select 'users.suid',
                   'master.logins.suid',
                   'user='  +  name
            from sysusers
            where uid                > 2
              and uid                < 16384
              and suser_name (suid) IS NULL
      end

   ---------------------------------------------------------------------------

   print ' '

   update #cstwork
   set id1 = user_name (o.uid)  +  '.'  +  object_name (convert (int, id1))
   from sysobjects o
   where o.id          = convert (int, id1)
     and #cstwork.type = 1

   update #cstwork
   set id2 = user_name (o.uid)  +  '.'  +  object_name (convert (int, id2))
   from sysobjects o
   where o.id          = convert (int, id2)
     and #cstwork.type = 1

   update #cstwork
   set id1 = substring (id1, 5, 30)
   where id1 like 'dbo.%'

   update #cstwork
   set id2 = substring (id2, 5, 30)
   where id2 like 'dbo.%'

   update #cstwork
   set id1 = object_name (convert (int, id1), db1)
   where id1                                   IS NOT NULL
     and db1                                   IS NOT NULL
     and object_name (convert (int, id1), db1) IS NOT NULL
     and #cstwork.type                          = 2

   update #cstwork
   set id2 = object_name (convert (int, id2), db2)
   where id2                                   IS NOT NULL
     and db2                                   IS NOT NULL
     and object_name (convert (int, id2), db2) IS NOT NULL
     and #cstwork.type                          = 2

   update #cstwork
   set id3 = object_name (convert (int, id3), db3)
   where id3                                   IS NOT NULL
     and db3                                   IS NOT NULL
     and object_name (convert (int, id3), db3) IS NOT NULL
     and #cstwork.type                          = 2

   insert into #cst
   select fkey,
          pkey,
          substring (txt1 + id1 + txt2 + id2 + txt3 + id3, 1, 60)
   from #cstwork

   if (exists (select *
               from #cst))
      begin
         print '    THERE MAY BE SYSTEM TABLE ERRORS.'

         print ' '

         select @maxftxt = max (char_length (fkey)),
                @maxptxt = max (char_length (pkey))
         from #cst

         if (@maxftxt > 19)
            begin
               if (@maxptxt > 17)
                  begin
                     select 'FOREIGN KEY / STATUS' = substring (fkey, 1, 27),
                            'PRIMARY KEY / TEXT'   = substring (pkey, 1, 24),
                            'TEXT'                 = substring (txt, 1, 24)
                     from #cst
                     order by fkey, pkey, txt
                  end
               else
                  begin
                     select 'FOREIGN KEY/STATUS' = substring (fkey, 1, 27),
                            'PRIMARY KEY/TEXT'   = substring (pkey, 1, 17),
                            'TEXT'               = substring (txt, 1, 31)
                     from #cst
                     order by fkey, pkey, txt
                  end
            end
         else
            begin
               if (@maxptxt > 17)
                  begin
                     select 'FOREIGN KEY / STATUS' = substring (fkey, 1, 19),
                            'PRIMARY KEY / TEXT'   = substring (pkey, 1, 20),
                            'TEXT'                 = substring (txt, 1, 36)
                     from #cst
                     order by fkey, pkey, txt
                  end
               else
                  begin
                     select 'FOREIGN KEY/STATUS' = substring (fkey, 1, 19),
                            'PRIMARY KEY/TEXT'   = substring (pkey, 1, 17),
                            'TEXT'               = substring (txt, 1, 39)
                     from #cst
                     order by fkey, pkey, txt
                  end
            end
      end
   else
      begin
         print '    NO SYSTEM TABLE ERRORS WERE DETECTED.'

         print ' '
      end
go
sp_addcomment sp_checksystables, 11, 'Checks consistency of system tables (with multiple options)'
go
sp_addcomment sp_checksystables, 11, '   (NULL): Maximum checking'
go
sp_addcomment sp_checksystables, 11, '   "DP":   sysdepends not checked (default)'
go
sp_addcomment sp_checksystables, 11, '   "LG":   syslogins not checked'
go
sp_addcomment sp_checksystables, 11, '   "UM":   sysusermessages not checked'
go
sp_procxmode sp_checksystables, anymode
go

dump transaction sybsystemprocs with truncate_only
go
