Toad World® Forums

Copy Table Data and Structure

Is there a simple way, using the Toad interface and not SQL queries, to copy a table and all columns, indexes, constraints, triggers, etc.? I want to back up a table before altering it so that I can simply rename the back up to restore it if it causes site issues.

The best way to do this is make a copy of the whole database (or at least the schema), and use the copy for testing, but to answer your question…

There is no one-step method to do this, but it’s not very many steps and not very difficult.

Go to SB-Tables-Script. Click the first toolbar button to make sure that indexes, triggers, and everything else you want is included in the script. Make sure the “Include Drop Statement” option is UNCHECKED. Send the script to the Editor, and go through it change object names (including indexes, constraints, triggers) so there won’t be any clashes with current object names.

If your table doesn’t have any LONG columns, you can add this statement after the create table:

Insert into select * from <old_table>;

Then run your script.

If you have foreign keys that point to the table being rebuilt, you’ll have to recreate that manually, as I don’t think Oracle will let you have the same FK from one table to two tables.

There is a “Script” TAB in the schema-browser. Select table in LHS and the select tab - it will generate all requirements for creating the selected object.

I think by LHS you mean the ‘left hand side’ pane with the table listing. But I’m not sure what you mean by select tab. Please describe in more detail how you get to the screen with all the requirements for creating the new table.