Thread: Create table statement from DatabaseMetaData

Hi Experts,

Do you know if it's possible to retrieve a create table statement based on a DatabaseMetaData ? (without looping through columnName / Type)

The idea is to get the DatabaseMetaData from one db server, and execute the Create Table statements on a different db server.

One obvious solution would be to loop through each table's column name/type and construct the create table statement manually, but I'd like to know whether this can be automated.

Thanks in advnace,

Sid

Yes, but it depends upon how you wish to create the new tables. Can you provide a simple example that demonstrates what you wish to achieve?

Sure.

We have two databases: Database A and B.

On database A, there is a table called Table T.

Using JDBC, we get the DatabaseMetaData for database A , and locate Table T in the meta data.

Now at this point, I'd like to know how we can get a ready made Create statement for Table T (simply a string) and execute it on database B.

Hope that was clear.

Thanks.

I meant more of a precise example showing the schema for Table T, and how you wish to create the table in Database B...for example, do you just wish to Copy the table to database B? Or restructure its data into database B.

No it's simply creating the table with no data manipulation. I don't really care about table contetns.

example:

Table T (on database A)
Column A int
Column B int

conn = getConnection(database A);

DatabaseMetaData dmd = conn.getMetaData();

String createStatement = dmd.getCreateStatement ( String tableName ); // This is what I am looking for

Ah, I see...take a look at this link. It provides code to copy a database, but can be trimmed to copy a single table schema:How To Copy Database Data Using JDBC. You should note that things like foreign keys, indexes, triggers, views, etc...are not copied in this code.

Thanks for that - appreciate your help.

Sid