Managing Database Objects
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.
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.
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.
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.