I can see a couple of different solutions, the best one would be have to be determined by you.

Firstly, I'm assuming that at least 1 of these tables would have to be the master table that all data would be referenced against.

Secondly, my solutions would depend on the size of the table.

Thirdly, being a system table (IMHO) it would probably make more sense to have these scripts notify a DBA, but that is a separate conversation entirely. (You may also want to seriously consider removing all non-select access to system tables except for 1 to 2 DBA types to eliminate this from happening in the future)

*also worth noting: With each of these solutions, if your master table gets horked, it cascades down...

1. If the tables are small: Write a script that opens up connections to the 7 tables.

Then do a 'select stuff from master table'. While looping through the results, make select calls out to the remaining 6 tables and compare the results (most likely based on primary key). If something is out of wack send notice or fix it.

This could also be done on each of the slave dbs making a connection to the master as explained below, just using perl instead of db actions.

2. If the tables are large/very large: Write a script that runs (daily/hourly/etc) that connects from each of the 'slave' databases. This script would connect to the master, then copy the contents of the master system table into a local table on the slave db. You _should_ (this is based on knowledge of oracle and 2 seconds of googling sybase) then be able to create a synonym of the new table to be referenced as the system table (you may also have to drop the system table first). You'd also have to make sure to enable all of the same grants that are in place on the master table before you create the synonym.

Hope this helps.

In reply to Re: Comparing tables over multiple servers by RiotTown
in thread Comparing tables over multiple servers by mnlight

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.