SQL Insight

Previous  Index  Next  How To Use

SQL Insight, sometimes called "code completion" or "intellisense" is a powerful tool to help you creating your SQL statements.


In this topic:

1.SQL Insight Basics


3.Join Completion

4.Advanced Tips


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.



SQL Insight Basics

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)

A list of items, sorted by object name

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)

A list of items, sorted by schema name first, then object name

Same list of items as above, but sorted by "qualified name"



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.



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)

Update statement, list of columns

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, the second WHERE clause only accepts items from the second part

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.


A Temporary View in a SELECT statement

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.

Click here for a sample video


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.

Click here for a sample video


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.


Click here for a sample video


As you can see, filtering is made extra easy for you.


Join Completion


There's more power at your hands when writing JOIN statements. See the screenshots below.


(click image to expand)

JOIN completion, before

"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, after

"Join-completion" - the user selected the first foreign key


Advanced Tips

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 & Parameter Insight combination

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.


Click here for a sample movie