Generating SQL Statements
In DatabaseSpy, you can generate SQL statements from existing objects displayed in the Online Browser and Design Editor. You can also type SQL statements directly in the SQL Editor.
The objects from which you can generate SQL statements and extract information such as name and path are listed below:
•Tables 
•Columns 
•Keys 
•Indexes 
•Triggers 
•Views 
•Procedures 
•Functions 
To run SQL statements, click Execute (F5) in the SQL Editor's toolbar.
Generating SQL statements from Online Browser
To generate an SQL statement from the Online Browser, do one of the following:
•Drag a database object from the Online Browser into an SQL Editor window. If the SQL Editor is not open, click the SQL Editor toolbar command. One statement per table will be generated if you select objects from different tables. By default, a SELECT statement is generated. You can specify the kind of statement that should be generated for any database object in the Online Browser options.
•Right-click a database object in the Online Browser, select Show in New SQL Editor, and click the relevant statement in the context menu.
You can define various options that determine how generated SQL statements should appear in the SQL Editor. For more information, see the SQL Generation and SQL Formatting options.
Generating SQL statements from Design Editor
To generate an SQL statement from the Design Editor, take the following steps:
1.Right-click a database object in the Design Editor.
2.Select SQL and Data | Show in New SQL Editor from the context menu.
3.Select the relevant SQL statement to generate.
Supported SQL statements
The table below summarizes the SQL statements you can generate and the information you can extract from database objects (such as name and path). You can edit generated SQL statements as required.
The list of supported statements may vary depending on the database kind.
Statement | Description |
|---|---|
SELECT | Generates a SELECT statement that retrieves data from:
•All columns of the source table •The selected column(s) of the parent table •All columns that are included in a view
|
SELECT OFFSET/LIMIT | Returns only a specific portion of the query results. Instead of retrieving every matching row, you can skip a number of rows and then limit how many rows are returned.
•The OFFSET clause specifies how many rows to skip before returning results. •The LIMIT clause specifies the maximum number of rows to return. This number is defined in the Buffered amount (rows) property in the SQL Editor Options.
Some vendors require ORDER BY for OFFSET/LIMIT, which will be added automatically in DatabaseSpy.
The command is disabled for vendors that do not support OFFSET/LIMIT.
|
Name | Returns the name of the selected database object. You can also select several objects. The names are printed in individual lines, separated by commas.
|
Path | Returns the full path of the selected database object (for example, Database.Schema.Table.Column). You can also select several objects. The paths are printed in individual lines, separated by commas.
|
CREATE | Generates a CREATE statement. Depending on the selected database object, you may have to modify the statement as follows:
•Index: Edit the index name and properties, and specify the column for the new index. •Trigger: Edit the trigger name and table that invokes the trigger, and specify the action to be triggered. •Procedure: Edit the procedure name and parameters, and adapt the procedure body as required. •Function: Edit the function name and body, and add or remove parameters if required.
|
DROP | Depending on the database object, this command generates a DROP or ALTER statement that deletes the selected database object from the database.
|
ADD | Generates an ALTER TABLE ... ADD ... statement that adds one of the following:
•A new column to the parent table of the selected column, using the name and definition of the selected column as initial values •A constraint, using the properties of the selected constraint as initial values
|
ALTER | Generates an ALTER TABLE ... ALTER ... statement that allows you to edit the properties of the selected database object.
|
RENAME | Generates an ALTER TABLE ... RENAME ... statement that enables you to rename the selected table, column, or view.
|
INSERT | Generates an INSERT statement for the selected table, column, or view. Specify the values to be inserted. If you insert data into individual columns, or if the primary key is not included in the selected view, you must also specify a value for the primary key unless it is generated automatically.
|
UPDATE | Generates an UPDATE statement for the selected table, column, or view. Provide values for the columns to be updated. If you do not add a WHERE clause to the statement, the specified values will be applied to all rows.
|
DELETE | Generates a DELETE statement for the selected table or view:
•For a selected table, the statement includes a WHERE clause based on the table's primary key to specify the row to be deleted. •For a selected view, the statement deletes data from the parent table of the view. If you remove the WHERE clause, the statement will delete all rows from the table.
|
Execute | Declares the parameters needed for execution and creates an EXEC statement that executes the stored procedure with the required parameters.
|