1

I have two mysql dump files.

dump1.sql

CREATE TABLE `designs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `template` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

dump2.sql

CREATE TABLE `designs` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `act` tinyint(1) NOT NULL,
      `temp` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

How to find the difference in two table structure in dump1 & dump2?

vinoth
  • 106
  • 2
  • 12

2 Answers2

1

Second query select all COLUMN_NAME of table1 and by using NOT IN we compare COLUMN of both table :-

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'your_schema' AND 
      TABLE_NAME = 'table2' AND 
      COLUMN_NAME NOT IN (
         SELECT COLUMN_NAME
         FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_SCHEMA = 'your_schema' AND 
               TABLE_NAME = 'table1');
Abhishek Sharma
  • 6,689
  • 1
  • 14
  • 20
  • 1
    I would like to compare the two sql dump files. – vinoth Oct 27 '15 at 06:33
  • before create table plz change table names and use this query – Abhishek Sharma Oct 27 '15 at 06:36
  • SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'designs1' AND COLUMN_NAME NOT IN ( SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'designs2') – Abhishek Sharma Oct 27 '15 at 06:36
  • Actullay, I want to restore the database from dump.sql file. The table `designs` related functionality already done. – vinoth Oct 27 '15 at 06:51
  • I have maintained the dump1.sql file in server. while restoring the dump2.sql file, i want to check the table structure is correct or not? ie) table structure is identical to the dump1.sql – vinoth Oct 27 '15 at 06:55
  • first create 2 new tables and than upload your both dump file and use my MySQL query – Abhishek Sharma Oct 27 '15 at 06:58
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/93449/discussion-between-rahautos-and-vinoth). – Abhishek Sharma Oct 27 '15 at 07:03
  • Please don't just post "code only" answers. While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762) out of the code really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, this reduces the readability of both the code and the explanations! [**And it's not just this one!**](http://pastebin.com/9GE2A5rr) – Rizier123 Nov 19 '15 at 13:40
  • Also you then [improve your answer in so many ways and many more!](http://pastebin.com/9v6KFiM8) – Rizier123 Nov 19 '15 at 13:51
  • Use the [help] to improve your answers. See: http://stackoverflow.com/help/how-to-answer , http://stackoverflow.com/help/formatting – Rizier123 Nov 19 '15 at 13:55
  • To get ideas how good answers can be formatted, structured or worded. Just look at some good ones: http://stackoverflow.com/a/31280108/3933332 and see the options you have with formatting, additional information, explanations and references. – Rizier123 Nov 19 '15 at 14:16
0

But your table names should be different to check this. try this, 

select column_name
      ,max(case when table_name = 'table_1' then 'Yes' end) as in_table_1
      ,max(case when table_name = 'table_2' then 'Yes' end) as in_table_2
  from information_schema.columns
  where table_name in('table_1', 'table_2')
   and table_schema = 'your_database'
  group
    by column_name
 order
    by column_name;

Or, if you want to use files,

mysqldump --skip-comments --skip-extended-insert -u root -p dbName1>file1.sql
mysqldump --skip-comments --skip-extended-insert -u root -p dbName2>file2.sql

diff file1.sql file2.sql

For more information, see this link All possible ways are given here.

Community
  • 1
  • 1
Niranjan N Raju
  • 12,047
  • 4
  • 22
  • 41