• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1578
  • Last Modified:

describe query

hi there
few question,
1) i want to see all tables in DB2 whats query for tht like for oracle its select * from tab;
wht is query for DB2 ?
2) in oracle if u want to see all datatypes in tables like in oracle describe * from emp;
it gives us all datatypes

wht is query for DB2 ?
  • 2
  • 2
1 Solution
for getting the list of tables:

select tabname form syscat.tables;

The above query will return all tables including system tables..if u want to retrieve only tables from a specific schema..use this
select tabname from syscat.tables where tabschema='dbSchemaName'

for getting column details;

select * from syscat.columns where tabname='emp'
bobby2929Author Commented:
thanks for part 1
wht query is there if i want to see all datatypes in a table
lets say i have tABLE EMP and i want to see all datatypes
like first varchar (20);
last varchar (20);

as in oracle if query describe * from emp; will do
what abt DB2.
The following query will list all the details of columns in a table..

select * from syscat.columns where tabname='TableName'..

or specifically if u want just column dataype information..just retrieve these fields
SELECT colname,typename,length FROM SYSCAT.COLUMNS WHERE TABNAME='TableName'
bobby2929Author Commented:
Hi there
when i run this in oracle i get following result

sql>DESCRIBE employee
SQL*Plus lists the following information:

Name                           Null?    Type
------------------------------ -------- ------------
ENAME                                   VARCHAR2(30)
EMPADDR                                 ADDRESS
JOB                                     VARCHAR2(20)
SAL                                     NUMBER(7,2)

when i run this query in DB2

select * from syscat.columns where tabname='employee'  

i am not getting same result as above

even this query dont give me this result

SELECT colname,typename,length FROM SYSCAT.COLUMNS WHERE TABNAME='employee'


Kent OlsenData Warehouse Architect / DBACommented:
Hi bobby2929,

It may be easier (at leat to remember) to use a system provided view:

select * from sysibm.tables where tablename = 'TableName'

Good Luck!

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now