use sybsystemprocs
go
drop procedure sp_columnsunique
go
drop procedure sp_dbdiff
go
drop procedure sp_domainsright_col_cnstrnts
go
drop procedure sp_domainsright_col_ut_rules
go
drop procedure sp_domainsright
go
drop procedure sp_objectsunique
go
drop procedure sp_protectsright
go
drop procedure sp_refkeysright
go
drop procedure sp_usesright
go

set quoted_identifier on
go

create procedure sp_columnsunique (@NULL_FLAG varchar (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_columnsunique> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @colcount   varchar (5),
           @diffcount  varchar (5)

   create table #columnsunique
      (uname       varchar (12)  not null,
       objectname  varchar (24)  not null,
       columnname  varchar (20)  not null,
       typetext    varchar (20)  not null,
       utype       smallint      not null,
       stype       tinyint       not null,
       len         tinyint       not null,
       prec        tinyint       null,
       scale       tinyint       null,
       nulltype    int           not null,
       sortorder   varchar (30)  null)

   if (@NULL_FLAG IS NULL)
      begin
         print '    CHECKING WILL BE BY TYPE AND LENGTH/PRECISION/SCALE.'

         insert into #columnsunique
            select substring (isnull (user_name (o.uid), '?'), 1, 12),
                   substring (o.name, 1, 24),
                   substring (c.name, 1, 18),
                   substring (t.name, 1, 20),
                   c.usertype,
                   c.type,
                   c.length,
                   c.prec,
                   c.scale,
                   convert (int, convert (bit, (c.status & 8))),
                   NULL
            from syscolumns c,
                 syscolumns c2,
                 sysobjects o,
                 sysobjects o2,
                 systypes   t
            where c.id               = o.id
              and (o.sysstat & 15)  in (2, 3)
              and c2.id              = o2.id
              and (o2.sysstat & 15) in (2, 3)
              and c.name             = c2.name
              and c.number           = 0
              and c.usertype         = t.usertype
              and c2.number          = 0
              and (c.usertype <> c2.usertype  or
                   c.length   <> c2.length    or
                   c.prec     <> c2.prec      or
                   c.scale    <> c2.scale)
      end
   else
      begin
         print '    CHECKING WILL BE BY TYPE, LENGTH/PRECISION/SCALE AND NULLABILITY.'

         insert into #columnsunique
            select substring (isnull (user_name (o.uid), '?'), 1, 12),
                   substring (o.name, 1, 24),
                   substring (c.name, 1, 18),
                   substring (t.name, 1, 20),
                   c.usertype,
                   c.type,
                   c.length,
                   c.prec,
                   c.scale,
                   convert (int, convert (bit, (c.status & 8))),
                   NULL
            from syscolumns c,
                 syscolumns c2,
                 sysobjects o,
                 sysobjects o2,
                 systypes   t
            where c.id               = o.id
              and (o.sysstat & 15)  in (2, 3)
              and c2.id              = o2.id
              and (o2.sysstat & 15) in (2, 3)
              and c.name             = c2.name
              and c.number           = 0
              and c.usertype         = t.usertype
              and c2.number          = 0
              and (c.usertype     <> c2.usertype  or
                   c.length       <> c2.length    or
                   c.prec         <> c2.prec      or
                   c.scale        <> c2.scale     or
                   (c.status & 8) <> (c2.status & 8))
      end

   print ' '

   if (exists (select *
               from #columnsunique))
      begin
         update #columnsunique
         set sortorder = substring (columnname + ' ' + objectname + ' ' + uname,
                                    1,
                                    30)

         select @colcount  = ltrim (substring (convert (char (8),
                                                        convert (money,
                                                                 count (distinct columnname)),
                                                        1),
                                               1,
                                               5)),
                @diffcount = ltrim (substring (convert (char (8),
                                                        convert (money,
                                                                 count (distinct sortorder)),
                                                        1),
                                               1,
                                               5))
         from #columnsunique

         print '    THERE ARE %1! COLUMN NAMES WITH %2! POTENTIAL DIFFERENCES.',
               @colcount, @diffcount

         print ' '

         update #columnsunique
         set len = len / @@ncharsize
         where ((utype = 24)  and  (stype = 47))
            or ((utype = 25)  and  (stype = 39))

         update #columnsunique
         set typetext = typetext
                        + ' ('
                        + convert (varchar (3), len)
                        + ')'
         where utype in (1, 2, 3, 4, 24, 25)

         update #columnsunique
         set typetext = typetext
                        + ' ('
                        + convert (varchar (3), prec)
                        + ', '
                        + convert (varchar (3), scale)
                        + ')'
         where utype in (10, 26)

         update #columnsunique
         set typetext = 'double precision'
         where utype  = 8
           and prec  IS NULL

         update #columnsunique
         set typetext = 'float (' + convert (varchar (3), prec) + ')'
         where utype in (8, 23)
           and prec  IS NOT NULL

         update #columnsunique
         set typetext = 'int'
         where utype in (7, 13)
           and len    = 4

         update #columnsunique
         set typetext = 'real'
         where utype  = 23
           and prec  IS NULL

         update #columnsunique
         set typetext = 'tinyint'
         where utype in (5, 13)
           and len    = 1

         update #columnsunique
         set typetext = 'smallint'
         where utype in (6, 13)
           and len    = 2

         update #columnsunique
         set typetext = typetext + ' *'
         where nulltype = 1

         insert into #columnsunique
            select ' ', ' ', ' ', ' ', 0, 0, 0, 0, 0, 0, min (sortorder)
            from #columnsunique
            group by columnname

         delete from #columnsunique
         where columnname = ' '
           and sortorder  = (select min (sortorder)
                             from #columnsunique)

         select distinct 'OWNER'               = uname,
                         'TABLE/VIEW'          = objectname,
                         'COLUMN'              = substring (columnname, 1, 18),
                         'TYPE (* = NULLABLE)' = typetext
         from #columnsunique
         order by sortorder, columnname
      end
   else if (@NULL_FLAG IS NULL)
      begin
         print '    ALL USER COLUMN NAMES ARE OF UNIQUE TYPE AND LENGTH.'

         print ' '
      end
   else
      begin
         print '    ALL USER COLUMN NAMES ARE OF UNIQUE TYPE, LENGTH AND NULLABILITY.'

         print ' '
      end
go
sp_addcomment sp_columnsunique, 11, 'Checks consistency of user column names by type/length'
go
sp_addcomment sp_columnsunique, 11, '   if NULL_FLAG is non-null check by type/length/nullability'
go
sp_procxmode sp_columnsunique, anymode
go

------------ Temporary tables needed for creation of sp_dbdiff ---------------

select tabname = o.name,
       tabownr = u.suid,
       tabid   = c.id,
       ttype   = (o.sysstat & 15),
       colname = c.name,
       ctype   = c.type,
       clen    = convert (varchar (3), c.length),
       nul     = char (32 + (10 * convert (int, convert (bit, (c.status & 8)))))
into #olddb
from syscolumns c,
     sysobjects o,
     sysusers u
where c.id              = o.id
  and u.uid             = o.uid
  and (o.sysstat & 15) in (2, 3)

select *
into #newdb
from #olddb

select procname = o.name,
       procownr = u.suid,
       ostatus  = (o.sysstat & 15),
       pstatus  = (p.status & 4096)
into #oldprocs
from sysobjects o,
     sysprocedures p,
     sysusers u
where (o.sysstat & 15) = 4
  and u.uid            = o.uid
  and p.id             = o.id
  and p.sequence       = 1
go


create procedure sp_dbdiff (@OTHERDB  varchar (30)) as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   declare @currdb  varchar (30)

   print ' '

   select @currdb = db_name ()

   if (@currdb = @OTHERDB)
      begin
         print '    DATABASES <%1!> AND <%2!> MUST BE DIFFERENT.',
               @currdb, @OTHERDB

         print ' '

         return
      end
   else
      begin
         select 'CURRENT DB' = substring (@currdb, 1, 28),
                'OTHER DB'   = substring (@OTHERDB, 1, 28),
                'DATE RUN'   = convert (char (19), getdate ())
      end

   print ' '

   set nocount off

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

   if (@@isolation > 1)
      begin
         set transaction isolation level 1
      end

   if (exists (select op.*
               from #oldprocs op
               where not exists (select p.*
                                 from sysobjects o,
                                      sysprocedures p,
                                      sysusers u
                                 where o.name             = op.procname
                                   and (o.sysstat & 15)   = op.ostatus
                                   and u.uid              = o.uid
                                   and u.suid             = op.procownr
                                   and p.id               = o.id
                                   and p.sequence         = 1
                                   and (p.status & 4096) <> 4096)))
      begin
            select 'DROPPED PROCEDURES/RULES' = space (30),
                   OWNER                      = space (29),
                   'TYPE'                     = space (15)
            where 1 = 2
         union
            select op.procname,
                   substring (suser_name (op.procownr), 1, 29),
                   'rule'
            from #oldprocs op
            where op.ostatus = 7
              and not exists (select p.*
                              from sysobjects o,
                                   sysprocedures p,
                                   sysusers u
                              where o.name             = op.procname
                                and (o.sysstat & 15)   = 7
                                and o.uid              = u.uid
                                and u.suid             = op.procownr
                                and p.id               = o.id
                                and p.sequence         = 1
                                and (p.status & 4096) <> 4096)
         union
            select op.procname,
                   substring (suser_name (op.procownr), 1, 29),
                   'stored procedure'
            from #oldprocs op
            where op.ostatus = 4
              and not exists (select o.*
                              from sysobjects o,
                                   sysusers u
                              where o.name           = op.procname
                                and (o.sysstat & 15) = 4
                                and o.uid            = u.uid
                                and u.suid           = op.procownr)
         order by 'DROPPED PROCEDURES/RULES', 'TYPE'
      end
   else
      begin
         print '    THERE ARE NO DROPPED PROCEDURES/RULES.'
      end

   print ' '

   if (exists (select p.*
               from sysobjects o,
                    sysprocedures p,
                    sysusers u
               where (((o.sysstat & 15) = 4)  or
                      (((o.sysstat & 15) in (6, 7))  and  (p.status & 4096) <> 4096))
                 and u.uid              = o.uid
                 and p.id               = o.id
                 and p.sequence         = 1
                 and (p.status & 4096) <> 4096
                 and not exists (select op.*
                                 from #oldprocs op
                                 where op.procname = o.name
                                   and op.procownr = u.suid
                                   and op.ostatus  = (o.sysstat & 15))))
      begin
            select 'NEW PROCEDURES/RULES' = space (30),
                   OWNER                  = space (29),
                   'TYPE'                 = space (15)
            where 1 = 2
         union
            select o.name,
                   substring (suser_name (u.suid), 1, 29),
                   'rule'
            from sysobjects o,
                 sysprocedures p,
                 sysusers u
            where (o.sysstat & 15)   = 7
              and u.uid              = o.id
              and p.id               = o.id
              and p.sequence         = 1
              and (p.status & 4096) <> 4096
              and not exists (select op.*
                              from #oldprocs op
                              where op.procname = o.name
                                and op.procownr = u.suid
                                and op.ostatus  = 4)
         union
            select o.name,
                   substring (suser_name (u.suid), 1, 29),
                   'stored procedure'
            from sysobjects o,
                 sysusers u
            where (o.sysstat & 15) = 4
              and u.uid            = o.uid
              and not exists (select op.*
                              from #oldprocs op
                              where op.procname = o.name
                                and op.procownr = u.suid
                                and op.ostatus  = 4)
         order by 'NEW PROCEDURES/RULES', 'TYPE'
      end
   else
      begin
         print '    THERE ARE NO NEW PROCEDURES/RULES.'
      end

   print ' '

   if (exists (select o.*
               from #olddb o
               where not exists (select n.*
                                 from #newdb n
                                 where n.tabname = o.tabname
                                   and n.tabownr = o.tabownr
                                   and n.ttype   = o.ttype)))
      begin
         select distinct 'DROPPED TABLES/VIEWS' = o.tabname,
                         OWNER                  = substring (suser_name (o.tabownr), 1, 29),
                         'TYPE'                 = substring (vO.name, 1, 10)
         from #olddb o,
              sybsystemprocs.dbo.usptvalues vO
         where vO.type    = 'O'
           and vO.number  = o.ttype
           and not exists (select n.*
                           from #newdb n
                           where n.tabname = o.tabname
                             and n.tabownr = o.tabownr
                             and n.ttype   = o.ttype)
         order by o.tabname, suser_name (o.tabownr)
      end
   else
      begin
         print '    THERE ARE NO DROPPED TABLES/VIEWS.'
      end

   print ' '

   if (exists (select n.*
               from #newdb n
               where not exists (select o.*
                                 from #olddb o
                                 where o.tabname = n.tabname
                                   and o.tabownr = n.tabownr
                                   and o.ttype   = n.ttype)))
      begin
         select distinct 'NEW TABLES/VIEWS' = n.tabname,
                         OWNER              = substring (suser_name (n.tabownr), 1, 29),
                         'TYPE'             = substring (vO.name, 1, 10)
         from #newdb n,
              sybsystemprocs.dbo.usptvalues vO
         where vO.type    = 'O'
           and vO.number  = n.ttype
           and not exists (select o.*
                           from #olddb o
                           where o.tabname = n.tabname
                             and o.tabownr = n.tabownr
                             and o.ttype   = n.ttype)
         order by n.tabname
      end
   else
      begin
         print '    THERE ARE NO NEW TABLES/VIEWS.'
      end

   print ' '

   if (exists (select o.*
               from #olddb o
               where exists     (select n.*
                                 from #newdb n
                                 where n.tabname = o.tabname
                                   and n.tabownr = o.tabownr
                                   and n.ttype   = o.ttype)
                 and not exists (select n.*
                                 from #newdb n
                                 where n.tabname = o.tabname
                                   and n.tabownr = o.tabownr
                                   and n.ttype   = o.ttype
                                   and n.colname = o.colname)))
      begin
         select 'TABLE/VIEW'     = substring (o.tabname + ' (' + suser_name (o.tabownr) + ')', 1, 40),
                'DROPPED COLUMN' = substring (o.colname, 1, 20),
                'TYPE'           = substring (t.name + ' (' + o.clen + ') ' + o.nul, 1, 15)
         from #olddb o,
              systypes t
         where o.ctype    = t.type
           and t.type     < 100
           and t.usertype not in (18, 24, 25, 80)
           and exists     (select n.*
                           from #newdb n
                           where n.tabname = o.tabname
                             and n.tabownr = o.tabownr
                             and n.ttype   = o.ttype)
           and not exists (select n.*
                           from #newdb n
                           where n.tabname = o.tabname
                             and n.tabownr = o.tabownr
                             and n.ttype   = o.ttype
                             and n.colname = o.colname)
         order by o.tabname, suser_name (o.tabownr), o.colname
      end
   else
      begin
         print '    THERE ARE NO DROPPED COLUMNS.'
      end

   print ' '

   if (exists (select n.*
               from #newdb n
               where exists     (select o.*
                                 from #olddb o
                                 where o.tabname = n.tabname
                                   and o.tabownr = n.tabownr
                                   and o.ttype   = n.ttype)
                 and not exists (select o.*
                                 from #olddb o
                                 where o.tabname = n.tabname
                                   and o.tabownr = n.tabownr
                                   and o.ttype   = n.ttype
                                   and o.colname = n.colname)))
      begin
         select 'TABLE/VIEW' = substring (n.tabname + ' (' + suser_name (n.tabownr) + ')', 1, 40),
                'NEW COLUMN' = substring (n.colname, 1, 20),
                'TYPE'       = substring (t.name + ' (' + n.clen + ') ' + n.nul, 1, 15)
         from #newdb n,
              systypes t
         where n.ctype    = t.type
           and t.type     < 100
           and t.usertype not in (18, 24, 25, 80)
           and exists     (select o.*
                           from #olddb o
                           where o.tabname = n.tabname
                             and o.tabownr = n.tabownr
                             and o.ttype   = n.ttype)
           and not exists (select o.*
                           from #olddb o
                           where o.tabname = n.tabname
                             and o.tabownr = n.tabownr
                             and o.ttype   = n.ttype
                             and o.colname = n.colname)
         order by n.tabname, suser_name (n.tabownr), n.colname
      end
   else
      begin
         print '    THERE ARE NO NEW COLUMNS.'
      end

   print ' '

   if (exists (select n.*
               from #newdb n,
                    #olddb o
               where n.tabname  = o.tabname
                 and n.tabownr  = o.tabownr
                 and n.colname  = o.colname
                 and (n.ctype  <> o.ctype  or
                      n.clen   <> o.clen   or
                      n.nul    <> o.nul)))
      begin
         select 'TABLE/VIEW' = substring (n.tabname + ' (' + suser_name (n.tabownr) + ')', 1, 24),
                'COLUMN'     = substring (n.colname, 1, 18),
                'OLD TYPE'   = substring (tx.name + ' (' + o.clen + ') ' + o.nul, 1, 16),
                'NEW TYPE'   = substring (tn.name + ' (' + n.clen + ') ' + n.nul, 1, 16)
         from #newdb n,
              #olddb o,
              systypes tn,
              systypes tx
         where n.tabname  = o.tabname
           and n.tabownr  = o.tabownr
           and n.colname  = o.colname
           and n.ctype    = tn.type
           and tn.type    < 100
           and tn.usertype not in (18, 24, 25, 80)
           and o.ctype    = tx.type
           and tx.type    < 100
           and tx.usertype not in (18, 24, 25, 80)
           and (n.ctype  <> o.ctype  or
                n.clen   <> o.clen   or
                n.nul    <> o.nul)
         order by n.tabname, suser_name (n.tabownr), n.colname
      end
   else
      begin
         print '    THERE ARE NO COLUMN ATTRIBUTE DIFFERENCES.'
      end

   print ' '
go
sp_addcomment sp_dbdiff, 11, 'Lists differences between OTHERDB and current database'
go
sp_procxmode sp_dbdiff, anymode
go

------------ Temporary tables needed for sp_domainsright subroutines ---------

create table #dr23
   (id        int           not null,
    uid       smallint      not null,
    TC        varchar (60)  not null,
    colid     tinyint       not null,
    cnstrid   int           not null,
    cnstrtxt  varchar (60)  not null,
    cnstrtype varchar (11)  not null,
    sorttext  varchar (60)  not null)
go

create table #dr45
   (id         int           not null,
    uid        smallint      not null,
    TC         varchar (60)  not null,
    colid      tinyint       not null,
    cnstrid    int           not null,
    cnstrtxt   varchar (60)  not null,
    cnstrtype  varchar (11)  not null,
    utid       int           not null,
    uttxt      varchar (60)  not null,
    utdfltid   int           not null,
    utdflttxt  varchar (60)  not null,
    sorttxt    varchar (60)  not null)
go

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

   declare @CR         char (1),
           @LF         char (1),
           @LFas       char (4),
           @LFasLF     char (4),
           @as         char (4),
           @asLF       char (4),
           @maxtext    tinyint

   select @CR = char (13),                            -- Carriage Return
          @LF = char (10)                             -- Line Feed

   select @as     = ' AS ',
          @asLF   = ' AS' + @LF,
          @LFas   = @LF + 'AS ',
          @LFasLF = @LF + 'AS' + @LF

   update #dr23
   set TC = substring (TC + '.' + col_name (id, colid), 1, 60)

   update #dr23
   set TC = substring (TC + ' (' + user_name (o.uid) + ')', 1, 60)
   from #dr23 d,
        sysobjects o
   where o.id = d.id
     and ((o.uid <> 1)  or
          (1      < (select count (o2.uid)
                     from sysobjects o2
                     where o.name            like o2.name + '%'
                       and (o2.sysstat & 15)    = 7)))

   update #dr23
   set sorttext = TC

   update #dr23
   set cnstrtxt = substring (c.text, 1, 60)
   from #dr23 d,
        syscomments c
   where d.cnstrtype  = 'check'
     and c.id       = d.cnstrid
     and c.colid    = 1
     and c.colid2   = 0
     and c.texttype = 0

   update #dr23
   set cnstrtxt = substring (c.text, charindex ('(@', text), 60)
   from #dr23 d,
        syscomments c
   where d.cnstrtype              = 'rule'
     and c.id                   = d.cnstrid
     and c.texttype             = 0
     and charindex ('(@', text) > 0

   update #dr23
   set cnstrtxt = substring (c.text, charindex ('@', text), 60)
   from #dr23 d,
        syscomments c
   where d.cnstrtype             = 'rule'
     and d.cnstrtxt            = ' '
     and c.id                  = d.cnstrid
     and c.texttype            = 0
     and charindex ('@', text) > 0

   update #dr23
   set cnstrtxt = ltrim (substring (cnstrtxt, charindex ('(', cnstrtxt) + 1, 60))
   where cnstrtype = 'check'

   update #dr23
   set cnstrtxt = substring (cnstrtxt, charindex (@as, upper (cnstrtxt)) + 4, 60)
   where cnstrtype                      = 'rule'
     and charindex (@as, upper (cnstrtxt)) > 0

   update #dr23
   set cnstrtxt = substring (cnstrtxt, charindex (@asLF, upper (cnstrtxt)) + 4, 60)
   where cnstrtype                        = 'rule'
     and charindex (@asLF, upper (cnstrtxt)) > 0

   update #dr23
   set cnstrtxt = substring (cnstrtxt, charindex (@LFas, upper (cnstrtxt)) + 4, 60)
   where cnstrtype                        = 'rule'
     and charindex (@LFas, upper (cnstrtxt)) > 0

   update #dr23
   set cnstrtxt = substring (cnstrtxt, charindex (@LFasLF, upper (cnstrtxt)) + 4, 60)
   where cnstrtype                          = 'rule'
     and charindex (@LFasLF, upper (cnstrtxt)) > 0

   update #dr23
   set cnstrtxt = substring (cnstrtxt, 1, charindex (@CR, cnstrtxt) - 1)
             + ' '
             + substring (cnstrtxt, charindex (@CR, cnstrtxt) + 1, 60)
   where charindex (@CR, cnstrtxt) > 1

   update #dr23
   set cnstrtxt = substring (cnstrtxt, 1, charindex (@LF, cnstrtxt) - 1)
             + ' '
             + substring (cnstrtxt, charindex (@LF, cnstrtxt) + 1, 60)
   where charindex (@LF, cnstrtxt) > 1

   update #dr23
   set cnstrtxt = substring (cnstrtxt, 1, char_length (cnstrtxt) - 1)
   where cnstrtype = 'check'
     and cnstrtxt like '%)'

   update #dr23
   set cnstrtxt = substring (cnstrtxt, 1, char_length (cnstrtxt) - 2)
   where cnstrtype = 'check'
     and cnstrtxt like '%),'

   select @maxtext = max (char_length (TC))
   from #dr23

   update #dr23
   set TC = ' '
   from #dr23 d1
   where exists (select *
                 from #dr23 d2
                 where d2.id        = d1.id
                   and d2.colid     = d1.colid
                   and d2.cnstrtype = d1.cnstrtype
                   and d2.cnstrtxt  = d1.cnstrtxt
                   and (d2.cnstrtype + '  ' + d2.cnstrtxt) > (select min (d3.cnstrtype + '  ' + d3.cnstrtxt)
                                                              from #dr23 d3
                                                              where d3.id    = d2.id
                                                                and d3.colid = d2.colid))

   if (@maxtext < 26)
      begin
         select 'TABLE.COLUMN' = substring (TC, 1, 25),
                TYPE           = cnstrtype,
                DETAIL         = substring (cnstrtxt, 1, 39)
         from #dr23
         order by sorttext, colid, cnstrtype, cnstrtxt
      end
   else if (@maxtext < 31)
      begin
         select 'TABLE.COLUMN' = substring (TC, 1, 30),
                TYPE           = cnstrtype,
                DETAIL         = substring (cnstrtxt, 1, 34)
         from #dr23
         order by sorttext, colid, cnstrtype, cnstrtxt
      end
   else
      begin
         select 'TABLE.COLUMN' = substring (TC, 1, 35),
                TYPE           = cnstrtype,
                DETAIL         = substring (cnstrtxt, 1, 29)
         from #dr23
         order by sorttext, colid, cnstrtype, cnstrtxt
      end
go
sp_procxmode sp_domainsright_col_cnstrnts, unchained
go

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

   declare @CR         char (1),
           @LF         char (1),
           @LFas       char (4),
           @LFasLF     char (4),
           @as         char (4),
           @asLF       char (4)

   select @CR = char (13),                            -- Carriage Return
          @LF = char (10)                             -- Line Feed

   select @as     = ' AS ',
          @asLF   = ' AS' + @LF,
          @LFas   = @LF + 'AS ',
          @LFasLF = @LF + 'AS' + @LF

   update #dr45
   set TC = substring (d.TC + ' (' + user_name (d.uid) + ')', 1, 60)
   from #dr45 d,
        sysobjects o
   where o.id = d.id
     and ((d.uid <> 1)  or
          (1       < (select count (o2.uid)
                                    from sysobjects o2
                                    where o2.name            = object_name (o.id)
                                      and (o2.sysstat & 15) in (2, 3))))

   update #dr45
   set utdflttxt = substring (c.text, 1, 60)
   from #dr45 d,
        syscomments c
   where c.id       = d.utdfltid
     and c.colid    = 1
     and c.colid2   = 0
     and c.texttype = 0

   update #dr45
   set utdflttxt = substring (utdflttxt, charindex (@as, upper (utdflttxt)) + 4, 60)
   where charindex (@as, upper (utdflttxt)) > 0

   update #dr45
   set utdflttxt = substring (utdflttxt, charindex (@asLF, upper (utdflttxt)) + 4, 60)
   where charindex (@asLF, upper (utdflttxt)) > 0

   update #dr45
   set utdflttxt = substring (utdflttxt, charindex (@LFas, upper (utdflttxt)) + 4, 60)
   where charindex (@LFas, upper (utdflttxt)) > 0

   update #dr45
   set utdflttxt = substring (utdflttxt, charindex (@LFasLF, upper (utdflttxt)) + 4, 60)
   where charindex (@LFasLF, upper (utdflttxt)) > 0

   update #dr45
   set utdflttxt = stuff (utdflttxt, charindex (@CR, utdflttxt), 1, ' ')
   where charindex (@CR, utdflttxt) > 0

   update #dr45
   set utdflttxt = stuff (utdflttxt, charindex (@LF, utdflttxt), 1, ' ')
   where charindex (@LF, utdflttxt) > 0

   update #dr45
   set sorttxt = TC

   update #dr45
   set TC = ' '
   from #dr45 d1
   where exists (select *
                 from #dr45 d2
                 where d2.TC       = d1.TC
                   and d2.cnstrtxt = d1.cnstrtxt
                   and d2.cnstrtxt > (select min (d3.cnstrtxt)
                                      from #dr45 d3
                                      where d3.TC = d2.TC))

   select 'TABLE.COLUMN'  = substring (TC, 1, 28),
          'RULE / CHECK'  = substring (cnstrtxt, 1, 17),
          USERTYPE        = substring (uttxt, 1, 12),
          'USERTYPE RULE' = substring (utdflttxt, 1, 17)
   from #dr45
   order by sorttxt, cnstrtxt
go
sp_procxmode sp_domainsright_col_ut_rules, unchained
go

drop table #dr23
go
drop table #dr45
go

create procedure sp_domainsright as

   --   SOME OF THE WORK TABLES ARE USED IN MULTIPLE SECTIONS AND THE COLUMN
   --   NAMES CAN BE MISLEADING.  THE COMMENTS FOR THE TABLE DECLARATIONS
   --   ARE ONLY GUARANTEED FOR THE INITIAL TABLE USAGE.

   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_domainsright> 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),
           @maxtext    tinyint

   select @CR = char (13),                            -- Carriage Return
          @LF = char (10)                             -- Line Feed

   select @as     = ' AS ',
          @asLF   = ' AS' + @LF,
          @LFas   = @LF + 'AS ',
          @LFasLF = @LF + 'AS' + @LF

   create table #dr1
      (id     int           not null,      -- object id
       uid    smallint      not null,      -- object uid
       itext  varchar (53)  not null,      -- identifier text
       itype  varchar (23)  not null)      -- identifier type

   --  SEARCH FOR IMPROPER QUOTED IDENTIFIERS.

   insert into #dr1
      select distinct o.id, o.uid, o.name, vO.name
      from sysobjects o,
           sybsystemprocs.dbo.usptvalues vO
      where ((valid_name (o.name) = 0)  or  (charindex ('"', o.name) > 0))
        and vO.number  = convert (int, o.sysstat & 15)
        and vO.type    = 'O'
        and vO.number in (4, 6, 8)

   insert into #dr1
      select o.id, o.uid, i.name, 'index'
      from sysindexes i,
           sysobjects o
      where i.indid > 0
        and i.indid < 254
        and ((valid_name (i.name) = 0)  or  (charindex ('"', i.name) > 0))
        and o.id    = i.id

   insert into #dr1
      select o.id, o.uid, o.name, 'rule'
      from sysobjects o,
           sysprocedures p
      where ((valid_name (o.name) = 0)  or  (charindex ('"', o.name) > 0))
        and (o.sysstat & 15)   = 7
        and p.id               = o.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096

   insert into #dr1
      select 0, 1, name, 'segment'
      from syssegments
      where ((valid_name (name) = 0)  or  (charindex ('"', name) > 0))
        and name <> 'default'

   insert into #dr1
      select 0, uid, name, 'usertype'
      from systypes
      where valid_name (name)     = 0
         or charindex ('"', name) > 0

   update #dr1
   set itext = substring (itext + ' (' + user_name (o.uid) + ')', 1, 53)
   from #dr1 d,
        sysobjects o
   where o.id = d.id
     and ((o.uid <> 1)  or
          (1      < (select count (o2.uid)
                     from sysobjects o2
                     where o.name like o2.name + '%')))

   update #dr1
   set itext = substring (object_name (id) + '.' + itext, 1, 53)
   where itype = 'index'

   if (db_id () = 1)
      begin
         insert into #dr1
            select 0, 0, name, 'character set'
            from master.dbo.syscharsets
            where valid_name (name)     = 0
               or charindex ('"', name) > 0

         insert into #dr1
            select 0, 0, name, 'database'
            from master.dbo.sysdatabases
            where valid_name (name)     = 0
               or charindex ('"', name) > 0

         insert into #dr1
            select 0, 0, name, 'device'
            from master.dbo.sysdevices
            where valid_name (name)     = 0
               or charindex ('"', name) > 0

         insert into #dr1
            select 0, 0, name, 'language'
            from master.dbo.syslanguages
            where valid_name (name)     = 0
               or charindex ('"', name) > 0

         insert into #dr1
            select 0, 0, name, 'login'
            from master.dbo.syslogins
            where valid_name (name)     = 0
               or charindex ('"', name) > 0
      end
   else
      begin
         insert into #dr1
            select 0, 0, db_name (), 'database'
            where valid_name (db_name())     = 0
               or charindex ('"', db_name()) > 0
      end

   if (exists (select *
               from #dr1))
      begin
         print '    THERE ARE INVALID QUOTED IDENTIFIERS.'

         print ' '

         select @maxtext = max (char_length (itext))
         from #dr1

         if (@maxtext < 26)
            begin
               select IDENTIFIER        = substring (itext, 1, 25),
                      'IDENTIFIER TYPE' = itype
               from #dr1
               order by itext, itype
            end
         else if (@maxtext < 41)
            begin
               select IDENTIFIER        = substring (itext, 1, 40),
                      'IDENTIFIER TYPE' = itype
               from #dr1
               order by itext, itype
            end
         else
            begin
               select IDENTIFIER        = itext,
                      'IDENTIFIER TYPE' = itype
               from #dr1
               order by itext, itype
            end
      end
   else
      begin
         print '    ALL QUOTED IDENTIFIERS ARE FOR VALID OBJECTS.'
      end

   drop table #dr1

   print ' '

   --  SEARCH FOR COLUMNS WITH MULTIPLE CONTRAINTS (INCLUDING SINGULAR
   --  FOREIGN KEY CONSTRAINTS)

   create table #dr23
      (id        int           not null,   -- table id
       uid       smallint      not null,   -- table uid
       TC        varchar (60)  not null,   -- table.column text
       colid     tinyint       not null,   -- column id
       cnstrid   int           not null,   -- constraint id
       cnstrtxt  varchar (60)  not null,   -- constraint text
       cnstrtype varchar (11)  not null,   -- constraint type
       sorttext  varchar (60)  not null)   -- since TC may be blanked out ...

   insert into #dr23
      select o.id, o.uid, o.name, c.colid, c.constrid, ' ', 'check', ' '
      from sysconstraints c,
           sysobjects o
      where (c.status & 128) = 128
        and o.id             = c.tableid
        and c.colid          > 0

   insert into #dr23
      select o.id, o.uid, o.name, c.colid, c.domain, ' ', 'rule', ' '
      from syscolumns c,
           sysobjects o,
           sysprocedures p
      where (p.status & 4096) <> 4096
        and p.sequence         = 1
        and c.domain           = p.id
        and o.id               = c.id

   insert into #dr23
      select o.id,
             o.uid,
             o.name,
             r.fokey1,
             r.constrid,
             substring (object_name (r.reftabid, r.pmrydbid)
                        + ' ('
                        + col_name (r.reftabid, r.refkey1, r.pmrydbid)
                        + ')',
                        1,
                        60),
             'foreign key',
             ' '
      from sysreferences r,
           sysobjects o
      where r.fokey2 = 0
        and o.id     = r.tableid

   delete #dr23
   from #dr23 d
   where 1 = (select count (dx.id)
              from #dr23 dx
              where d.id    = dx.id
                and d.colid = dx.colid)

   if (exists (select *
               from #dr23))
      begin
         print '    THERE ARE COLUMNS WITH CHECK/RULE/SINGULAR FOREIGN KEY CONSTRAINTS.'

         print ' '

         exec sp_domainsright_col_cnstrnts
      end
   else
      begin
         print '    NO COLUMN HAS CHECK/RULE/SINGULAR FOREIGN KEY CONSTRAINTS.'
      end

   print ' '

   delete from #dr23

   --  SEARCH FOR COLUMN CHECKS WHICH DO NOT REFERENCE THE COLUMN.  THIS
   --  SEARCH IS FAR FROM FOOLPROOF.

   if (exists (select o.*
               from syscolumns c,
                    syscomments cmc,
                    sysconstraints cn,
                    sysobjects o
               where o.id                         = cn.constrid
                 and (o.sysstat & 15)             = 7
                 and c.id                         = cn.tableid
                 and c.colid                      = cn.colid
                 and cmc.id                       = cn.constrid
                 and ((cmc.text like 'CHECK %')  or  (cmc.text like 'CONSTRAINT %'))
                 and charindex (c.name, cmc.text) = 0))
      begin
         print '    THERE ARE COLUMN CHECKS NOT REFERENCING THE COLUMN.'

         print ' '

         insert into #dr23
            select distinct 0,
                            0,
                            substring (object_name (c.id) + '.' + c.name, 1, 60),
                            0,
                            0,
                            substring (substring (cmc.text,
                                                  1 + charindex ('(', cmc.text),
                                                  char_length (cmc.text) - charindex ('(', cmc.text) - 1),
                                       1,
                                       60),
                            ' ',
                            ' '
            from syscolumns c,
                 syscomments cmc,
                 sysconstraints cn,
                 sysobjects o
            where (o.sysstat & 15)             = 7
              and cn.colid                     > 0
              and c.id                         = cn.tableid
              and c.colid                      = cn.colid
              and cmc.id                       = cn.constrid
              and ((cmc.text like 'CHECK %')  or  (cmc.text like 'CONSTRAINT %'))
              and charindex (c.name, cmc.text) = 0

         update #dr23
         set sorttext = TC

         update #dr23
         set TC = ' '
         from #dr23 d1
         where exists (select *
                       from #dr23 d2
                       where d2.TC       = d1.TC
                         and d2.cnstrtxt = d1.cnstrtxt
                         and d2.cnstrtxt > (select min (d3.cnstrtxt)
                                            from #dr23 d3
                                            where d3.TC    = d2.TC))

         select 'TABLE.COLUMN' = substring (TC, 1, 25),
                'CHECK'        = substring (cnstrtxt, 1, 51)
         from #dr23
         order by sorttext, cnstrtxt
      end
   else
      begin
         print '    ALL COLUMN CONTRAINTS HAVE A REFERENCE TO THE COLUMN.'
      end

   drop table #dr23

   print ' '

   --  SEARCH FOR COLUMNS WITH OVERRIDES OF USERTYPE DEFAULTS.

   create table #dr45
      (id         int           not null,   -- table id
       uid        smallint      not null,   -- table uid
       TC         varchar (60)  not null,   -- table.column text
       colid      tinyint       not null,   -- column id
       cnstrid    int           not null,   -- column constraint id
       cnstrtxt   varchar (60)  not null,   -- column constraint text
       cnstrtype  varchar (11)  not null,   -- literal/usertype
       utid       int           not null,   -- usertype
       uttxt      varchar (60)  not null,   -- usertype text
       utdfltid   int           not null,   -- usertype default id
       utdflttxt  varchar (60)  not null,   -- usertype default text
       sorttxt    varchar (60)  not null)

   insert into #dr45                                  -- literal defaults
      select o.id,
             o.uid,
             o.name + '.' + c.name,
             c.colid,
             c.cdefault,
             ltrim (substring (cm.text, 8, 60)),
             'literal',
             t.usertype,
             t.name,
             t.tdefault,
             object_name (t.tdefault),
             ' '
      from syscolumns c,
           syscomments cm,
           sysobjects o,
           sysprocedures p,
           systypes t
      where t.usertype         > 99
        and t.tdefault        <> 0
        and t.usertype         = c.usertype
        and t.tdefault        <> c.cdefault
        and c.id               = o.id
        and (o.sysstat & 15)   = 3
        and cm.id              = c.cdefault
        and cm.colid           = 1
        and cm.texttype        = 0
        and p.id               = c.cdefault
        and p.sequence         = 1
        and (p.status & 4096)  = 4096

   update #dr45
   set cnstrtxt = substring (cnstrtxt, 1, charindex (@CR, cnstrtxt) - 1)
   where charindex (@CR, cnstrtxt) > 0

   update #dr45
   set cnstrtxt = substring (cnstrtxt, 1, charindex (@LF, cnstrtxt) - 1)
   where charindex (@LF, cnstrtxt) > 0

   update #dr45
   set cnstrtxt = substring (cnstrtxt, 2, char_length (cnstrtxt) - 2)
   where char_length (cnstrtxt) > 2

   insert into #dr45                                  -- named defaults
      select o.id,
             o.uid,
             o.name + '.' + c.name,
             c.colid,
             c.cdefault,
             object_name (c.cdefault),
             'usertype',
             t.usertype,
             t.name,
             t.tdefault,
             object_name (t.tdefault),
             ' '
      from syscolumns c,
           sysobjects o,
           sysprocedures p,
           systypes t
      where t.usertype         > 99
        and t.tdefault        <> 0
        and t.usertype         = c.usertype
        and t.tdefault        <> c.cdefault
        and c.id               = o.id
        and (o.sysstat & 15)   = 3
        and p.id               = c.cdefault
        and p.sequence         = 1
        and (p.status & 4096) <> 4096

   update #dr45
   set TC = substring (d.TC + ' (' + user_name (d.uid) + ')', 1, 60)
   from #dr45 d,
        sysobjects o
   where o.id = d.id
     and ((d.uid <> 1)  or
          (1      < (select count (o2.uid)
                                   from sysobjects o2
                                   where o2.name            = object_name (o.id)
                                     and (o2.sysstat & 15) in (2, 3))))

   update #dr45
   set cnstrtxt = substring (d.cnstrtxt + ' (' + user_name (o.uid) + ')', 1, 60)
   from #dr45 d,
        sysobjects o
   where d.cnstrtype  = 'usertype'
     and o.id       = d.cnstrid
     and o.uid     <> d.uid

   update #dr45
   set utdflttxt = substring (d.utdflttxt + ' (' + user_name (t.uid) + ')', 1, 60)
   from #dr45 d,
        systypes t
   where t.usertype  = d.utid
     and t.uid      <> d.uid

   if (exists (select *
               from #dr45))
      begin
         if (1 < (select count (*)
                  from #dr45))
            begin
               print '    USERTYPE DEFAULTS HAVE BEEN OVERRIDEN.'
            end
         else
            begin
               print '    A USERTYPE DEFAULT HAS BEEN OVERRIDEN.'
            end

         print ' '

         update #dr45
         set sorttxt = TC

         select 'TABLE.COLUMN'     = substring (TC, 1, 28),
                'DEFAULT'          = substring (cnstrtxt, 1, 17),
                USERTYPE           = substring (uttxt, 1, 12),
                'USERTYPE DEFAULT' = substring (utdflttxt, 1, 17)
         from #dr45
         order by TC
      end
   else
      begin
         print '    NO USERTYPE DEFAULT HAS BEEN OVERRIDEN.'
      end

   print ' '

   delete from #dr45

   --  SEARCH FOR COLUMNS WITH ADDITIONS/OVERRIDES OF USERTYPE RULES.

   insert into #dr45                                  -- rules
      select o.id,
             o.uid,
             o.name + '.' + c.name,
             c.colid,
             c.domain,
             cm.text,
             'rule',
             t.usertype,
             t.name,
             t.domain,
             ' ', ' '
      from syscolumns c,
           syscomments cm,
           sysobjects o,
           sysprocedures p,
           systypes t
      where t.usertype         > 99
        and t.domain          <> 0
        and c.usertype         = t.usertype
        and c.domain          <> t.domain
        and o.id               = c.id
        and (o.sysstat & 15)   = 3
        and p.id               = c.domain
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and cm.id              = c.domain
        and cm.colid           = 1
        and cm.colid2          = 0
        and cm.texttype        = 0

   update #dr45
   set cnstrtxt = substring (cnstrtxt, charindex (@as, upper (cnstrtxt)) + 4, 60)
   where charindex (@as, upper (cnstrtxt)) > 0

   update #dr45
   set cnstrtxt = substring (cnstrtxt, charindex (@asLF, upper (cnstrtxt)) + 4, 60)
   where charindex (@asLF, upper (cnstrtxt)) > 0

   update #dr45
   set cnstrtxt = substring (cnstrtxt, charindex (@LFas, upper (cnstrtxt)) + 4, 60)
   where charindex (@LFas, upper (cnstrtxt)) > 0

   update #dr45
   set cnstrtxt = substring (cnstrtxt, charindex (@LFasLF, upper (cnstrtxt)) + 4, 60)
   where charindex (@LFasLF, upper (cnstrtxt)) > 0

   update #dr45
   set cnstrtxt = stuff (cnstrtxt, charindex (@CR, cnstrtxt), 1, ' ')
   where charindex (@CR, cnstrtxt) > 0

   update #dr45
   set cnstrtxt = stuff (cnstrtxt, charindex (@LF, cnstrtxt), 1, ' ')
   where charindex (@LF, cnstrtxt) > 0

   insert into #dr45                                  -- checks
      select o.id,
             o.uid,
             o.name + '.' + c.name,
             c.colid,
             c.domain,
             cm.text,
             'check',
             t.usertype,
             t.name,
             t.domain,
             ' ',
             ' '
      from syscolumns c,
           syscomments cm,
           sysconstraints cs,
           sysobjects o,
           systypes t
      where t.usertype        > 99
        and t.domain         <> 0
        and c.usertype        = t.usertype
        and c.domain         <> t.domain
        and o.id              = c.id
        and (o.sysstat & 15)  = 3
        and cs.tableid        = c.id
        and cs.colid          = c.colid
        and (cs.status & 128) = 128
        and cm.id             = cs.constrid
        and cm.colid          = 1
        and cm.colid2         = 0
        and cm.texttype       = 0

   update #dr45
   set cnstrtxt = ltrim (substring (cnstrtxt, charindex ('(', cnstrtxt) + 1, 60))
   where cnstrtype = 'check'

   update #dr45
   set cnstrtxt = substring (cnstrtxt, 1, char_length (cnstrtxt) - 1)
   where cnstrtype     = 'check'
     and cnstrtxt like '%)'

   update #dr45
   set cnstrtxt = substring (cnstrtxt, 1, char_length (cnstrtxt) - 2)
   where cnstrtype     = 'check'
     and cnstrtxt like '%),'

   if (exists (select *
               from #dr45))
      begin
         if (1 < (select count (*)
                  from #dr45))
            begin
               print '    USERTYPE RULES HAVE BEEN OVERRIDEN OR APPENDED TO.'
            end
         else
            begin
               print '    A USERTYPE RULE HAS BEEN OVERRIDEN OR APPENDED TO.'
            end

         print ' '

         exec sp_domainsright_col_ut_rules
      end
   else
      begin
         print '    NO USERTYPE RULE HAS BEEN OVERRIDEN.'
      end

   print ' '
go
sp_addcomment sp_domainsright, 11, 'Checks for consistency of column constraints/defaults'
go
sp_procxmode sp_domainsright, anymode
go

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

         print ' '

         return 1
      end

   if (1 < any (select count (uid)
                from sysobjects
                group by name, (sysstat & 15)))
      begin
         select OWNER = substring (isnull (user_name (o.uid), '?'), 1, 16),
                NAME  = o.name,
                TYPE  = substring (vO.name, 1, 23)
         from sysobjects o,
              sybsystemprocs.dbo.usptvalues vO
         where 1 < (select count (o2.uid)
                    from sysobjects o2
                    where o2.name           = o.name
                      and (o2.sysstat & 15) = (o.sysstat & 15))
           and vO.number = convert (int, o.sysstat & 15)
           and vO.type   = 'O'
         group by o.name
         having 1 < (select count (o2.uid)
                     from sysobjects o2
                     where o2.name           = o.name
                       and (o2.sysstat & 15) = (o.sysstat & 15))
            and vO.number = convert (int, o.sysstat & 15)
            and vO.type   = 'O'
         order by o.name, user_name (o.uid), vO.name
      end
   else
      begin
         print '    ALL SYSOBJECT NAMES ARE UNIQUE FOR EACH OBJECT TYPE.'

         print ' '
      end
go
sp_addcomment sp_objectsunique, 11, 'Checks for existence of duplicate names (by type) in sysobjects'
go
sp_procxmode sp_objectsunique, anymode
go

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

         print ' '

         return 1
      end

   if (exists (select u.*
               from sysprotects p,
                    sysusers    u
               where p.uid = u.uid
                 and u.uid > 2                    -- ignore guest/dbo/public
                 and u.uid < 16384))
      begin
         print '    THERE ARE PERMISSIONS FOR INDIVIDUAL USERS INSTEAD OF GROUPS.'

         print ' '

            select 'USER'                    = space (13),
                   'ACCESS (* = WITH GRANT)' = space (23),
                   TYPE                      = space (3),
                   OBJECT                    = space (23),
                   'FROM'                    = space (10)
            where 1 = 2
         union
            select substring (u.name, 1, 13),
                   substring (vTP.name + ' ' + vTA.name, 1, 23),
                   isnull (convert (char (3), o.type), '-'),
                   substring (isnull (o.name, '-'), 1, 23),
                   substring (isnull (user_name (p.grantor), '?'), 1, 10)
            from sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects  o,
                 sysprotects p,
                 sysusers    u
            where p.grantor  = o.uid
              and p.id       = o.id
              and u.uid      = p.uid
              and u.uid      > 2
              and u.uid      < 16384
              and vTA.number = p.action
              and vTA.type   = 'TA'
              and vTP.number = p.protecttype
              and vTP.type   = 'TP'
         union
            select substring (u.name, 1, 13),
                   substring (vTP.name + ' ' + vTA.name, 1, 23),
                   isnull (convert (char (3), o.type), '-'),
                   substring (o.name + ' (' + user_name (o.uid) + ')', 1, 23),
                   substring (isnull (user_name (p.grantor), '?'), 1, 10)
            from sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects  o,
                 sysprotects p,
                 sysusers    u
            where p.grantor  <> o.uid
              and p.id        = o.id
              and u.uid       = p.uid
              and u.uid       > 2
              and u.uid       < 16384
              and vTA.number  = p.action
              and vTA.type    = 'TA'
              and vTP.number  = p.protecttype
              and vTP.type    = 'TP'
         order by 'USER', 'ACCESS (* = WITH GRANT)', OBJECT, TYPE
      end
   else
      begin
         print '    ALL NON-GUEST PERMISSIONS ARE BY GROUPS, NOT INDIVIDUAL USERS.'

         print ' '
      end
go
sp_addcomment sp_protectsright, 11, 'Checks for user permissions (instead of group permissions)'
go
sp_procxmode sp_protectsright, anymode
go

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

         print ' '

         return 1
      end

   declare @maxkeycnt  tinyint,
           @seq        tinyint

   create table #refkeysright
     (constrid   int           not null,
      tableid    int           not null,            -- Also used for final sort
      reftabid   int           not null,
      keycnt     tinyint       not null,
      pmrydbid   smallint      not null,
      foseq      tinyint       not null,            -- Also used for text sequence
      fokey      tinyint       not null,
      nullflag   tinyint       not null,
      fktext     varchar (255) null,
      pktext     varchar (30)  null)

   if (exists (select *
               from sysreferences))
      begin
         if (exists (select *
                     from sysreferences
                     where (frgndbid <> db_id ()  and  pmrydbid <> db_id ())
                        or col_name (tableid, fokey1, frgndbid)   IS NULL
                        or col_name (reftabid, refkey1, pmrydbid) IS NULL))
            begin
               print '    THERE ARE INVALID REFERENTIAL KEYS.'

               print ' '

                  select 'REFERENCE NAME'     = space (15),
                         'FOREIGN KEY TABLE'  = space (30),
                         'PRIMARY KEY TABLE'  = space (30)
                  where 1 = 2
               union
                  select substring (isnull (object_name (constrid), '?'),
                                    1,
                                    15),
                         isnull (db_name (frgndbid), 'unknown database'),
                         isnull (db_name (pmrydbid), 'unknown database')
                  from sysreferences
                  where frgndbid <> db_id ()
                    and pmrydbid <> db_id ()
               union
                  select substring (isnull (object_name (r.constrid), '?'),
                                    1,
                                    15),
                         substring (isnull (db_name (r.frgndbid), '?')
                                    + '.'
                                    + isnull (user_name (o.uid), '?')
                                    + '.'
                                    + isnull (o.name, '?'),
                                    1,
                                    30),
                         substring (isnull (db_name (r.pmrydbid), '?')
                                    + '/'
                                    + isnull (object_name (r.reftabid, r.pmrydbid), '?'),
                                    1,
                                    30)
                  from sysobjects    o,
                       sysreferences r
                  where r.frgndbid  = db_id ()
                    and r.pmrydbid <> db_id ()
                    and ((col_name (r.tableid, r.fokey1, r.frgndbid)   IS NULL)  or
                         (col_name (r.reftabid, r.refkey1, r.pmrydbid) IS NULL))
                    and r.tableid  *= o.id
               union
                  select substring (isnull (object_name (r.constrid), '?'),
                                    1,
                                    15),
                         substring (isnull (db_name (r.frgndbid), '?')
                                    + '/'
                                    + isnull (object_name (r.tableid, r.frgndbid), '?'),
                                    1,
                                    30),
                         substring (isnull (db_name (r.pmrydbid), '?')
                                    + '.'
                                    + isnull (user_name (o.uid), '?')
                                    + '.'
                                    + isnull (o.name, '?'),
                                    1,
                                    30)
                  from sysobjects    o,
                       sysreferences r
                  where r.frgndbid <> db_id ()
                    and r.pmrydbid  = db_id ()
                    and ((col_name (r.tableid, r.fokey1, r.frgndbid)   IS NULL)  or
                         (col_name (r.reftabid, r.refkey1, r.pmrydbid) IS NULL))
                    and r.reftabid *= o.id
               union
                  select substring (isnull (object_name (r.constrid), '?'),
                                    1,
                                    15),
                         substring (isnull (db_name (r.frgndbid), '?')
                                    + '.'
                                    + isnull (user_name (fo.uid), '?')
                                    + '.'
                                    + isnull (object_name (r.tableid, r.frgndbid), '?'),
                                    1,
                                    30),
                         substring (isnull (db_name (r.pmrydbid), '?')
                                    + '.'
                                    + isnull (user_name (ro.uid), '?')
                                    + '.'
                                    + isnull (object_name (r.reftabid, r.pmrydbid), '?'),
                                    1,
                                    30)
                  from sysobjects    fo,
                       sysobjects    ro,
                       sysreferences r
                  where r.frgndbid  = db_id ()
                    and r.pmrydbid  = db_id ()
                    and ((col_name (r.tableid, r.fokey1, r.frgndbid)   IS NULL)  or
                         (col_name (r.reftabid, r.refkey1, r.pmrydbid) IS NULL))
                    and r.tableid  *= fo.id
                    and r.reftabid *= ro.id
               order by 'REFERENCE NAME', 'FOREIGN KEY TABLE'
            end
         else
            begin
               print '    THERE ARE NO INVALID REFERENTIAL KEYS.'
            end

         print ' '

         if (exists (select *
                     from sysreferences
                     where db_id (pmrydbname) <> pmrydbid
                        or db_id (frgndbname) <> frgndbid))
            begin
               print '    THERE ARE INCONSISTENT REFERENTIAL DATABASE NAME/ID''S.'

               print ' '

                  select 'REFERENCE NAME' = space (15),
                         'REFERENCING DB' = space (30),
                         'REFERENCED DB'  = space (30)
                  where 1 = 2
               union
                  select substring (isnull (object_name (constrid), '?'),
                                    1,
                                    15),
                         substring (isnull (db_name (frgndbid), '?')
                                    + ' / '
                                    + frgndbname,
                                    1,
                                    30),
                         substring (isnull (db_name (pmrydbid), '?')
                                    + ' / '
                                    + pmrydbname,
                                    1,
                                    30)
                  from sysreferences
                  where db_id (frgndbname) <> frgndbid
                     or db_id (pmrydbname) <> pmrydbid
               order by 'REFERENCE NAME', 'REFERENCING DB'
            end
         else
            begin
               print '    REFERENTIAL DATABASE NAMES/IDS ARE CONSISTENT.'
            end

         print ' '

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 1, fokey1, 0, NULL, NULL
            from sysreferences

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 2, fokey2, 0, NULL, NULL
            from sysreferences
            where fokey2 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 3, fokey3, 0, NULL, NULL
            from sysreferences
            where fokey3 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 4, fokey4, 0, NULL, NULL
            from sysreferences
            where fokey4 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 5, fokey5, 0, NULL, NULL
            from sysreferences
            where fokey5 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 6, fokey6, 0, NULL, NULL
            from sysreferences
            where fokey6 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 7, fokey7, 0, NULL, NULL
            from sysreferences
            where fokey7 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 8, fokey8, 0, NULL, NULL
            from sysreferences
            where fokey8 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 9, fokey9, 0, NULL, NULL
            from sysreferences
            where fokey9 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 10, fokey10, 0, NULL, NULL
            from sysreferences
            where fokey10 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 11, fokey11, 0, NULL, NULL
            from sysreferences
            where fokey11 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 12, fokey12, 0, NULL, NULL
            from sysreferences
            where fokey12 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 13, fokey13, 0, NULL, NULL
            from sysreferences
            where fokey13 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 14, fokey14, 0, NULL, NULL
            from sysreferences
            where fokey14 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 15, fokey15, 0, NULL, NULL
            from sysreferences
            where fokey15 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 16, fokey16, 0, NULL, NULL
            from sysreferences
            where fokey16 <> 0

         update #refkeysright
         set nullflag = 1
         from syscolumns c
         where tableid        = c.id
           and fokey          = c.colid
           and (c.status & 8) = 8

         delete from #refkeysright
         where keycnt = 1

         delete from #refkeysright
         where constrid not in (select distinct c2.constrid
                                from #refkeysright c2
                                where c2.nullflag = 1)

         if (exists (select *
                     from #refkeysright))
            begin
               print '    THERE ARE NULLABLE [FOREIGN SUBKEYS].'

               print ' '

               select @maxkeycnt = max (keycnt),
                      @seq       = 2
               from #refkeysright

               update #refkeysright
               set fktext = object_name (tableid)
               where foseq = 1

               update #refkeysright
               set fktext = substring (user_name (o.uid) + '.' + fktext, 1, 30)
               from sysobjects o
               where foseq   = 1
                 and tableid = o.id
                 and (o.uid <> 1  or
                      1      < (select count (o2.uid)
                                from sysobjects o2
                                where o2.name           = o.name
                                  and (o2.sysstat & 15) = 3))

               update #refkeysright
               set fktext = fktext
                            + ' ('
                            +  ltrim (char (32 + (59 * (nullflag))))
                            +  col_name (tableid, fokey)
                            +  ltrim (char (32 + (61 * (nullflag))))
                            +  ', '
               where foseq = 1

               -- Note that if the pseudonymn order is reversed in the following
               -- from clause then the results will change.

               while (@seq <= @maxkeycnt)
                  begin
                     update #refkeysright
                     set r1.fktext = r1.fktext
                                     + ' '
                                     +  ltrim (char (32 + (59 * (r2.nullflag))))
                                     +  col_name (r2.tableid, r2.fokey)
                                     +  ltrim (char (32 + (61 * (r2.nullflag))))
                                     +  ', '
                     from #refkeysright r1,
                          #refkeysright r2
                     where r1.constrid = r2.constrid
                       and r1.foseq    = 1
                       and r2.foseq    = @seq

                     select @seq = @seq + 1
                  end

               delete from #refkeysright
               where foseq <> 1

               update #refkeysright
               set fktext = substring (fktext, 1, char_length (fktext) - 1)   +  ')'
               where char_length (fktext) < 255

               update #refkeysright
               set pktext = object_name (reftabid)

               update #refkeysright
               set pktext = substring (user_name (o.uid) + '.' + pktext, 1, 30)
               from sysobjects o
               where reftabid = o.id
                 and (o.uid <> 1  or
                      1      < (select count (o2.uid)
                                from sysobjects o2
                                where o2.name           = o.name
                                  and (o2.sysstat & 15) = 3))

               update #refkeysright
               set tableid = constrid

               insert into #refkeysright
                  select 0, tableid, reftabid, keycnt, pmrydbid, 2, 0, 0, '   ' + ltrim (substring (fktext, 45, 255)), ' '
                  from #refkeysright
                  where char_length (fktext) > 44

               insert into #refkeysright
                  select 0, tableid, reftabid, keycnt, pmrydbid, 3, 0, 0, '   ' + ltrim (substring (fktext, 45, 255)), ' '
                  from #refkeysright
                  where foseq                = 2
                    and char_length (fktext) > 44

               select 'REFERENCE NAME'    = isnull (substring (object_name (constrid), 1, 14), ' '),
                      'FOREIGN KEY'       = substring (fktext, 1, 44),
                      'PRIMARY KEY TABLE' = substring (pktext, 1, 17)
               from #refkeysright
               order by object_name (tableid), foseq
            end
         else
            begin
               print '    THERE ARE NO NULLABLE FOREIGN SUBKEYS.'

               print ' '
            end
      end
   else
      begin
         print '    THERE ARE NO REFERENTIAL KEYS.'

         print ' '
      end
go
sp_addcomment sp_refkeysright, 11, 'Checks for invalid referential keys'
go
sp_procxmode sp_refkeysright, anymode
go

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

         print ' '

         return 1
      end

   if ((exists (select o.*
                from sysdepends d,
                     sysobjects o
                where o.id                   = d.id
                  and object_name (d.depid) IS NULL))   or
       (exists (select o.*
                from sysdepends d,
                     sysdepends d2,
                     sysobjects o
                where o.id                    = d.id
                  and d.depid                 = d2.id
                  and object_name (d2.depid) IS NULL))  or
       (exists (select o.*
                from sysdepends d,
                     sysobjects o,
                     sysobjects o2
                where o.id     = d.id
                  and d.depid  = o2.id
                  and o.crdate < o2.crdate))            or
       (exists (select o.*
                from sysdepends d,
                     sysdepends d2,
                     sysobjects o,
                     sysobjects o2
                where o.id     = d.id
                  and d.depid  = d2.id
                  and o2.id    = d2.id
                  and o.crdate < o2.crdate)))
      begin
         create table #usesright
            (objid     int               not null,
             objuid    smallint          not null,
             objname   varchar (30)      not null,
             objdate   datetime          null,
             basename  varchar (30)      not null,
             basedate  datetime          null,
             sort      varchar (61)      null)

         insert into #usesright
            select o.id, o.uid, o.name, o.crdate, convert (char (20), d.depid), NULL, NULL
            from sysdepends d,
                 sysobjects o
            where o.id      = d.id
              and object_name (d.depid) IS NULL

         insert into #usesright
            select o.id, o.uid, o.name, o.crdate, convert (varchar (20), d2.depid) + ' ##', NULL, NULL
            from sysdepends d,
                 sysdepends d2,
                 sysobjects o
            where o.id      = d.id
              and d.depid   = d2.id
              and object_name (d2.depid) IS NULL

         insert into #usesright
            select o.id, o.uid, o.name, o.crdate, o2.name, o2.crdate, NULL
            from sysdepends d,
                 sysobjects o,
                 sysobjects o2
            where o.id      = d.id
              and o2.id     = d.depid
              and o.crdate  < o2.crdate

         insert into #usesright
            select o.id, o.uid, o.name, o.crdate, o2.name, o2.crdate, NULL
            from sysdepends d,
                 sysdepends d2,
                 sysobjects o,
                 sysobjects o2
            where o.id      = d.id
              and d.depid   = d2.id
              and o2.id     = d.id
              and o.crdate  < o2.crdate

         update #usesright
         set objname = substring (u.objname + '(' + user_name (u.objuid) + ')', 1, 30)
         from #usesright u
         where u.objuid <> 1
            or 1         < (select count (distinct o.uid)
                            from sysobjects o
                            where o.name = u.objname)

         update #usesright
         set sort = substring (objname + ' ' + basename, 1, 61)

         update #usesright
         set objname = ' ',
             objdate = NULL
         from #usesright ur
         where exists (select *
                       from #usesright ur2
                       where ur2.objname  = ur.objname
                         and ur2.basename = ur.basename
                         and ur2.basename > (select min (ur3.basename)
                                             from #usesright ur3
                                             where ur3.objname = ur2.objname))

         select 'OBJECT'       = substring (objname, 1, 25),
                'OBJECT DATE'  = isnull (convert (char (8), objdate, 11), ' ') + ' ' + convert (char (5), objdate, 8),
                'BASE OBJECT?' = substring (basename, 1, 21),
                'BASE DATE'    = isnull (convert (char (8), basedate, 11), '?') + ' ' + convert (char (5), basedate, 8)
         from #usesright
         order by sort
      end
   else
      begin
         print '    ALL OBJECTS ARE CONSISTENT WITH THEIR BASE OBJECTS.'
      end
go
sp_addcomment sp_usesright, 11, 'Checks for objects with invalid/newer base objects'
go
sp_procxmode sp_usesright, anymode
go

dump transaction sybsystemprocs with truncate_only
go
