How Can I Print Details (table Name, Column Name, Data Type) Of Each Column/table In My Db2 Database?

In my previous question Mark suggested a good answer for displaying count on every table in my database. I would like to expand this procedure and - instead of counts - display the

Solution 1:

If you use Db2 for LUW, then you shouldn't use SYSIBM schema in your queries on the system catalog. Use SYSCAT instead.

You don't have to use any functions to get what you want here. Use the following query instead:

SELECT TABSCHEMA, TABNAME, COLNAME, TYPENAME
FROM SYSCAT.COLUMNS
ORDERBY TABSCHEMA, TABNAME, COLNO;

As for your routine. There is a number of errors in the text.

1) if you want to assign multiple values with SET statement, you must use the corresponding number of parameter markers in the statement:

SET (?, ..., ?) = (SELECT COL1, ..., COLn FROM ...);
PREPARE S FROM L_STMT;
EXECUTE S INTO L_V1, ..., L_Vn;

2) RETURNS TABLE (...) and PIPE(...) must have the same number of columns

Solution 2:

You could directly query the tables SYSCAT.COLUMNS and SYSCAT.TABLES. The following returns the table schema and name followed by column name and their type. Column info is sorted by the column order:

select t.tabschema, t.tabname, c.colname, c.typename, c.colno
from syscat.columns c, syscat.tables t
where t.type='T' and t.tabname=c.tabname and t.tabschema=c.tabschemaorderby1,2,c.colno

BTW: Db2 has a tool db2look to export schema information.