Generating SQL Statements
In DatabaseSpy, you can automatically generate SQL statements based on existing objects displayed in the Online Browser. You can either drag a database object from the Online Browser into an SQL Editor window, or right-click a database object in the Online Browser and use the context menu. The objects from which you can generate SQL statements (or extract information such as name and path) are as follows:
•Tables 
•Columns 
•Keys 
•Indexes 
•Triggers 
•Views 
•Procedures 
•Functions 
The following table shows which SQL statements can be generated (or information be extracted into the SQL Editor) for each database object type, using SQL Server 2016 as an example.
Select | | | - | - | - | | - | - |
Name | | | | | | | | |
Path | | | | | | | | |
Create | | - | - | | | | | |
Drop | | | | | | | | |
Add | - | | | - | - | - | - | - |
Alter | - | | - | | | | | |
Rename | | | | | - | | | |
Insert | | | - | - | - | | - | - |
Update | | | - | - | - | | - | - |
Delete data | | | - | - | - | | - | - |
Execute | - | - | - | - | - | - | | - |
SQL Server 2016
| Note: | The list of supported statements may vary depending on the database kind. |
Select | Creates a SELECT statement that retrieves data from (i) all columns of the source table, (ii) the selected column(s) of the parent table, or (iii) all columns that are included in a view. Basically, this is the same procedure as when selecting Retrieve data from the context menu, however the query is not automatically executed in this case. |
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 based on the selected database object. Depending on the selected database object, you have to edit this statement as follows:
•Edit the index name as well as the properties, and enter the appropriate column to create a new index for the parent column of the selected index. •Edit the trigger name as well as the table that invokes the trigger, and enter the action that is to be triggered. •Edit procedure name and parameters, and adapt the body accordingly to create a new stored procedure. •Edit the function name as well as the body of the function. Define additional or remove parameters, if required. |
Drop | Creates a DROP or ALTER statement, respectively, that deletes the selected database object from the database. |
Add | Generates an ALTER statement that (i) adds a new column to the parent table of the selected column and uses the name and definition of the selected column as default values, or (ii) adds a constraint and uses the properties of the selected constraint as default. The default values have to be edited. |
Alter | Generates an ALTER statement that allows you to edit the properties of the selected database object. |
Rename | Allows you to rename the selected table, column, or view. Change this parameter to the desired new table name. |
Insert | Allows you to insert data into the selected table, column, or view. By default, no default values specified. You must edit the statement and specify the values that are to be inserted into the table. When inserting data into individual columns, or if the primary key is not included in the view, you must also specify a value for the primary key unless an IDENTITY statement has been used for auto-generation of a primary key when the table was created. |
Update | Creates an UPDATE statement that updates (i) all columns of the selected table, (ii) the selected column, or (iii) the columns that are included in the selected view. No default is specified for the new values. Note that this command will update all rows with the same values if no WHERE statement is added to select a specific row. |
Delete data | Generates a DELETE statement that deletes data (i) from the selected table and adds a WHERE clause using the table's primary key to specify the row to be deleted, or (ii) from the parent table of the selected view. Note that this command will delete all rows of the table if no WHERE statement is added. |
Execute | Declares the parameters needed for execution and creates an EXEC statement that executes the stored procedure with the required parameters. |
Generating SQL statements with drag and drop
When you drag a table or column from the Online Browser and drop it into an SQL Editor window, a SELECT statement is generated by default. The kind of statement that should be generated for any database object is configurable in the Online Browser options. For example, you can configure that dropping a table into the SQL editor should generate an UPDATE statement, not a SELECT.
To generate SQL statements using the default configured statement type:
•Drag and drop one or several database objects from the Online Browser into an existing SQL Editor window. An SQL statement appears in the SQL Editor. Note that one statement per table will be generated if you select objects from different tables.
You can also right-click a database object and drag it into an open SQL Editor window. In this case, when you release the mouse button, a context menu opens from which you can choose the desired statement type to be generated (for example, SELECT, INSERT, UPDATE, etc.).
To generate SQL statements of specific type:
1.Right-click one or several database objects in the Online Browser and use drag right to drop the object(s) into an existing SQL window.
2.Select from the context menu the type of SQL statement you want to generate.
You can define several additional options that determine how the generated SQL statement appears in the SQL Editor. In the example below, the Append semi-colons to statement end has been activated in the SQL Generation options. This causes the semi-colon character to appear at the end of the statement. Also, the Enable SQL Formatting for the SQL Editor has been deactivated in the SQL Formatting options. This causes the FROM keyword to appear on the same line as the SELECT keyword (if SQL formatting had been enabled, a line break would have been automatically inserted before the FROM keyword).
Generated SELECT statement
Generating statements in a new SQL Editor window
If you want your SQL statements to be generated in a new SQL Editor window, do the following:
1.Right-click a database object in the Online Browser and select Show in new SQL Editor from the context menu.
2.From the sub-menu, select the appropriate statement you want to use (for example, SELECT, INSERT, etc).