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?
Â
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.
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.
CREATE TABLE TableName1 AS SELECT * FROM TableName2 WHERE 1=0
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
Â
