2

I have 2 identical databases. abc15 and abc18. But one of the database has one extra table and I need to find that. I thought the following query should return it, but is it not showing the record that I expect.

select * from information_schema.tables as a
    left join information_schema.tables as b
        on a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAME
    where a.TABLE_SCHEMA = 'abc15' AND b.TABLE_SCHEMA='abc18' and
        b.TABLE_NAME IS NULL
Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
shantanuo
  • 31,689
  • 78
  • 245
  • 403
  • 1
    Your query is only going to check one direction, what happens when the new "table" is in the "b" database? Switch to a outer join. – Zachary May 16 '10 at 05:16

2 Answers2

3

What if database b has the extra table? Try a full outer join and an extra constraint at the end (OR a.TABLE_NAME IS NULL)

LesterDove
  • 3,014
  • 1
  • 23
  • 24
1
$ mysqldumpslow --database abc15 >/tmp/a
$ mysqldumpslow --database abc18 >/tmp/b
$ diff /tmp/a /tmp/b
amphetamachine
  • 27,620
  • 12
  • 60
  • 72