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.

Blackburn

It is possible that multiple tables have a column with that name. Especially if the column name is for example "ID" or "LAST_UPDATED" – Reimport

@Reimport - Check the question please I have already mentioned table/tables. – Blackburn

Answer 1

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

Dagley

Answer 2

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

Bakken

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') – Downey

You should even consider not using UPPER in the right part of the condition, for performance purposes WHERE UPPER(colname) = "SOMECOLUMNNAME – Radiophotograph

Answer 3

For DB2/AS400 users:

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

Danialah

Answer 4

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.

Myeshamyhre