Toad World Blog

Synchronization and Mapping of Renamed Table Columns

Sep 1, 2015 4:35:00 AM by Vaclav Frolik

Toad Data Modeler contains many powerful synchronization features, incl. model comparison, model merge and generation of change scripts. 
In addition to the complex features, there is another possibility how to quickly compare selected object with database and generate change script
just for the object. The key benefit of the single-object comparison is the possibility to map renamed tables or columns.

 

Example

Let's say I reverse engineered Oracle Database structure and renamed column SURNAME to LAST_NAME in table T_CUSTOMER.

Now I want to generate the ALTER TABLE <TABLENAME> RENAME COLUMN statement for the change.

 

Complex Change Script Generation - Wizard

If I run the complex change script generation wizard:

Whole the model will be compared with the database and all changes will be detected, however the renamed columns will not be mapped together:

The complex Change Script Generation Wizard is very powerful and allows you to generate full change script for various model modifications,
but there is even better feature for such simple scenarios. Let me explain what's necessary to do to map renamed columns.

 

Simple Change Script Generation

To generate change script for selected object, right click the object name in model explorer and select Change Script | Run...

Select existing database connection:

It is necessary to define set of objects that should be used for the comparison,
either select just the objects you modified, or click the first icon on toolbar to select all tables, view and other objects.

Compare Physical Properties only

And save the configuration as new Action Definition.

Change Script window opens. To map renamed objects, click button with three dots and select existing column from the list.

Then click Refresh and see the generated ALTER TABLE <TABLENAME> RENAME COLUMN statement.

Done!

If you later modify another object in your model, the stored "Videorental Change Script Generation" action definition will be available
and the configuration wizard will be skipped completely.

Good Luck!

 

Tags: Toad Data Modeler Data Modeling

Vaclav Frolik

Written by Vaclav Frolik

Interests:

  • UX design and graphics
  • Databases
  • Product / Project management
  • Web technologies
  • Online marketing

Favorites:

  • Music bands: Pearl Jam, Audioslave, RHCP, Midnight Oil, Alter Bridge...
  • Actors: Kevin, Edward, Jack, Jeff and my kids.
  • Actresses: Kim, Jennifer
  • Sports: MTB, Squash, Football (the one played with feet, not hands)
  • Series: IT Crowd, The Big Bang Theory