Devart Blog

Would you like to change data types or other attributes for multi tables in one shot?

Posted by on October 23rd, 2009

Recently we’ve got a letter from a customer asking how to alter all float datatype to double datatype in one “Find and Replace all” shot. Good news! dbForge Studio can do this.  Let’s see how Find and Replace functionality,  Schema Compare tool,  and a database project of dbForge Studio will do their best to change the required properties at a heat.

1. Create a database project based on the existing database, i.g., the one where float data type should be changed to double. Do the following:

a. Select File ->New ->Project in the top menu. In the New Database Project Wizard check “Import schema objects from the existing database”.

New Database Project Wizard

b. Select a required connection and a schema to import objects from.

c. Check “Import all objects into a single file” and click the Create button.

Import all objects into a single file

2. Use Find and Replace functionality to replace float with double in required columns. Do the following:

a. In the Project Explorer window, double-click your_datbase_name.sql file to open the file with the database DDL.

Double click to open the file

b. In the opened document, press Ctrl+F to open the Find and Replace dialog box. Fill in the Find what and Replace with fields with the data types. In the Look in field, select “Current Document”.

Find and Replace

c. Press either the Replace or the Replace All button. When replacement is completed, save the changes by pressing Ctrl+S.
3. Compare a live database with the database project. Do the following:
a. In the Project Explorer window right-click the top node and select New Schema Comparison from the menu.

New Schema Comparison

b. When the  wizard opens, select your database project as Source and the live database as Target. Press Compare.

New Schema Comparison Wizard

4. Synchronize the changes in databases using the Schema Synchronization wizard. Do the following:

a. Press the Show Synchronization Wizard button on the Comparison toolbar.

Show Synchronization Wizard

b. In the wizard, select Execute the script directly against the Target database. (It is recommended to backup your database before synchronization.) Press Synchronize.

Synchronize

5. View the changes in the grid of the scomp file. Do the following:

a. After synchronization press the Refresh button on the scomp file toolbar or F5.
b. Review the tables the changes.

Review the tables the changes

Download a fully-functional version of dbForge Studio and try yourself!

Leave a Reply