compare the structure of two tables or two databases:

### compare the structures between two different databases

There are two ways to compare the structure of two databases.

one is by linux and other by stored procedure in mysql.

Method1:

 diff <(mysqldump -hsystem -ukiran -pkiran test myinfo --skip-extended-insert) <(mysqldump -hsystem2 -ukiran -pkiran test myinfo --skip-extended-insert)
 --side-by-side --suppress-common-lines --width=690 | more


method2:


USE test;
DROP PROCEDURE IF EXISTS comparetwodb;
DELIMITER |
CREATE PROCEDURE comparetwodb( vdb1 VARCHAR(64), vdb2 VARCHAR(64) )
BEGIN

  DROP TEMPORARY TABLE IF EXISTS desc1,desc2;
  CREATE TEMPORARY TABLE desc1
  SELECT
    t1.table_schema,
    t1.table_name,
    t1.table_type,
    t1.engine,
    c1.column_name,
    c1.ordinal_position,
    c1.column_type,
    c1.column_default,
    c1.is_nullable,
    c1.column_key
  FROM information_schema.tables t1
  JOIN information_schema.columns c1 USING (table_schema,table_name)
  WHERE t1.table_schema=vdb1 
  ORDER BY t1.table_name,c1.column_name;

  CREATE TEMPORARY TABLE desc2
  SELECT
    t1.table_schema,
    t1.table_name,
    t1.table_type,
    t1.engine,
    c1.column_name,
    c1.ordinal_position,
    c1.column_type,
    c1.column_default,
    c1.is_nullable,
    c1.column_key
  FROM information_schema.tables t1
  JOIN information_schema.columns c1 USING (table_schema,table_name)
  WHERE t1.table_schema=vdb2 
  ORDER BY t1.table_name,c1.column_name;

  SELECT
    TableName,column_name,MIN(SchemaName),table_type,engine,
    ordinal_position,column_type,column_default,is_nullable,column_key
  FROM (
    SELECT 
      a.table_schema AS SchemaName,a.table_name AS TableName,a.table_type,a.engine,
      a.column_name,a.ordinal_position,a.column_type,a.column_default,a.is_nullable,a.column_key
    FROM desc1 a
    UNION ALL
    SELECT 
      b.table_schema AS SchemaName,b.table_name AS TableName,b.table_type,b.engine,
      b.column_name,b.ordinal_position,b.column_type,b.column_default,b.is_nullable,b.column_key
    FROM desc2 b
  ) AS tmp
  GROUP BY TableName,table_type,engine,column_name,ordinal_position,column_type,column_default,is_nullable,column_key
  HAVING COUNT(*) = 1
  ORDER BY TableName,column_name,SchemaName;   

  DROP TEMPORARY TABLE desc1, desc2;

END |
DELIMITER ;

Call it as follows:

CALL comparetwodb('db1','db2');

Suppose if we want to compare the structure of two tables the query is as follows:

SELECT
  MIN(TableName) AS 'Table',
  column_name AS 'Column',
  ordinal_position AS 'Position'
FROM (
  SELECT
    'transaction_table' as TableName,
    column_name,
    ordinal_position
  FROM information_schema.columns AS i1
  WHERE table_schema='test' AND table_name='testtable'
  UNION ALL
  SELECT
    'transaction_log_table' as TableName,
    column_name,
    ordinal_position
  FROM information_schema.columns AS i2
  WHERE table_schema='test' AND table_name='testtable_log'
) AS tmp
GROUP BY column_name
HAVING COUNT(*) = 1
ORDER BY ordinal_position;