Need help in rewriting query

Question

I have 2 databases test1 and test2. where I need to compare columns from schema1 to schema2 and fetch the results.If table in test1 schema not present on test2 schema ,it should ignore that table. I have below query which shows all the results including table not present in test2 schema.

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 test1,
               c2.column_name as test2
        from
            (select table_name,
                    column_name
             from information_schema.columns c
             where c.table_schema ='test1') c1
        right join
                 (select table_name,
                         column_name
                  from information_schema.columns c
                  where c.table_schema ='test2') 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 test1,
           c2.column_name as test2
    from
        (select table_name,
                column_name
         from information_schema.columns c
         where c.table_schema ='test1') c1
    left join
             (select table_name,
                     column_name
              from information_schema.columns c
              where c.table_schema ='test2') c2
    on c1.table_name = c2.table_name and c1.column_name = c2.column_name

) tmp
where test1 is null
      or test2 is null
order by table_name,
         table_column;

Is it possible to rewrite above query ,just compare table in test1 with test2 and display the mismatch columns.if table not present in test2 schema, it should ignore that table. Thanks

Akina · Answer 1 · 2024-09-06T10:42:46.913

test this:

WITH 
cte1 AS (
    SELECT table_schema,
           table_name,
           column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema IN ('test1', 'test2')
    ),
cte2 AS (
    SELECT DISTINCT table_name
    FROM cte1
    WHERE table_schema = 'test2'
    )
SELECT table_name,
       column_name
       CASE SUM(RIGHT(table_schema, 1))
       WHEN 1 THEN 'Present in test1 only'
       WHEN 2 THEN 'Present in test2 only'
       WHEN 3 THEN 'Present in both schemas'
       ELSE        'Something went wrong..'
       END
FROM cte1
NATURAL JOIN cte2
GROUP BY 1, 2
/* ORDER BY ??? */;

score 0 · Answer 2 · answered Sep 20 '24 at 03:28

For starters, simplify

            from  
            (
                SELECT  table_name, column_name
                    from  information_schema.columns c
                    where  c.table_schema ='test1'
            ) c1
            left join  
            (
                SELECT  table_name, column_name
                    from  information_schema.columns c
                    where  c.table_schema ='test2'
            ) c2  ON c1.table_name = c2.table_name
              and  c1.column_name = c2.column_name

to:

            from       information_schema.columns c1
            left join  information_schema.columns c2
                ON   c1.table_name  = c2.table_name
               and   c1.column_name = c2.column_name 
            where  c1.table_schema ='test1'
              AND  c2.table_schema ='test2'

Need help in rewriting query

2 Answers2