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,

I should also add, I need to accomplish this programatically, and repeatably. — kevingreen, Jul 16 '12 at 13:23
seeing as a view is basically named sql statement, the sql being in there isn't some sort of optional extra. Unless someone has already done and it's not version specific, this is going to be an RTFM and skull sweat manouevre — Tony Hopkinson, Jul 16 '12 at 13:27
@TonyHopkinson fair enough. I figured it was going to be a matter of piecing it all together. I wasn't sure if there were any built in functions, or somewhere I could get better data definitions. I'm more used to MySQL and things like this always seem much easier in that DBMS. — kevingreen, Jul 16 '12 at 13:29
@kevingreen Madhivanan is suggesting you use (Microsoft) SQL Server Management Studio. I don't think this will help — podiluska, Jul 16 '12 at 13:51

score 0 · Accepted Answer · answered Jul 16 '12 at 19:55

0

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

answered Jul 16 '12 at 19:55

Bryan Pendleton

16,128
3
32
56

Exactly what I was looking for, thanks. I knew there had to be a method to extract all the info I needed without manually putting it together manually. – kevingreen Jul 17 '12 at 13:00
I only thought that was a standalone tool based on the documentation, but I see now how I can use it in Java and automate my task. – kevingreen Jul 17 '12 at 13:07
The link is broken. :-( – Sergiy Belozorov Nov 24 '13 at 19:48
I'm sorry you're not able to get to the link. It works fine for me. – Bryan Pendleton Dec 01 '13 at 16:43

Extracting create table statements from a existing database.

1 Answers1