Extracting create table statements from a existing database.

Question

I'm trying to extract a create table statement from an existing derby schema. I can get all of the columns and data types via this.

select * from SYS.SYSTABLES a inner join sys.SYSCOLUMNS b on a.TABLEID= b.REFERENCEID

This gives me the table name, column name, data type, default value, auto increment etc. Very useful for basic table construction. It's lacking for constraints and indexes.

I can get some information on constraints via:

select a.*, b.TABLENAME from SYS.SYSCONSTRAINTS a inner join sys.SYSTABLES b on a.TABLEID = b.TABLEID

This will give me the constraint name, table it's on, and a type. I don't know what the type letters mean. I'm also not sure there's index information here.

What I would like is something very similar to what I can get from views.

select * from sys.SYSVIEWS

There's a column from that called VIEWDEFINITION that will give me the create statement for each view. That would be incredibly useful for tables.

Thanks,

Answer 1

The full source code for dblook is available as part of the Derby source. You can read about dblook here: http://db.apache.org/derby/docs/10.8/tools/ctoolsdblook.html