SQL Insight, sometimes called "code completion" or "intellisense" is a powerful tool to help you creating your SQL statements.
In this topic:
If you don't like the automatic popup, you can turn this behaviour OFF in the Editors - SQL Insight section of the Preferences. You can always force it to popup by using the CTRL+SPACE shortcut.
Another editor feature that might help you to avoid typing code is the Code Template feature.
The SQL Insight tools helps you to quickly write an SQL statement, either by suggesting objects when appropriate or remembering column names, for example. Combined with Parameter Insight, this can be a very helpful tool.
Whenever a database item is required, a list of possible items will pop up and you can select from it either by using the mouse or the cursor keys on the keyboard, ENTER will accept the given entry.
(click image to expand)
An example popup for an SQL SELECT statement, items sorted by "object name"
List Sorting
Items in a list can be sorted in two ways, by "object name" of "qualified name", the latter is of use when you have a DBMS that supports multiple schemas or owners for objects, it will sort on schema or owner-name first, then by object name. If you sort by "object name", the schema or owner-name will be ignored.
The same list as above, but sorted by "qualified name", lists all items from schema "dbo" first, "HumanResources" next.
(click image to expand)
Same list of items as above, but sorted by "qualified name"
Availability
Comprehensive SQL Insight is available for SELECT, UPDATE, INSERT, DELETE, UPDATE OR INSERT, MERGE, REPLACE and EXEC(UTE) statements: columns, parameters etc will be listed.
Lists of object names are available for DROP and ALTER statements (eg: DROP TABLE list-of-table-names).
Selectable Stored Procedures or (Table) Functions are also available when appropriate.
Context
SQL Insight is context sensitive, it will recognize your SQL statement and show items accordingly. For example, take the below UPDATE statement, when a column item is required, it shows a list of columns.
(click image to expand)
SQL UPDATE statement
But the context sensitivity doesn't end there, if columns of multiple tables are allow, SQL Insight will show these, or, for example, in the second part of a UNION query, the WHERE-clause only accepts items from that part, see the screenshot below.
(click image to expand)
UNION query, context example
As you can see above, when you have an alias for an item, this is supported as well. SQL Insight will use the alias for the object name and also lists the original source for the items. In the above example, the alias "e2" is used for the "Employee" table.
When your database system supports derived tables or temporary views, these are parsed and added to the SQL Insight output.
Temporary view in SELECT statement can be used in SQL Insight
Before the list is available, you can enter part of an object name to show a shorter list, when the list is being displayed, entering more characters will filter the list to "drill down" to the object of your choice.
Filtering doesn't require you to enter the text "before the dot", eg the schema name, if you choose to start typing text that comes after the dot, all available items starting with that text, despite schema or owner-name, will be valid.
For database systems that support multiple schemas, typing the schema name before the list pops up automatically filters to the items from that schema only.
As you can see, filtering is made extra easy for you.
There's more power at your hands when writing JOIN statements. See the screenshots below.
(click image to expand)
"Join-completion" - the popup shows the possible JOIN combinations by displaying the foreign key constraints
Using the "enter" key will select the foreign key and complete the JOIN statement.
(click image to expand)
"Join-completion" - the user selected the first foreign key
You already know about "filtering after the dot" for object names, this works for schema objects, but also for table columns.
SQL Insight & Parameter Insight Combo
You can have SQL Insight and Parameter Insight at the same time, this can be useful when writing an SQL statement with a select-able Stored Procedure, for example.
(click image to expand)
SQL Insight shows the columns from the Products table, Parameter Insight shows the expected parameters
System tables in InterBase & Firebird
InterBase & Firebird have several system tables, it's names are prefixed by RDB$, TMP$ or MON$. When filtering, these prefixes are ignored, see the below movie for an example.