describe query

Posted on 2005-04-16
Last Modified: 2012-05-05
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 ?
Question by:bobby2929
    LVL 8

    Expert Comment

    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'

    Author Comment

    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.
    LVL 8

    Accepted Solution

    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'

    Author Comment

    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'


    LVL 45

    Expert Comment

    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now