Look for missing columns

Question

I'm trying to find an easy way how to check all tables in schema where this columns doesn't exists.

So my case: In the middle of project we started adding some technical columns that needs to be in every table in that schema and I want to get list of tables that doesn't have this column.

Is there an easy way of doing that?

Answer 1

Using QUALIFY and COUNT_IF:

USE DATABASE <db_name>;

SELECT DISTINCT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_SCHEMA ILIKE ANY ('<SCHEMA_NAME1>', '<SCHEMA_NAME2>')
QUALIFY COUNT_IF(c.COLUMN_NAME ILIKE '<SOME_TECHNICAL_COL>')
        OVER(PARTITION BY c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME) = 0;