db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: derby user (Forwarding to DerbyDev)
Date Fri, 16 Dec 2005 18:56:56 GMT
We keep seeing many requests for this kind of behavior... It should be
pretty easy to enhance IJ to add DESCRIBE like functionality. This would
be a good project for anyone interested in learning Derby system tables
and some JDBC programming, so I am forwarding this to DerbyDev.

We do have a Jira entry for this new feature already:
http://issues.apache.org/jira/browse/DERBY-457

Satheesh

Jean T. Anderson wrote:

> 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