views:

1095

answers:

2

If I have a table like:

CREATE TABLE FRED
(
recordId number(18) primary key,
firstName varchar2(50)
);

Is there an easy way to clone it's structure (not it's data) into another table of a given name. Basically I want to create table with exactly the same structure, but a different name, so that I can perform some functionality on it. I want to do this in code obviously. Java preferably, but most other languages should be similar.

+5  A: 

CREATE TABLE tablename AS SELECT * FROM orginaltable WHERE 1=2;

Edit: The WHERE clause prohibits any rows from qualifying.

BQ
Won't that copy it's data as well? I just want the structure.
rustyshelf
No rows are copied since 1=2 is always false. You also will not get any constraints, foreign keys, indexes, etc.
WW
thanks for updating it, that's exactly what I wanted!
rustyshelf
I accidentally submitted the answer before I put the WHERE clause in there and he got his comment in in the 20 sec it took me to edit. I read the OP as just wanting the table def, not the extras you mention (but thanks for noting it for the OP).
BQ
@rustyshelf Glad to help!
BQ
Hmm, it's not going to preserve the primary key, or any foreign key constraints or check constraints etc.. It will also create it as a completely vanilla table. If you wanted to copy an index organised or partitioned table, or anything exotic, then use DBMS_METADATA.
David Aldridge
+11  A: 

If you're looking a way to find the exact DDL to recreate the table, including the storage clause, you can use

select dbms_metadata.get_ddl('TABLE', 'TABLE_NAME', 'SCHEMA_NAME') from dual

as described here.

Salamander2007
Nice find. Hadn't seen that before. Any idea what grants you need to run it?
BQ
I don't have access to Oracle box currently, so I can't test for sure.
Salamander2007
No problem. Just asking out of curiosity rather than need.
BQ