How to view DB2 Table structure

How to view the table structure in DB2 database

19 Answers

I got the answer from the sysibm.syscolumns

Select distinct(name), ColType, Length from Sysibm.syscolumns where tbname = 'employee'; 

Generally it's easiest to use DESCRIBE.

DESCRIBE TABLE MYSCHEMA.TABLE 

or

DESCRIBE INDEXES FOR MYSCHEMA.TABLE SHOW DETAIL 

etc.

See the documentation: DESCRIBE command

In DB2, enter on db2 command prompt.

 db2 => describe table MyTableName 

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.

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; 

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

1.use db2 describe table

 db2 describe table tabschema.tabname 

2.use db2 describe output

 db2 "describe select * from tabschema.tabname" 

3.use db2look utility

 db2look -d dbname -e -t tabname 

4.find rows in db2 syscat

 db2 "Select * from syscat.columns wher tabname='' and tabschema =''" 

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); 

You can Get the table meta data using this query

SELECT * FROM SYSIBM.COLUMNS WHERE TABLE_NAME = 'ASTPCLTEXT'; 

to get all tables: (You may want to restrict schema to your schema)

select * from syscat.tables 

to get all columns: (where tabname = your_tabname)

select * from syscat.columns 

Control Center already got the feature of that. It's just below the table list.

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

if you're using Aqua Data studio, simply write select * from table_name and instead of pressing execute,, press ctrl +D .

You shall be able to see the description for the table

The easiest way as many have mentioned already is to do a DESCRIBE TABLE

However you can also get some the same + additional information from

db2> SELECT * SYSCAT.TABLES db2> SELECT * FROM SYSCAT.COLUMNS 

I usually use SYSCAT.COLUMNS to find the related tables in the database where I already know the column name :)

Another good way if you want to get the DDL of a particular table or the whole database is to use the db2look

# db2look -d *dbname* -t *tablename* > tablestructure.out 

This will generate the ".out" file for you which will contain the particular table's DDL script.

# db2look -d *dbname* -e > dbstructure.out 

This will generate the entire database's DDL as a single script file, this is usually used to replicate the database, "-e" is to indicate that one wants to export DDL suitable recreate exact same setup in a new database.

Hope this can help someone looking for such answers :)

I am running DB2/LINUXX8664 10.5.3 and describe select * from schema_name.table_name works for me.

However, describe table schema_name.table_name fails with this error:

SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

drop view lawmod9t.vdesc create view lawmod9t.vDesc as select upper(t.table_cat) as Catalog, upper(t.table_schem) as Schema, upper(t.table_name) as table, t.table_text as tableDesc, c.system_column_name as colname_short, c.column_name as colname_long, c.column_text as coldesc, c.Type_Name as type, c.column_Size as size from sysibm.SQLColumns c inner join sysibm.sqltables t on c.table_schem = t.table_schem and c.table_name = t.table_name select * from vdesc where table = 'YPPPOPL' 

I am using Aquadata Studio 12.0.23, which is several versions short of the newest. So your experience may be better than mine. I found that the best way to get an overview was to use the ERD generator. It took a couple of hours, since normalization was not a concept used in the design of this database almost 30 years ago. I was able to get definitions for all of the objects in a few hours, with a file for each.

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

Follow this simple steps:

1. Select the Browsers window.
2. Extract (expand) it.
3. Select and extract (expand) the table list.
4. Select the required table and extract (expand) it.
5. On double click the code option, it opens the table structure.