How to view DB2 Table structure

Question

How to view the table structure in DB2 database

Answer 1

Use the below to check the table description for a single table

DESCRIBE TABLE Schema Name.Table Name

join the below tables to check the table description for a multiple tables, join with the table id syscat.tables and syscat.columns

You can also check the details of indexes on the table using the below command describe indexes for table . show detail

Answer 2

Also the following command works:

describe SELECT * FROM table_name;

Where the select statement can be replaced with any other select statement, which is quite useful for complex inserts with select for example.

Answer 3

The OP doesn't mention if this is DB2/400 being discussed, but I found that the only way I could get the table structure including the column name descriptions was to use DSPFFD.

DSPFFD FILE(TBNAME) OUTPUT(*OUTFILE) OUTFILE(SOMELIB/TBDESC)

This puts the description of TBNAME in a table called TBDESC in the SOMELIB library. You can then query that with:

select * from SOMELIB/TBDESC

Answer 4

FOR TABLE DESCRIPTION IN IBM DB2 10.7 VERSION I TRIED THIS AND IT WORKED FINE

SELECT NAME,COLTYPE,NULLS,LONGLENGTH FROM SYSIBM.SYSCOLUMNS where TBcreator =SCHEMANAME and TBNAME =TABLENAME;

Answer 5

How to view the table structure in db2 database

Open db2 command window, connect to db2 with following command.

> db2 connect to DATABASE_NAME USER USERNAME USING PASSWORD

Once you connected successfully, issue the following command to view the table structure.

> db2 "describe select * from SCHEMA_NAME.TABLE_NAME"

The above command will display db2 table structure in tabular format.

Note: Tested on DB2 Client 9.7.11

Answer 6

php example for iSeries (as400) db2, yes this worked!

$i5 = db2_connect($database, $user, $password, array("i5_lib"=>"qsys2"));

$querydesc = "select * from qsys2.syscolumns where table_schema = '".$library."' and table_name = '".$table_name."' ";

$result = db2_exec($i5, $querydesc);

also if you just want to list all tables with their descriptions

$query = "select TABLE_NAME, TABLE_TEXT from systables where table_schema = '$library' ";

$result = db2_exec($i5, $query);