Code completion function helps to look up and finalize table, views, column and stored procedure names during code development by popping up the available database objects, their attributes and context-dependent SQL keywords lists in any SQL Editor component. You can also create your own SQL templates for frequently used SQL statements and scripts and utilize them using code completion function. See User-Defined SQL Templates topic below for details.
You can invoke code completion in SQL Editor component by pressing CTRL+Space after partial object name is typed. For example, if you type characters 'cus' and then press Ctrl+Space, the popup selection menu will be opened in place that will list all objects which name starts with 'cust', such as customers, customer_orders, customer_location, etc. You can select required entry from the list or keep typing the letters of the required object name - it will automatically narrow down the selection list.
If you enter the table or view name followed by period character and then press Ctrl+Space, the selection list, which contains list of columns for the selected table or view will be displayed. You can also use aliases defined in FROM clause of SELECT table. For example, if you define alias 'myt1' for table 'customer' and then type 'myt1.' and press Ctrl+Space, the list of columns for table 'customer' will be displayed.
When you press Ctrl+Space after a space, the editor makes a guess on which list of keywords or objects to display based on a content of the statement in which it was pressed. For example, if you press Ctrl+Space in the beginning of the editor or after ';' separator, it displays the list of first-level SQL templates, such as SELECT * FROM/INSERT INTO VALUES/DELETE FROM/CREATE TABLE, etc. If you press CTRL+Space after space following UPDATE keyword, it displays list of tables. Pressing Ctrl+Space after EXECUTE IMMEDIATE produces list of stored procedures.
For each entry in the selection list, which pops up when you press Ctrl+Space, it displays the entry type icon : table, view, stored procedure, column or SQL keyword. For entry that is a column name, it also shows a column datatype and the table or view to which this column belongs.
You can define any SQL statement(s) or script that is frequently used, create a keyword for it and use this keyword in a code-completion function to substitute it with a full template definition.
For example, if you frequently use SELECT count(*) FROM statement, you can define this as a template, give it keyword ‘sc’ and simply type sc and CTRL+Space to replace it in the editor with SELECT count(*) FROM.
Another example of the templates usage is the stored procedure’s header block with comments containing copyright and other descriptive information which developers usually add to any stored procedure. You can define ‘copyright’ keyword for this template that will insert entire formatted comments section into the SQL editor.
Server Studio comes with a set of predefined templates for all commonly used SPL stored procedure constructs. You can edit these templates to customize for your own needs.
Template keywords work similar to standard SQL keywords in code completion.
When you type only partial name of template keyword and press Ctrl+Space, the full keyword name will be displayed in code-completion list allowing you to choose it from a list. But when you type a full template keyword name and press Ctrl+Space, the full template is immediately inserted into the editor.
Template keyword icon in the code-code completion list is different from a standard keyword icon.
Templates are stored in a text file that can be shared between multiple developers on a network allowing to maintain department-level SQL code standards. Use Tool -> Preferences menu and SQL Template node to change location of the template file.
You create new and edit existing templates in SQL Templates Manager window. To open SQL Templates Manager, select Edit -> SQL Templates top-level menu, or choose Generate SQL Templates -> User-Defined SQL Templates from SQL Editor popup menu.
To create a new template, press Add Template button, enter template keyword that you will be used to insert this template in a code-completion function and enter template’s statement or script.
You can use a special tag - !cursor - when creating a template text to specify a place in a template where you want to put a cursor when template text is inserted into SQL Editor. For example, to place a cursor after a keyword SELECT in a template 'SELECT FROM WHERE', enter the following template text: SELECT !cursor FROM WHERE