How to get table name from column in DB2?

Question

I need the DB2 SQL query to find the table/tables from column name. I have the column name but don't have table name which this column belongs to.

It is possible that multiple tables have a column with that name. Especially if the column name is for example "ID" or "LAST_UPDATED" — crowne, Sep 01 '11 at 17:59
@crowne - Check the question please I have already mentioned table/tables. — AAA_king, Sep 01 '11 at 19:13

score 14 · Answer 1 · edited Feb 14 '17 at 11:25

14

select TBNAME
    from sysibm.syscolumns
    where NAME = '<column name>'

edited Feb 14 '17 at 11:25

Vitaliy Ulantikov

answered Sep 01 '11 at 17:52

boes

score 6 · Answer 2 · edited Feb 14 '17 at 11:26

6

SELECT tabname
    FROM syscat.columns
    WHERE colname = 'mycol'

edited Feb 14 '17 at 11:26

Vitaliy Ulantikov

answered Sep 02 '11 at 14:10

Peter Miehle

2

Object names default to all upper case in DB2, but in case someone is deliberately using mixed-case names, consider using WHERE UPPER(colname) = UPPER('somecolumnname') – Fred Sobotka Sep 07 '11 at 17:17
You should even consider not using UPPER in the right part of the condition, for performance purposes `WHERE UPPER(colname) = "SOMECOLUMNNAME` – Kilazur Dec 12 '14 at 10:15

score 5 · Answer 3 · answered Apr 13 '18 at 14:57

5

For DB2/AS400 users:

SELECT TABLE_SCHEMA, TABLE_NAME 
    FROM QSYS2.SYSCOLUMNS 
    WHERE upper(column_name) = upper('[column_name]')

answered Apr 13 '18 at 14:57

Wildcat Matt

score 0 · Answer 4 · edited Oct 31 '18 at 11:59

0

If you are using Visual Studio Server Explorer, I found using the following worked the best:

SELECT       TABNAME
FROM         SYSCAT.COLUMNS
WHERE        COLNAME = 'NASR_DESC'

Visual Studio still formatted it, but the formatting inserted by Visual Studio still worked.

Hope this helps someone searching for a known column name in their IBM DB2 database using Visual Studio Server Explorer.

edited Oct 31 '18 at 11:59

brasofilo

answered Oct 31 '18 at 11:35

Keenan Stewart

4 Answers4