Search code examples
sqlsnowflake-cloud-data-platforminformation-schema

Find columns that are one table and but not in the other table

I am working with two very wide tables (Table A, Table B) in Snowflake. Both tables should have exact same columns, though not necessarily in the same order. However Table B is missing a few columns. Is there a way to quickly find all the columns that are in Table A but not in Table B? I am not concerned with the Data, just the existence of columns.

Essentially, I am looking for a SQL way to find all columns in Table that are missing in Table B.

I currently use a ETL tool for this. But it is rather cumbersome. It would be nice if this can be done using SQL.

Solution

  • Try the following

    SELECT column_name
FROM information_schema.columns 
WHERE table_name = 'tableA'
    AND column_name NOT IN
    (
        SELECT column_name
        FROM information_schema.columns 
        WHERE table_name = 'tableB'
    );