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 ?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 OlsenDBACommented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.