Check existence and non-existence of columns in a table

admin

In this Bog, We will prepare a query which will tell us which tables has one specific column but another column which is also important is not present.
QUERY-1
select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS c where COLUMN_NAME='column1' and not exists ( select 1 from INFORMATION_SCHEMA.columns cc where cc.TABLE_NAME=c.TABLE_NAME
and cc.COLUMN_NAME='column2')

The above Query will list all tables in which column1 is present and column2 is not present.

QUERY-2
select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS c where COLUMN_NAME='column1' and exists ( select 1 from INFORMATION_SCHEMA.columns cc where cc.TABLE_NAME=c.TABLE_NAME
and cc.COLUMN_NAME='column2')

This Query will list all tables in which both the columns(Column1 and column2) are present.

Related posts