db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean T. Anderson" <...@bristowhill.com>
Subject Re: derby user
Date Fri, 16 Dec 2005 18:44:50 GMT
meenakshi selvi wrote:
> hi
>  
> 1.is there any way to describe the table in ij tools.

You can execute SQL select statements that query the system tables. The 
system tables are listed here

    http://db.apache.org/derby/docs/dev/ref/rrefsistabs38369.html

Obtaining the description for a table is pretty simple if you just want 
the column names and types; for example, given this table:

    ij> create table hotelavailability
    (hotel_id int not null,
    booking_date date not null,
    rooms_taken int default 0,
    primary key (hotel_id, booking_date)
    );

This query gets you the information in the sys.systables table:

   select * from sys.systables where tablename = 'HOTELAVAILABILITY';

This query gets the columns for that table:

    ij> select c.columnnumber,
        c.columnname,
        c.columndatatype
    from   sys.syscolumns c, sys.systables t
    where  c.referenceid=t.tableid
    and    t.tablename='HOTELAVAILABILITY'
    order by 1;

    COLUMNNUMB&|COLUMNNAME             |COLUMNDATATYPE
    -----------------------------------------------------
    1          |HOTEL_ID               |INTEGER NOT NULL
    2          |BOOKING_DATE           |DATE NOT NULL
    3          |ROOMS_TAKEN            |INTEGER

    3 rows selected


Add a join to sys.syscontraints to get constraint information -- here's 
where the query can start getting really messy.  dblook might be an 
easier option; see the next point.

> 2.is there any way to show the tables in a database.

dblook dumps the schema for a database or table; for more information, see

  http://db.apache.org/derby/docs/dev/tools/ctoolsdblook.html

regards,

  -jean

Mime
View raw message