Db2: Query for searching table with different column name

Question

In my database, all tables should have a column (let's say "abc") and I want to find out the tables which do not have this column. Do we have any such query to fulfill this requirement?

Database: Db2 v11.1 LUW

Possible duplicate of [Find related columns among hundreds of tables for future relational identification](https://stackoverflow.com/questions/42397279/find-related-columns-among-hundreds-of-tables-for-future-relational-identificati) — Clockwork-Muse, May 14 '19 at 21:14

score 1 · Answer 1 · answered May 14 '19 at 16:09

You can build a query against SYSCAT.COLUMNS (and SYSCAT.TABLES) to find those tables not having such column:

select tabname from syscat.tables t1
where not exists
    (select colname from syscat.columns c
     where c.tabname=t1.tabname and colname='foo')
and tabname like 'SYSX%'

Above is just an example and not optimized.

score 0 · Answer 2 · answered May 14 '19 at 21:37

Non-system tables only. Column name must be in uppercase, unless you specified the column name as “abc” (in double quotes) upon the table creation intentionally.

select tabschema, tabname 
from syscat.tables t
where not exists
(
select 1 
from syscat.columns c
where c.tabschema=t.tabschema and c.tabname=t.tabname 
and c.colname='ABC'
)
and tabschema not like 'SYS%'
and type='T';

Db2: Query for searching table with different column name

2 Answers2