Search code examples
sqldb2dbeaver

List all tables with a specific column Name

i didn't find the right answear here. So I work with a lot of different tables. The Schema of the Tables are GDBADMP.[Table_Name] or USCH1060.[Table_Name].

Now i search a way to list all tables with an exact column Name like PLZ_ID. The column contains different values, somtimes varchar or int

Like: Show all Tables (GDBADMP.[Table_Name]) with the column Name PLZ_ID

My first thoughts are like this:

SELECT *
FROM GDBADMP.*
WHERE PLZ_ID

Kind regards

Solution

  • Look at the syscat.columns catalog view. You may join it to syscat.tables by (tabschema, tabname) to get tables only (excluding views, for example).

    Thanks to this comment. I found a solution!

    SELECT *
FROM SYSCAT.COLUMNS a
WHERE a.colname = 'PLZ_ID'