Altering data objects with the Data Object editor

You can use the Data Object editor to alter or modify data objects by generating and running a DDL script.

Before you begin

Before you can alter a data object with the Data Object editor, you must create a connection to a database.

About this task

There is limited support for preserving data when altering data objects with the Data Object editor. When you alter a table, the workbench will determine whether the modifications can be performed using an ALTER TABLE statement. If an ALTER TABLE statement cannot support the requested modifications, then the generated DDL will create a backup of the original table before creating the modified table. Additionally, when a backup is required, a statement to copy the backed-up data to the modified table will be created. This statement is intended as a template for you to modify as required to support the unique requirements of the existing data and table modifications.

Procedure

To alter data objects with the Data Object editor:

  1. In the Data Source Explorer (or the Object List for products that include this view), right-click a data object, and select Alter. If you are prompted to specify which editor you want to use, select Data Object Editor. The Data Object editor opens.
    Note: Other menu actions on this object and its children, such as Refresh, Drop, or Generate DDL, are disabled while the editor is open.
  2. In the Data Object Properties section of the editor, use the properties tabs to modify properties for the data object.
  3. Expand the Impacted Objects section of the editor, and select impacted objects from the tree to include in the generated DDL script.

    The objects that are displayed in the tree are potentially impacted by changes to the object that you are altering.

  4. Expand the DDL section of the editor, and click Preview DDL to preview the generated DDL script.

    When you are modifying a table and there is generated DDL that backs up the existing table, you might need to modify the DDL to copy the data from the backup to the altered table. The generated DDL should be used as a template.

    If you make additional changes in the Data Object Properties or Impacted Objects sections, click Preview DDL again to refresh the generated script.

    You cannot type directly in the DDL section of the editor. If you want to modify the generated DDL script, click Open with SQL editor to open the script in the SQL editor, where you can make changes and save the script to an existing data project. You can also use this option to save your changes to an SQL script that you can run later.

  5. After you are satisfied with the generated DDL script, click Run DDL in the Data Object Properties section.

Results

The generated DDL script is run on the database, and the data object and other impacted objects are modified according to the changes that you made in the editor. The Data Output view displays the results, including any errors that occurred. You can view the changes in each data object by highlighting the object in the explorer and opening the Properties view.

Feedback