asked on

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 ?
Thanks,
Bobby

nagki

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'

bobby2929

ASKER

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.
thanks,
Bobby

ASKER CERTIFIED SOLUTION

nagki

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bobby2929

ASKER

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'

thanks
Bobby

Kent Olsen

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!
Kent