use sybsystemprocs
go

sp_dropprocedure sp_nullcolumns
go
sp_dropprocedure sp_refkeys
go
sp_dropprocedure sp_refkeysright
go
sp_dropprocedure sp_refkeysx
go

set quoted_identifier on
go

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

         print ' '

         return 1
      end

   declare @cmdstring  varchar (255),
           @collen     tinyint,
           @tabcount   smallint,
           @tablen     tinyint,
           @typelen    tinyint

   create table #nullcolumns
      (tabname   varchar (62)  not null,
       tabid     int           not null,
       tabuid    smallint      not null,
       colname   varchar (30)  not null,
       colid     tinyint       not null,
       typetext  varchar (30)  not null,
       sort      varchar (62)  not null)

   insert into #nullcolumns
      select o.name, o.id, o.uid, c.name, c.colid, t.name, ' '
      from syscolumns c,
           sysobjects o,
           systypes t
      where t.usertype       > 99
        and t.usertype       = c.usertype
        and c.id             = o.id
        and (c.status & 8)   = 8
        and (o.sysstat & 15) = 3

   insert into #nullcolumns
      select o.name, o.id, o.uid, c.name, c.colid, t.name, ' '
      from syscolumns c,
           sysobjects o,
           systypes t
      where t.usertype       in (11, 12, 16, 18, 19, 20, 21, 22, 80)
        and t.usertype        = c.usertype
        and (c.status & 8)    = 8
        and c.id              = o.id
        and (o.sysstat & 15)  = 3

   insert into #nullcolumns
      select o.name, o.id, o.uid, c.name, c.colid,
             substring (t.name  +  ' ('
                        +  convert (varchar (3), c.length)  + ')',
                        1,
                        30),
             ' '
      from syscolumns c,
           sysobjects o,
           systypes t
      where c.usertype       in (1, 2, 3, 4)
        and (c.status & 8)    = 8
        and c.usertype        = t.usertype
        and c.id              = o.id
        and (o.sysstat & 15)  = 3

   insert into #nullcolumns
      select o.name, o.id, o.uid, c.name, c.colid,
             substring (t.name  +  ' ('
                        +  convert (varchar (3), c.length / @@ncharsize)  + ')',
                        1,
                        30),
             ' '
      from syscolumns c,
           sysobjects o,
           systypes t
      where c.usertype       in (24, 25)
        and c.usertype        = t.usertype
        and (c.status & 8)    = 8
        and c.id              = o.id
        and (o.sysstat & 15)  = 3

   insert into #nullcolumns
      select o.name, o.id, o.uid, c.name, c.colid,
             substring ('decimal ('
                        +  convert (varchar (3), c.prec) + ', '
                        +  convert (varchar (3), c.scale) + ')',
                        1,
                        30),
             ' '
      from syscolumns c,
           sysobjects o
      where c.usertype       in (26, 27)
        and (c.status & 8)    = 8
        and c.id              = o.id
        and (o.sysstat & 15)  = 3

   insert into #nullcolumns
      select o.name, o.id, o.uid, c.name, c.colid,
             substring ('numeric ('
                        +  convert (varchar (3), c.prec) + ', '
                        +  convert (varchar (3), c.scale) + ')',
                        1,
                        30),
             ' '
      from syscolumns c,
           sysobjects o
      where c.usertype       in (10, 28)
        and (c.status & 8)    = 8
        and c.id              = o.id
        and (o.sysstat & 15)  = 3

   insert into #nullcolumns
      select o.name, o.id, o.uid, c.name, c.colid,
             substring ('float ('
                        +  convert (varchar (3), c.prec)  + ')',
                        1,
                        30),
             ' '
      from syscolumns c,
           sysobjects o
      where c.usertype       in (8, 23)
        and c.prec           IS NOT NULL
        and (c.status & 8)    = 8
        and c.id              = o.id
        and (o.sysstat & 15)  = 3

   insert into #nullcolumns
      select o.name, o.id, o.uid, c.name, c.colid, 'real', ' '
      from syscolumns c,
           sysobjects o
      where ((c.usertype = 23)  or  ((c.usertype = 8) and (c.length = 4)))
        and c.prec           IS NULL
        and (c.status & 8)    = 8
        and c.id              = o.id
        and (o.sysstat & 15)  = 3

   insert into #nullcolumns
      select o.name, o.id, o.uid, c.name, c.colid, 'double precision', ' '
      from syscolumns c,
           sysobjects o
      where c.usertype        = 8
        and c.length          = 8
        and c.prec           IS NULL
        and (c.status & 8)    = 8
        and c.id              = o.id
        and (o.sysstat & 15)  = 3

   insert into #nullcolumns
      select o.name, o.id, o.uid, c.name, c.colid, 'tinyint', ' '
      from syscolumns c,
           sysobjects o
      where c.usertype       in (5, 13)
        and c.length          = 1
        and (c.status & 8)    = 8
        and c.id              = o.id
        and (o.sysstat & 15)  = 3

   insert into #nullcolumns
      select o.name, o.id, o.uid, c.name, c.colid, 'smallint', ' '
      from syscolumns c,
           sysobjects o
      where c.usertype       in (6, 13)
        and c.length          = 2
        and (c.status & 8)    = 8
        and c.id              = o.id
        and (o.sysstat & 15)  = 3

   insert into #nullcolumns
      select o.name, o.id, o.uid, c.name, c.colid, 'int', ' '
      from syscolumns c,
           sysobjects o
      where c.usertype       in (7, 13)
        and c.length          = 4
        and (c.status & 8)    = 8
        and c.id              = o.id
        and (o.sysstat & 15)  = 3

   execute  sp_exclude_object  @TYPE='EXT',
                               @TABLENAME='#nullcolumns',
                               @COLUMNNAME='tabname'

   if (exists (select *
               from #nullcolumns))
      begin
         update #nullcolumns
         set tabname = nw.tabname  +  ' ('  +  user_name (nw.tabuid)  +  ')'
         from #nullcolumns nw
         where nw.tabuid <> 1
            or 1          < (select count (o.uid)
                             from sysobjects o
                             where (o.sysstat & 15) = 3
                               and o.name           = nw.tabname)

         update #nullcolumns
         set sort = tabname

         update #nullcolumns
         set tabname = ' '
         from #nullcolumns n1
         where exists (select *
                       from #nullcolumns n2
                       where n2.tabid   = n1.tabid
                         and n2.colname = n1.colname
                         and n2.colname > (select min (n3.colname)
                                           from #nullcolumns n3
                                           where n3.tabid = n2.tabid))

         select @tabcount = count (*)
         from #nullcolumns
         where tabname <> ' '

         print '    THERE ARE %1! USER TABLES WITH NULLABLE COLUMNS.', @tabcount

         print ' '

         update #nullcolumns
         set typetext = typetext + ', FK column'
         from #nullcolumns nw, sysreferences sr
         where sr.frgndbname IS NULL
           and nw.tabid       = sr.tableid
           and nw.colid      in (sr.fokey1, sr.fokey2, sr.fokey3, sr.fokey4,
                                 sr.fokey5, sr.fokey6, sr.fokey7, sr.fokey8,
                                 sr.fokey9, sr.fokey10, sr.fokey11, sr.fokey12,
                                 sr.fokey13, sr.fokey14, sr.fokey15, sr.fokey16)

         select @tablen  = max (char_length (tabname)),
                @collen  = max (char_length (colname)),
                @typelen = max (char_length (typetext))
         from #nullcolumns

         if (@tablen < 5)
            begin
               select @tablen = 5
            end

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

         if (@typelen < 4)
            begin
               select @typelen = 4
            end

         if ((@tablen + @collen + @typelen) > 75)
            begin
               while ((@tablen + @collen + @typelen) > 75)
                  begin
                     if (@tablen > 5)
                        begin
                           select @tablen = @tablen - 1
                        end

                     if (@collen > 14)
                        begin
                           select @collen = @collen - 1
                        end
                  end

               select @collen = 75 - @tablen - @typelen
            end

         select @cmdstring = 'select ''TABLE'' = substring (tabname, 1,'
                             +  convert (varchar (2), @tablen)
                             +  '),'
                             +  'COLUMN  = substring (colname, 1,'
                             +  convert (varchar (2), @collen)
                             +  '),'
                             +  'TYPE = substring (typetext, 1,'
                             +  convert (varchar (2), @typelen)
                             +  ')'
                             +  ' from #nullcolumns'
                             +  ' order by sort, colname'
         execute  (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO NULLABLE COLUMNS ON USER TABLES.'

         print ' '
      end
go
sp_addcomment sp_nullcolumns, 'Displays all nullable columns with FK indication for user tables'
go
sp_procxmode sp_nullcolumns, anymode
go

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

         print ' '

         return 1
      end

   declare @continue     tinyint,
           @maxreflines  tinyint,
           @rkcount      tinyint,
           @sequence     tinyint,
           @tableid      int

   create table #refkeys
      (constrid   int            not null,
       tableid    int            not null,
       ftable     varchar (30)   null,
       fkey       varchar (255)  null,
       fkeysort   varchar (255)  null,
       reftabid   int            not null,
       rtable     varchar (30)   null,
       pkey       varchar (255)  null,
       seq        tinyint        not null)

   select @continue    = 0,
          @maxreflines = 4,
          @sequence    = 1

   if (@TABLE IS NULL)
      begin
         if (exists (select *
                     from sysreferences
                     where ((frgndbname IS NOT NULL)  or  (pmrydbname IS NOT NULL))))
            begin
               print '    THERE ARE EXTERNAL REFERENTIAL KEYS (USE sp_refkeysx).'

               print ' '
            end

         if (exists (select *
                     from sysreferences
                     where ((frgndbname IS NULL)  and  (pmrydbname IS NULL))))
            begin
               select @continue = 1
            end
         else
            begin
               print '    THERE ARE NO INTERNAL REFERENTIAL KEYS.'

               print ' '
            end
      end
   else
      begin
         select @tableid = id
         from sysobjects
         where id              = object_id (@TABLE)
           and (sysstat & 15) in (1, 3)

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

               print ' '
            end
         else
            begin
               if (exists (select *
                           from sysreferences
                           where ((tableid = @tableid)  or  (reftabid = @tableid))
                             and ((frgndbname IS NOT NULL)  or  (pmrydbname IS NOT NULL))))
                  begin
                     print '    THERE ARE EXTERNAL REFERENTIAL KEYS FOR %1! (USE sp_refkeysx).',
                           @TABLE

                     print ' '
                  end

               if (exists (select *
                           from sysreferences
                           where ((tableid = @tableid)  or  (reftabid = @tableid))
                             and frgndbname IS NULL
                             and pmrydbname IS NULL))
                  begin
                     select @continue = 1
                  end
               else
                  begin
                     print '    THERE ARE NO INTERNAL REFERENTIAL KEYS FOR %1!.',
                           @TABLE

                     print ' '
                  end
            end
      end

   if (@continue = 1)
      begin
         insert into #refkeys
            select constrid,
                   tableid,
                   object_name (tableid),
                   col_name (tableid, fokey1)
                   +  ', '  +  col_name (tableid, fokey2)
                   +  ', '  +  col_name (tableid, fokey3)
                   +  ', '  +  col_name (tableid, fokey4)
                   +  ', '  +  col_name (tableid, fokey5)
                   +  ', '  +  col_name (tableid, fokey6)
                   +  ', '  +  col_name (tableid, fokey7)
                   +  ', '  +  col_name (tableid, fokey8)
                   +  ', '  +  col_name (tableid, fokey9)
                   +  ', '  +  col_name (tableid, fokey10)
                   +  ', '  +  col_name (tableid, fokey11)
                   +  ', '  +  col_name (tableid, fokey12)
                   +  ', '  +  col_name (tableid, fokey13)
                   +  ', '  +  col_name (tableid, fokey14)
                   +  ', '  +  col_name (tableid, fokey15)
                   +  ', '  +  col_name (tableid, fokey16),
                   object_name (constrid),
                   reftabid,
                   object_name (reftabid),
                   col_name (reftabid, refkey1)
                   +  ', '  +  col_name (reftabid, refkey2)
                   +  ', '  +  col_name (reftabid, refkey3)
                   +  ', '  +  col_name (reftabid, refkey4)
                   +  ', '  +  col_name (reftabid, refkey5)
                   +  ', '  +  col_name (reftabid, refkey6)
                   +  ', '  +  col_name (reftabid, refkey7)
                   +  ', '  +  col_name (reftabid, refkey8)
                   +  ', '  +  col_name (reftabid, refkey9)
                   +  ', '  +  col_name (reftabid, refkey10)
                   +  ', '  +  col_name (reftabid, refkey11)
                   +  ', '  +  col_name (reftabid, refkey12)
                   +  ', '  +  col_name (reftabid, refkey13)
                   +  ', '  +  col_name (reftabid, refkey14)
                   +  ', '  +  col_name (reftabid, refkey15)
                   +  ', '  +  col_name (reftabid, refkey16),
                   1
            from sysreferences
            where frgndbname IS NULL
              and pmrydbname IS NULL
              and (@TABLE IS NULL  or  @tableid = tableid  or  @tableid  = reftabid)

         if (1 < any (select count (tableid)
                      from #refkeys
                      group by ftable, fkey, rtable, pkey))
            begin
               print '    THERE ARE DUPLICATE FOREIGN KEY CONSTRAINTS.'

               print ' '
            end

         update #refkeys
         set fkeysort = substring (fkey  +  ' '  +  fkeysort, 1, 255)

         while (@sequence < 16)
            begin
               update #refkeys
               set fkey = rtrim (substring (fkey, 1, char_length (fkey) - 1))
               where right (fkey, 1) = ','

               update #refkeys
               set pkey = rtrim (substring (pkey, 1, char_length (pkey) - 1))
               where right (pkey, 1) = ','

               select @sequence = @sequence + 1
            end

         if (@TABLE IS NULL)
            begin
               select @rkcount = count (*)
               from #refkeys

               print '    THERE ARE %1! INTERNAL REFERENTIAL CONSTRAINTS.', @rkcount

               print ' '
            end

         -- ADD OWNER IF NOT OWNED BY DBO OR TABLE NAME IS NON-UNIQUE

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

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

         -- SIMPLE TEXT MASSAGE WHICH SIMPLIFIES CODE IN EARLIER VERSIONS

         update #refkeys
         set fkey = ftable  +  ' ('  +  fkey  +  ')',
             pkey = rtable  +  ' ('  +  pkey  +  ')'

         select @sequence = 1

         while (@sequence < @maxreflines)
            begin
               -- SPLIT LONG TEXT (WITH COMMAS) ONTO A NEW LINE

               insert into #refkeys
                  select constrid,
                         tableid,
                         ftable,
                         '    '
                         +  ltrim (substring (fkey,
                                              40 - charindex (',',
                                                              reverse (substring (fkey,
                                                                                  1,
                                                                                  38))),
                                              255)),
                         fkeysort,
                         reftabid,
                         rtable,
                         '    '
                         +  ltrim (substring (pkey,
                                              40 - charindex (',',
                                                              reverse (substring (pkey,
                                                                                  1,
                                                                                  38))),
                                              255)),
                         @sequence + 1
                  from #refkeys
                  where seq                   = @sequence
                    and char_length (fkey)    > 38
                    and charindex (',', fkey) > 0
                    and char_length (pkey)    > 38
                    and charindex (',', pkey) > 0

               insert into #refkeys
                  select constrid,
                         tableid,
                         ftable,
                         '    '
                         +  ltrim (substring (fkey,
                                              40 - charindex (',',
                                                              reverse (substring (fkey,
                                                                                  1,
                                                                                  38))),
                                              255)),
                         fkeysort,
                         reftabid,
                         rtable,
                         ' ',
                         @sequence + 1
                  from #refkeys
                  where seq                   = @sequence
                    and char_length (fkey)    > 38
                    and charindex (',', fkey) > 0
                    and char_length (pkey)    < 39

               insert into #refkeys
                  select constrid,
                         tableid,
                         ftable,
                         ' ',
                         fkeysort,
                         reftabid,
                         rtable,
                         '    '
                         +  ltrim (substring (pkey,
                                              40 - charindex (',',
                                                              reverse (substring (pkey,
                                                                                  1,
                                                                                  38))),
                                              255)),
                         @sequence + 1
                  from #refkeys
                  where seq                   = @sequence
                    and char_length (fkey)    < 39
                    and char_length (pkey)    > 38
                    and charindex (',', pkey) > 0

               -- MASSAGE THE ORIGINAL SPLIT LINE AT COMMAS

               update #refkeys
               set fkey = substring (rk.fkey,
                                     1,
                                     39 - charindex (',',
                                                     reverse (substring (rk.fkey,
                                                                         1,
                                                                         38))))
               from #refkeys rk,
                    #refkeys rk2
               where rk.seq        = @sequence
                 and rk2.constrid  = rk.constrid
                 and rk2.seq       = @sequence + 1
                 and rk2.fkey     <> ' '

               update #refkeys
               set pkey = substring (rk.pkey,
                                     1,
                                     39 - charindex (',',
                                                     reverse (substring (rk.pkey,
                                                                         1,
                                                                         38))))
               from #refkeys rk,
                    #refkeys rk2
               where rk.seq        = @sequence
                 and rk2.constrid  = rk.constrid
                 and rk2.seq       = @sequence + 1
                 and rk2.pkey     <> ' '

               -- SPLIT OUT LONG LINES THAT HAVE NO COMMAS

               insert into #refkeys
                  select constrid,
                         tableid,
                         ftable,
                         '    '  +  substring (fkey, charindex ('(', fkey), 38),
                         fkeysort,
                         reftabid,
                         rtable,
                         '    '  +  substring (pkey, charindex ('(', pkey), 38),
                         @sequence + 1
                  from #refkeys
                  where seq                   = @sequence
                    and char_length (fkey)    > 38
                    and charindex (',', fkey) = 0
                    and char_length (pkey)    > 38
                    and charindex (',', pkey) = 0

               insert into #refkeys
                  select constrid,
                         tableid,
                         ftable,
                         '    '  +  substring (fkey, charindex ('(', fkey), 38),
                         fkeysort,
                         reftabid,
                         rtable,
                         ' ',
                         @sequence + 1
                  from #refkeys
                  where seq                   = @sequence
                    and char_length (fkey)    > 38
                    and charindex (',', fkey) = 0
                    and char_length (pkey)    < 39

               insert into #refkeys
                  select constrid,
                         tableid,
                         ftable,
                         ' ',
                         fkeysort,
                         reftabid,
                         rtable,
                         '    '  +  substring (pkey, charindex ('(', pkey), 38),
                         @sequence + 1
                  from #refkeys
                  where seq                   = @sequence
                    and char_length (fkey)    < 39
                    and char_length (pkey)    > 38
                    and charindex (',', pkey) = 0

               select @sequence = @sequence + 1
            end

         -- MASSAGE THE LONG ORIGINAL LINES THAT HAD NO COMMAS

         update #refkeys
         set fkey = substring (fkey, 1, charindex ('(', fkey) - 1)
         where seq                   = 1
           and char_length (fkey)    > 38
           and charindex (',', fkey) = 0

         update #refkeys
         set pkey = substring (pkey, 1, charindex ('(', pkey) - 1)
         where seq                   = 1
           and char_length (pkey)    > 38
           and charindex (',', pkey) = 0

         select 'FOREIGN KEY' = substring (fkey, 1, 38),
                'PRIMARY KEY' = substring (pkey, 1, 38)
         from #refkeys
         order by ftable, fkeysort, rtable, seq
      end
go
sp_addcomment sp_refkeys, 'Lists internal referential keys for TABLE or (default) all tables'
go
sp_procxmode sp_refkeys, 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 CONSTRAINTS.'

               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 sysreferences r  left join  sysobjects o
                    on r.tableid = o.id
                  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))
               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 sysreferences r  left join  sysobjects o
                    on r.tableid = o.id
                  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))
               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 sysreferences r  left join  sysobjects fo
                    on r.tableid = fo.id
                  left join sysobjects ro
                    on r.reftabid = ro.id
                  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))
               order by 'REFERENCE NAME', 'FOREIGN KEY TABLE'
            end
         else
            begin
               print '    THERE ARE NO INVALID REFERENTIAL CONSTRAINTS.'
            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.'
            end

         print ' '

         truncate table #refkeysright

         insert into #refkeysright
            select r.constrid, r.tableid, r.reftabid, 0, r.pmrydbid, 0, 0, 0,
                   object_name (r.tableid), NULL
            from sysreferences r
            where r.frgndbname IS NULL
              and exists (select r2.constrid
                          from sysreferences r2
                          where r2.frgndbname IS NULL
                            and r2.tableid     = r.tableid
                            and r2.constrid   <> r.constrid
                            and ((r2.fokey1 = r.fokey1)  or
                                 (r2.fokey1 = r.fokey2)  or
                                 (r2.fokey2 = r.fokey1)))

         if (exists (select *
                     from #refkeysright))
            begin
               print '    THERE ARE OVERLAPPING AND/OR DUPLICATE FOREIGN KEYS.'

               print ' '

               update #refkeysright
               set fktext = substring (user_name (o.uid) + '.' + fktext, 1, 30)
               from sysobjects o
               where 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 r1.fktext = substring (r1.fktext
                                          +  ' ('
                                          +  col_name (r2.tableid, r2.fokey1)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey2)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey3)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey4)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey5)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey6)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey7)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey8),
                                          1,
                                          255)
               from #refkeysright r1,
                    sysreferences r2
               where r1.constrid = r2.constrid

               update #refkeysright
               set fktext = substring (fktext, 1, charindex (', ,', fktext) - 1)
               where charindex (', ', fktext) > 0

               update #refkeysright
               set fktext = substring (fktext + ')', 1, 255)

               select 'REFERENCE NAME'    = isnull (substring (object_name (constrid), 1, 14), '?'),
                      'FOREIGN KEY'       = substring (fktext, 1, 44),
                      'PRIMARY KEY TABLE' = substring (object_name (reftabid, pmrydbid), 1, 17)
               from #refkeysright
               order by fktext, object_name (reftabid, pmrydbid)
            end
         else
            begin
               print '    THERE ARE NO OVERLAPPING OR DUPLICATE FOREIGN KEYS.'
            end
      end
   else
      begin
         print '    THERE ARE NO REFERENTIAL CONSTRAINTS.'
      end

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

create procedure sp_refkeysx (@TABLE varchar (63) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @continue     tinyint,
           @maxreflines  tinyint,
           @tableid      int,
           @sequence     tinyint

   create table #refkeysx
      (constrid  int            not null,
       fdbname   varchar (30)   not null,
       ftabid    int            not null,
       ftabname  varchar (75)   not null,
       fkey      varchar (255)  null,
       pdbname   varchar (30)   not null,
       ptabid    int            not null,
       ptabname  varchar (75)   not null,
       pkey      varchar (255)  null,
       seq       tinyint        not null)

   select @continue = 0,
          @sequence = 1

   if (@TABLE IS NULL)
      begin
         if (exists (select *
                     from sysreferences
                     where ((frgndbname IS NOT NULL)  or  (pmrydbname IS NOT NULL))))
            begin
               select @continue = 1
            end
         else
            begin
               print '    THERE ARE NO EXTERNAL REFERENTIAL KEYS.'

               print ' '
            end
      end
   else
      begin
         select @tableid = id
         from sysobjects
         where id              = object_id (@TABLE)
           and (sysstat & 15) in (1, 3)

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

               print ' '
            end
         else
            begin
               if (exists (select *
                           from sysreferences
                           where ((tableid = @tableid)  or  (reftabid = @tableid))
                             and ((frgndbname IS NOT NULL)  or  (pmrydbname IS NOT NULL))))
                  begin
                     select @continue = 1
                  end
               else
                  begin
                     print '    THERE ARE NO EXTERNAL REFERENTIAL KEYS FOR %1!.',
                           @TABLE

                     print ' '
                  end
            end
      end

   if (@continue = 1)
      begin
         insert into #refkeysx
            select constrid,
                   isnull (frgndbname, db_name ()),
                   tableid,
                   object_name (tableid, isnull (db_id (frgndbname), db_id ())),
                   substring (         col_name (tableid, fokey1, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey2, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey3, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey4, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey5, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey6, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey7, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey8, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey9, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey10, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey11, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey12, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey13, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey14, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey15, isnull (db_id (frgndbname), db_id ()))
                              + ', ' + col_name (tableid, fokey16, isnull (db_id (frgndbname), db_id ())),
                              1,
                              255),
                   isnull (pmrydbname, db_name ()),
                   reftabid,
                   object_name (reftabid, isnull (db_id (pmrydbname), db_id ())),
                   substring (         col_name (reftabid, refkey1, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey2, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey3, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey4, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey5, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey6, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey7, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey8, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey9, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey10, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey11, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey12, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey13, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey14, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey15, isnull (db_id (pmrydbname), db_id ()))
                              + ', ' + col_name (reftabid, refkey16, isnull (db_id (pmrydbname), db_id ())),
                              1,
                              255),
                   1
            from sysreferences
            where ((frgndbname IS NOT NULL)  or  (pmrydbname IS NOT NULL))

         if (@TABLE IS NOT NULL)
            begin
               delete from #refkeysx
               where ftabid <> @tableid
                 and ptabid <> @tableid
            end

         update #refkeysx
         set ftabname = substring (ftabname  +  ' '  +  fkey, 1, 75),
             ptabname = substring (ptabname  +  ' '  +  pkey, 1, 75)

         while (@sequence < 16)
            begin
               update #refkeysx
               set fkey = rtrim (substring (fkey, 1, char_length (fkey) - 1))
               where right (fkey, 1) = ','

               update #refkeysx
               set pkey = rtrim (substring (pkey, 1, char_length (pkey) - 1))
               where right (pkey, 1) = ','

               select @sequence = @sequence + 1
            end

         update #refkeysx
         set fkey = substring (fdbname
                               +  '/'
                               +  object_name (ftabid, db_id (fdbname))
                               +  ' ('
                               +  fkey
                               +  ')',
                               1,
                               255)
         where fdbname <> db_name ()

         update #refkeysx
         set fkey = substring (r.fdbname
                               +  '..'
                               +  object_name (r.ftabid)
                               +  ' ('
                               +  r.fkey
                               +  ')',
                               1,
                               255)
         from #refkeysx r,
              sysobjects o
         where r.fdbname = db_name ()
           and r.ftabid  = o.id
           and o.uid     = 1
           and 1         = (select count (o2.uid)
                            from sysobjects o2
                            where o2.id              = r.ftabid
                              and (o2.sysstat & 15) in (1, 2, 3))

         update #refkeysx
         set fkey = substring (fdbname
                               +  '.'
                               +  user_name (o.uid)
                               +  '.'
                               +  object_name (r.ftabid)
                               +  ' ('
                               +  r.fkey
                               +  ')',
                               1,
                               255)
         from #refkeysx r,
              sysobjects o
         where r.fdbname = db_name ()
           and r.ftabid  = o.id
           and (o.uid   <> 1  or
                1        < (select count (o2.uid)
                            from sysobjects o2
                            where o2.id              = r.ftabid
                              and (o2.sysstat & 15) in (1, 2, 3)))

         update #refkeysx
         set pkey = substring (pdbname
                               +  '/'
                               +  object_name (ptabid, db_id (pdbname))
                               +  ' ('
                               +  pkey
                               +  ')',
                               1,
                               255)
         where pdbname <> db_name ()

         update #refkeysx
         set pkey = substring (pdbname
                               +  '..'
                               +  object_name (r.ptabid)
                               +  ' ('
                               +  r.pkey
                               +  ')',
                               1,
                               255)
         from #refkeysx r,
              sysobjects o
         where pdbname   = db_name ()
           and r.ptabid  = o.id
           and o.uid     = 1
           and 1         = (select count (o2.uid)
                            from sysobjects o2
                            where o2.id              = r.ptabid
                              and (o2.sysstat & 15) in (1, 2, 3))

         update #refkeysx
         set pkey = substring (pdbname
                               +  '.'
                               +  user_name (o.uid)
                               +  '.'
                               +  object_name (r.ptabid)
                               +  ' ('
                               +  r.pkey
                               +  ')',
                               1,
                               255)
         from #refkeysx r,
              sysobjects o
         where r.pdbname  = db_name ()
           and r.ptabid   = o.id
           and (o.uid    <> 1  or
                1         < (select count (o2.uid)
                             from sysobjects o2
                             where o2.id              = r.ptabid
                               and (o2.sysstat & 15) in (1, 2, 3)))

         select @maxreflines = 4,
                @sequence    = 1

         while (@sequence < @maxreflines)
            begin
               -- SPLIT LONG TEXT (WITH COMMAS) ONTO A NEW LINE

               insert into #refkeysx
                  select constrid,
                         fdbname,
                         ftabid,
                         ftabname,
                         '    '
                         +  ltrim (substring (fkey,
                                              40 - charindex (',',
                                                              reverse (substring (fkey,
                                                                                  1,
                                                                                  38))),
                                              255)),
                         pdbname,
                         ptabid,
                         ptabname,
                         '    '
                         +  ltrim (substring (pkey,
                                              40 - charindex (',',
                                                              reverse (substring (pkey,
                                                                                  1,
                                                                                  38))),
                                              255)),
                         @sequence + 1
                  from #refkeysx
                  where seq                   = @sequence
                    and char_length (fkey)    > 38
                    and charindex (',', fkey) > 0
                    and char_length (pkey)    > 38
                    and charindex (',', pkey) > 0

               insert into #refkeysx
                  select constrid,
                         fdbname,
                         ftabid,
                         ftabname,
                         '    '
                         +  ltrim (substring (fkey,
                                              40 - charindex (',',
                                                              reverse (substring (fkey,
                                                                                  1,
                                                                                  38))),
                                              255)),
                         pdbname,
                         ptabid,
                         ptabname,
                         ' ',
                         @sequence + 1
                  from #refkeysx
                  where seq                   = @sequence
                    and char_length (fkey)    > 38
                    and charindex (',', fkey) > 0
                    and char_length (pkey)    < 39

               insert into #refkeysx
                  select constrid,
                         fdbname,
                         ftabid,
                         ftabname,
                         ' ',
                         pdbname,
                         ptabid,
                         ptabname,
                         '    '
                         +  ltrim (substring (pkey,
                                              40 - charindex (',',
                                                              reverse (substring (pkey,
                                                                                  1,
                                                                                  38))),
                                              255)),
                         @sequence + 1
                  from #refkeysx
                  where seq                   = @sequence
                    and char_length (fkey)    < 39
                    and char_length (pkey)    > 38
                    and charindex (',', pkey) > 0

               -- MASSAGE THE ORIGINAL SPLIT LINE AT COMMAS

               update #refkeysx
               set fkey = substring (rk.fkey,
                                     1,
                                     39 - charindex (',',
                                                     reverse (substring (rk.fkey,
                                                                         1,
                                                                         38))))
               from #refkeysx rk,
                    #refkeysx rk2
               where rk.seq        = @sequence
                 and rk2.constrid  = rk.constrid
                 and rk2.seq       = @sequence + 1
                 and rk2.fkey     <> ' '

               update #refkeysx
               set pkey = substring (rk.pkey,
                                     1,
                                     39 - charindex (',',
                                                     reverse (substring (rk.pkey,
                                                                         1,
                                                                         38))))
               from #refkeysx rk,
                    #refkeysx rk2
               where rk.seq        = @sequence
                 and rk2.constrid  = rk.constrid
                 and rk2.seq       = @sequence + 1
                 and rk2.pkey     <> ' '

               -- SPLIT OUT LONG LINES THAT HAVE NO COMMAS

               insert into #refkeysx
                  select constrid,
                         fdbname,
                         ftabid,
                         ftabname,
                         '    '  +  substring (fkey, charindex ('(', fkey), 38),
                         pdbname,
                         ptabid,
                         ptabname,
                         '    '  +  substring (pkey, charindex ('(', pkey), 38),
                         @sequence + 1
                  from #refkeysx
                  where seq                   = @sequence
                    and char_length (fkey)    > 38
                    and charindex (',', fkey) = 0
                    and char_length (pkey)    > 38
                    and charindex (',', pkey) = 0

               insert into #refkeysx
                  select constrid,
                         fdbname,
                         ftabid,
                         ftabname,
                         '    '  +  substring (fkey, charindex ('(', fkey), 38),
                         pdbname,
                         ptabid,
                         ptabname,
                         ' ',
                         @sequence + 1
                  from #refkeysx
                  where seq                   = @sequence
                    and char_length (fkey)    > 38
                    and charindex (',', fkey) = 0
                    and char_length (pkey)    < 39

               insert into #refkeysx
                  select constrid,
                         fdbname,
                         ftabid,
                         ftabname,
                         ' ',
                         pdbname,
                         ptabid,
                         ptabname,
                         '    '  +  substring (pkey, charindex ('(', pkey), 38),
                         @sequence + 1
                  from #refkeysx
                  where seq                   = @sequence
                    and char_length (fkey)    < 39
                    and char_length (pkey)    > 38
                    and charindex (',', pkey) = 0

               select @sequence = @sequence + 1
            end

         select 'FOREIGN KEY' = substring (fkey, 1, 38),
                'PRIMARY KEY' = substring (pkey, 1, 38)
         from #refkeysx
         order by ftabname, ptabname, seq
      end
go
sp_addcomment sp_refkeysx, 'Lists external referential keys for TABLE or (default) all tables'
go
sp_procxmode sp_refkeysx, anymode
go
