Altova DatabaseSpy 2024 Enterprise Edition

Managing Database Objects

Home Prev Top Next

This chapter deals with viewing and modifying the structure of database objects. If you are looking to interact with data stored by database objects, see Retrieving and Editing Data.

 

In DatabaseSpy, the Online Browser is the primary way to navigate the database structure and explore database objects in a hierarchical fashion. You can also add new database objects or modify the existing ones using the context menu actions.

dbs_table_01

Online Browser

The Design Editor provides the means to view multiple tables and relationships between them as a diagram that you can save to a file. In Design Editor, you can configure the level of displayed detail and you can change the database structure as well.

dbs_design_editor

Design Editor

 

Designing Database Objects

You can create new database objects or change the structure of existing ones in any of the following ways, depending on your preferences:

 

Visually, from the Online Browser. This approach does not require writing SQL; it involves using context menu commands and editing object properties from the Properties window.

Visually, from the Design Editor. This approach also does not require writing SQL. The table structure displayed in the Design Editor window changes appearance as you make changes to it, and you can customize the level of displayed details.

SQL-based approach, from the SQL Editor. You can either generate Data Definition Language (DDL) statements from existing objects, write such statements from scratch, or perhaps run existing SQL scripts.

 

Note the following:

 

Various database-specific limitations apply when editing existing database objects from either the Design Editor or Online Browser, as further mentioned in this documentation. When that is the case, the corresponding commands are disabled, or appropriate notification messages are displayed by DatabaseSpy.

Any changes you make to the database structure (either from Online Browser or Design Editor) are not applied immediately but recorded in a change script which is displayed in the Database Structure Change Script window. To actually deploy the changes to the database, you must execute the change script.

In order to create or modify database objects successfully, your database user account must have the required server-level and database-level privileges.

 

Taking action on multiple database objects

From the Online Browser, you can take actions on multiple database objects, if permitted by the context. These actions include:

 

Dropping multiple tables

Dropping multiple columns

Deleting data from multiple tables or views

 

For example, to drop multiple tables in the same operation, right-click the "Tables" folder under your database or schema and select Drop Tables from the context menu. Likewise, to drop several columns from the same table, first select several columns using the Ctrl key pressed, right-click them, and then select Drop Column(s) from the context menu.

dbs_drop_multiple

The structural changes are not committed directly to the database. Instead, a change script is generated in the Database Structure Change Script Window, where you can review it before execution or revert it if necessary, see also Dropping Database Objects.

© 2017-2023 Altova GmbH