How to view DB2 Table structure

Question

How to view the table structure in DB2 database

User · Answer

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

User · Answer

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

User · Answer

php example for iSeries  as400  db2  yes this worked    i5   db2 connect  database   user   password  array  i5 lib   gt  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

User · Answer

How to view the table structure in db2 database   Open db2 command window  connect to db2 with following command    gt  db2 connect to DATABASE NAME USER USERNAME USING PASSWORD   Once you connected successfully  issue the following command to view the table structure    gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can Get the table meta data using this query   SELECT   FROM SYSIBM COLUMNS WHERE TABLE NAME    ASTPCLTEXT

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Follow this simple steps    Select the Browsers window  Extract  expand  it  Select and extract  expand  the table list  Select the required table and extract  expand  it  On double click the code option  it opens the table structure

User · Answer

I got the answer from the sysibm syscolumns  Select distinct name   ColType  Length from Sysibm syscolumns where tbname    employee

User · Answer

In DB2  enter on db2 command prompt     db2    gt   describe  table MyTableName

[db2] How to view DB2 Table structure

Examples related to db2