Copying schema of table to another table?

Asked By 0 points N/A Posted on -

I have a situation in which I have to copy the schema of a table to another table in Oracle. I do not want to copy the data of that table. How can I copy the structure of a table without copying the data?

Best Answer by Terry Young
Best Answer
Best Answer
Answered By 0 points N/A #92788

Copying schema of table to another table?



To copy table structure only from one table to another table try this simple query:

CREATE TABLE table2 AS SELECT * FROM table1 WHERE 1 = 0;

Because of the WHERE clause (1 = 0) no data will be included.

However, this does not include indexes, constraints, and other objects.

If your table1 has constraints and indexes and needed to include them in table2,

you have to use the oracle's DBMS_METADATA package with GET_DDL function.

Here are the example query:

To retrieve the indexes:

SELECT DBMS_METADATA.GET_DDL('INDEX', <index_name>, <owner>)

FROM dba_indexes where rownum<2;

To retrieve the constraints of your table:

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', <constraint_name>, <owner>) FROM user_constraints WHERE table_name = '<your_table_name>'

Note 'owner' can be optional if you are the owner of the object. If not, you have to specify the owner.

Answered By 0 points N/A #92790

Copying schema of table to another table?


Copying table schema or structure is not a tough job and it can be done through a simple query. All you need to do is that run the following query while creating your database table through query.


In where clause of this query, 1=0 or any false condition can be used so that no rows are returned and only schema or structure will be populated in TableName1.

Important Note: Constraints of TableName2 will not be copied to TableName1 if you are using this method. You have to explicitly define the constraints on TableName1


Login/Register to Answer

Related Questions