Updated Table Names, Update Views with new table names

Toad for Oracle
1

Hey all,

I am going to rename multiple table name in Toad for Oracle and I am concerned that the views (about 20+) will not work because they may point to the wrong table names. Is there a way to update them all at the same time so I do not have to go one by one into each view and do a find and replace?

Thanks,
Hank

2

Hi Hank,

The best thing I can suggest is:

  1. get a single script to create/replace all 20 views
  2. put that script in the editor
  3. rename your tables
  4. go back to your script and do your search/replace for each renamed table
  5. run the script
1 Like
3

The views will break. That is not a behavior of TOAD but of Oracle. You are pulling the foundation out from under them. The only way to handle that is as John suggests to "create or replace" your views as he describes.

Cheers,
Russ

2 Likes
4

Hey John,

Thanks for your insights. I am wondering if there is any ribbon available on Toad for Oracle Base (64 bit) to replace the new table names efficiently. Your feedback is appreciated.

Thanks,
Hank

5

Hi Hank,

The easiest way is to find the table in the Schema Browser, click on it, than hit F2 on your keyboard to rename.

-John

1 Like
6

Thanks for the reply. The F2 works! In addition, I am wondering if any tool/ribbon on Toad can replace the views including the new table names also.

Thanks,
Hank

7

For the views, you have 2 choices:

  1. double-click on the views one at a time to open the "alter view" window. Find/replace the table names in that window, then click OK

OR

  1. After renaming your all of tables, go to Schema Browser -> Views. Click the "Img" column to sort it, and select the ones with an 'X' icon. These views are invalid (presumably because of the renamed tables, if the weren't already invalid). Select them and then click "Create Script"

image

In the next window, choose "Editor as Script". Then click on the "Script Options" tab and check the option as shown below

image
image470×574 16.7 KB

Then go to the "Views" tab and check the options as shown below.

image
image488×567 11.2 KB

Then click OK and Toad will place the script to recreate these views in the Editor. There, you can do a CTRL+R to find/replace your table names in the script to recreate all of the views. When you are done, press F5 t run the entire script.

1 Like
8

Appreciate for your great insights!

1 Like