Restructure Your Tables and Save All Updates in One Script

Did you know you that with the Flex2SQL Utility you can restructure all your tables and save your updates in one script that you can give to a DBA so modifications can be propagated to client sites?

The Flex2SQL Utility contains commands to generate SQL scripts to:

• Create SQL tables, including any constraints and indexes
• Analyze the SQL database after a text file load and adjust the RECNUM values
• Initialize auto increment columns (in PostgreSQL) or sequences (in Oracle)
• Enable or disable a trigger so that inverse key values are properly updated
• Drop objects such as sequence definitions on Oracle or an index or table in any SQL database engine
• Restructure SQL tables

To restructure your tables and save all the updates in one script

1.   Launch the Flex2SQL Utility and select the tables you want to modify in the Filelist dialog box.

2.   Select Maintenance > Restructure Table from the menu bar. The Restructure Table dialog box displays. The fields, indexes, and table properties for the first table you selected are shown. The table name displays at the top of the dialog box.

3.   Make the required table changes using available buttons and controls. Refer to the Flex2SQL Help for instructions. The Backordered field was added to the Invt table in this example.

4.   After you have made all the modifications to this table, click Next. The next table you selected is available for update.

5.   Make the required changes. The Comments field was deleted from the Customer table in this example.

6.   After you are done, click Next. When all your selected tables have been modified and you click Next, the Restructure Tables dialog updates with four tabs that show your changes.

• SQL Script tab: Shows the script(s) that can be submitted to the SQL Server to propagate the updates to client sites. Click Save to to save the script for the currently selected table. Click Save All in one script to save all the scripts in one file (see step 7 below).
• INT File tab: Displays the new INT file for the selected table and allows you to save it.
• Log tab: Lists the operations you performed and allows you to save them in a log file.
•  Source code tab: Lists the DataFlex restructure code for the operations you performed and allows you to create a src file containing the restructure code (click Settings) or to save the code in an INC file (click Save).

7.   Click Save All in one script in the SQL Script tab. You are prompted to save your updates in mdsrestruct.sql.

8.   Browse to select the location for this script and save it is using your selected filename. A simple SQL script, generated using the above examples, is shown below:

USE master
GO
ALTER TABLE "dbo"."Invt" ADD " Backordered" VARCHAR(1)
GO
USE master
GO
ALTER TABLE "master"."dbo"."Customer" DROP COLUMN "Comments"
GO

 Note: If you click Commit at the bottom of the dialog box, the changes are made on the server and the DataFlex .FD and .INT files are generated. The dialog box closes and scripts and source code are no longer available.