Oracle SQL find columns with different values-CodePudding

I have two tables A and B both with some millions rows and around one hundred columns.

I want to find which columns have different observations without the need of listing the names of all the columns.

For example, suppose column ID is the primary key in both tables. And that table A is

ID | col1 | col2
----------------
1  | 123  | 101
2  | 456  | 111

while table B is

ID | col1 | col2
----------------
1  | 123  | 101
2  | 456  | 222

The result of my query would be something about col2. I'm indifferent if a table based on observations form tabla A or B or whatever. What I would like in the query is to avoid to list all the columns since they are a lot. I am not interested in knowing the rows whose columns have different values, only the columns.

EDIT:

Consider these assumptions:

First, assume that the columns in the two tables have the same name. I would prefer something that works without this assumption but I'm more than ok with it.
Second, the columns of the tables are only numeric. Again, this is assumed to simplify.

Thanks!

CodePudding user response：

I got the answer from a colleague and I think it's worth posting it for future users. He used PL/SQL and a loop on the two tables TABLE_A and TABLE_B

SET SERVEROUTPUT ON SIZE 10000

DECLARE
    v_sql_text     VARCHAR2(300);
    n_contador     NUMBER;
    n_contador_2   NUMBER;
BEGIN
    FOR x IN (
        SELECT
            column_name
        FROM
            user_tab_columns
        WHERE
            table_name IN (
                'TABLE_A'
            )
    ) LOOP
        v_sql_text := 'SELECT /*  PARALLEL 32 */ count(1) FROM (SELECT DISTINCT('
                      || x.column_name
                      || ') FROM TABLE_A MINUS SELECT DISTINCT('
                      || x.column_name
                      || ') FROM TABLE_B)';

    EXECUTE IMMEDIATE v_sql_text
    INTO n_contador;
    v_sql_text := 'SELECT /*  PARALLEL 32 */ count(1) FROM (SELECT DISTINCT('
                  || x.column_name
                  || ') FROM TABLE_B MINUS SELECT DISTINCT('
                  || x.column_name
                  || ') FROM TABLE_A)';

    EXECUTE IMMEDIATE v_sql_text
    INTO n_contador_2;
    IF n_contador != 0 OR n_contador_2 != 0 THEN
        dbms_output.put_line(x.column_name || ' is different');
    END IF;

END LOOP;
END;
/