DB2: How to find a column in a table or list of tables?

Question

Im using a DB2 database. What would be the appropriate SQL query to find out if a column exists in a table or list of tables?

eg

if "column_name" is found in "table name" or [list of table names] return true or the name of tables that have that column. 

Many thanks.

Answer 1

Use the SYSCAT.COLUMNS view in the directory :

 SELECT TABNAME FROM SYSCAT.COLUMNS WHERE TABNAME IN ('table name 1', 'table name 2') AND COLNAME = 'column_name'; 

Answer 2

Tested on DB2 z / OS 9.1 and LUW 9.7:

 SELECT STRIP(TBCREATOR) || '.' || STRIP(TBNAME) FROM SYSIBM.SYSCOLUMNS WHERE NAME = 'your_col' AND TBNAME IN ('list', 'of', 'tables') 

If you only want results from a specific schema, you can add AND TBCREATOR = 'your_schema' at the end of the query.

Answer 3

Another way to do this is error handling:

 declare v_sql varchar(1000); declare col_missing integer default 0; declare col_does_not_exist condition for sqlstate '42703'; declare continue handler for col_does_not_exist set col_missing = 1; set v_sql = 'select table.foo from table'; execute immediate v_sql; if col_missing = 1 then --Do something if column foo doesn't exist. end if; 

This method will work with session tables, and you can also use it on an object, even if you don’t know if it is a table, view, alias, etc.

It is important to specify table.foo , not just the column name, because otherwise the existence of another object (such as a variable) called foo will break the test.

This handler will continue to mask any other errors for columns that are not in scope, so it's best to limit the scope to just the test you want to do.