Tip #34 – Using the Data Dictionary Differentiator

Advantage Data Architect includes a tool which helps in generating SQL Scripts for modifying database objects in the Data Dictionary called the Data Dictionary Differentiator. You can open this tool by choosing Compare Dictionaries or pressing ctrl-L. When the tool opens you will get a screen which allows you to select the dictionaries you wish to compare.

You can also select items to exclude for the comparison by pressing the Exclude Objects button. This is useful if you only want to include changes for particular objects or if you know you have only changed Views for example.

Once the tool has compared the data dictionaries a treeview displaying the differences will be shown. From this screen you can generate SQL scripts to convert from one version of the dictionary to the other.

In my example the tool generated the following code to convert HandsOn to SampleDB (two versions of my training database). Notice that the script is not quite complete since the tool cannot get the user password so you must add this to the script.

EXECUTE PROCEDURE sp_CreateUser 

   ( 'chrisf', '' /* YOUR PASSWORD GOES HERE */,

     'My admin account' );

EXECUTE PROCEDURE sp_ModifyUserProperty 

   ( 'chrisf', 'ENABLE_INTERNET', 'TRUE' );

EXECUTE PROCEDURE sp_CreateGroup 

   ( 'Managers','Company managers group' );

EXECUTE PROCEDURE sp_AddUserToGroup 

   ( 'chrisf',   'Managers' );

ALTER TABLE MaritalStatus

     ALTER COLUMN Code Code Char( 1 ) POSITION 2

     ALTER COLUMN Status Status CIChar( 50 ) POSITION 1;