Compare tables and columns in two schemas in MariaDB database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-09-23

Table of Contents:


    Article for: MySQL SQL Server Teradata

    Query below compares columns (names) in tables between two MySQL databases (schemas). It shows columns missing in either of two schema.

    Query

    set @database_1 = 'database_name_1'; -- provide first database name here
    set @database_2 = 'database_name_2'; -- provide second database name here
    select * 
    from (
            select COALESCE(c1.table_name, c2.table_name) as table_name,
                   COALESCE(c1.column_name, c2.column_name) as table_column,
                   c1.column_name as database1,
                   c2.column_name as database2
            from
                (select table_name,
                        column_name
                 from information_schema.columns c
                 where c.table_schema = @database_1) c1
            right join
                     (select table_name,
                             column_name
                      from information_schema.columns c
                      where c.table_schema = @database_2) c2
            on c1.table_name = c2.table_name and c1.column_name = c2.column_name
    
        union
    
            select COALESCE(c1.table_name, c2.table_name) as table_name,
                   COALESCE(c1.column_name, c2.column_name) as table_column,
                   c1.column_name as schema1,
                   c2.column_name as schema2
            from
                (select table_name,
                        column_name
                 from information_schema.columns c
                 where c.table_schema = @database_1) c1
            left join
                     (select table_name,
                             column_name
                      from information_schema.columns c
                      where c.table_schema = @database_2) c2
            on c1.table_name = c2.table_name and c1.column_name = c2.column_name
    ) tmp
    where database1 is null
          or database2 is null
    order by table_name,
             table_column;
    set @database_1 = null;
    set @database_2 = null;
    

    Instruction

    Replace database_name_1 and database_name_2 with names of two of your databases (schemas) (on MySQL instance) that you'd like to compare.

    Columns

    • table_name - name of the table
    • table_column - name of column
    • database1 - if column exists in a table in database1 (schema) then column contains its name (repeats it from column column)
    • database2 - if column exists in a table in database2 (schema) then column contains its name (repeats it from column column)

    Rows

    • One row represents one distinct name of column in specific table. Row represents column if it exist only in one schema.
    • Scope of rows: all distinct schema, table and column names in both databases.
    • Ordered by table name and column name

    Sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept