Developer Toolkit: SQL Assistant
I work as a database developer (MS SQL) and most of my work is writing T-SQL code. Working in Query Analyzer, and then in Management Studio (2005, 2008, 2008R2), I really lacked the functions of the editor and additional functions of the development environment, aimed specifically at the process of writing code, building queries, etc.… Compared to MS Visual Studio or Embarcadero RAD Studio, what could Query Analyzer offer? Change the indent of a code block, change the case of selected text, comment out and cancel commenting of a code block, drag the name of a table or field (s) into the editor window, and script the selected object. But so lacked a full IntelliSense. All these convenient functions for autocompletion, interactive hints to the typed code, a choice from the list of objects, etc.
With the release of MS Management Studio 2005, the situation has not changed. And only in MS Management 2008 appeared Transact-SQL IntelliSense. Yes, the completion of the typed word appeared, providing a list of database objects and table fields, highlighting syntax pairs (begin ... end, ()). But before that, I began to use third-party SQL Assistant utility from SoftTree Technologies. Against the background of the possibilities provided by SQL Assistant, native IntelliSense is just oak. Is it possible to get something more, because this is SQL, and not an object-oriented language? Can! In this article I would like to tell you about the SQL Assistant utility, or rather about the function and the techniques that I use when writing SQL-code.
SQL Assistant - toolkit for developers and database administrators. It can be used when working on projects of any complexity, from small databases to complex corporate systems. The software is ready for use immediately after installation and does not imply the suspension of current workflows. The toolkit increases the productivity of SQL developers and ensures high quality code. Including the package includes excellent tools for checking the correctness of the SQL syntax, mechanisms for completing syntax structures, tools for fast navigation through the program code and much more. Users will also have an integrated spell checker. Among other advantages of the product it is worth mentioning the support of the most common DBMS (Oracle, SQL Server, DB2, MySQL, Sybase ASE, Sybase ASA, MS Access and the corresponding SQL dialects).
SQL Assistant is embedded in Management Studio and all its commands are available through the main menu item or through the context menu of the code editor.
')
Here are the main functions:
Basic information can be found on the official website and in the documentation (links at the end of the article). And here I want to talk about the most important function of this utility, the assistant itself - the context-sensitive pop-up window that appears in the code editor.
The assistant provides assistance depending on the place where he was called, starting from prompts for autocompletion of typed words and ending with building relationships between tables. Writing a query with a bunch of tables takes much less time. You will not make a mistake in the name of the tables and fields, there is no need to remember the primary key fields and foreign key fields to build relationships. You only need to specify the names of the tables from the list, and SQL Assitant will finish the join construct based on the selected foreign key.
I start writing a query from the from section. The name of the first table is specified, I dial the inner join and the assistant in the window shows a list of tables for which foreign keys exist from the Product table. Red highlights the fields by which the two tables will be joined. I select the connection to the ProductInventory table and get the code:
Then I continue building the query, dial the inner join and the assistant shows a list of tables for which foreign keys already exist from the ProductInventory table, but connections from the Product table are also available.
Building links works exactly in the from section, i.e. and for select, insert, delete, update. If you need to build a connection between tables that are not linked by a foreign key, then you need to specify these tables, and SQL Assistant can suggest building a connection using fields with the same name.
Received request code:
Be brought to the desired view using the format function. Code formatting rules are customizable.
By moving the cursor to the select section, the assistant will show a hint for selecting the fields to be included in the selection from the tables listed in the from section. The same principle in any query section (where, group by, order by).
In addition to information about the type of the field, an icon in the form of a yellow key is displayed for the fields that make up the primary key, the green key is the foreign key field, and the lightning has an index built across the field.
Other objects from the current database of other server databases are also available in the list. If you select a field from a table that is not in the from section, it will be automatically added to the from section. It remains only to add the necessary conditions of communication.
In general, the following objects can be displayed in the list:
It all depends on the context of the helper call. So, if you write the insert Production.Product construction, then when you call the assistant, you can select the required fields of the table for which the insert statement is written.
But it is more convenient to use an assistant at the time of writing the operator. After I wrote the insert statement, the assistant prompts me to select a database object.
After I select the desired table, I will automatically receive this code:
Similarly it turns out and with the update operator. Automatically generated code with the same input sequence:
You can even declare variables based on the structure of the table. I type declare and select the Location table, as a result the code is generated:
Constructing a call to a procedure or function.
Even objects that are created or declared in code become available in the helper window. It takes into account the creation of temporary tables, the declaration of variables, including tabular ones.
If the code I meet a variable, then holding down the Ctrl key and clicking on this variable, I will move to the place where this variable was declared. But it is easier to bring the cursor to this variable in order to get a pop-up hint in which the type of the variable will be indicated. The table will display its structure and links for getting a script to create, quick viewing of the contents and getting additional information. Systemic
For the procedure - a list of parameters, etc. for other objects.
For built-in functions, a tooltip with a brief description of the function. A helper will help when entering parameters.
Here are the main functions that I use daily, working in Management Studio. This is not all the features of SQL Assistant. There are still many interesting features, features, pleasant things. But, I think that building links, a context list for selecting tables and table fields will already be able to ease the programmer's work by an order of magnitude. By the way, SQL Assistant keeps statistics on how many characters the user has entered and how many characters the assistant has added, and then the percentage of productivity increase is calculated from this data.
Official website of SQL Assistant
Documentation (PDF, 7.2Mb)
With the release of MS Management Studio 2005, the situation has not changed. And only in MS Management 2008 appeared Transact-SQL IntelliSense. Yes, the completion of the typed word appeared, providing a list of database objects and table fields, highlighting syntax pairs (begin ... end, ()). But before that, I began to use third-party SQL Assistant utility from SoftTree Technologies. Against the background of the possibilities provided by SQL Assistant, native IntelliSense is just oak. Is it possible to get something more, because this is SQL, and not an object-oriented language? Can! In this article I would like to tell you about the SQL Assistant utility, or rather about the function and the techniques that I use when writing SQL-code.
Theory
SQL Assistant - toolkit for developers and database administrators. It can be used when working on projects of any complexity, from small databases to complex corporate systems. The software is ready for use immediately after installation and does not imply the suspension of current workflows. The toolkit increases the productivity of SQL developers and ensures high quality code. Including the package includes excellent tools for checking the correctness of the SQL syntax, mechanisms for completing syntax structures, tools for fast navigation through the program code and much more. Users will also have an integrated spell checker. Among other advantages of the product it is worth mentioning the support of the most common DBMS (Oracle, SQL Server, DB2, MySQL, Sybase ASE, Sybase ASA, MS Access and the corresponding SQL dialects).
SQL Assistant is embedded in Management Studio and all its commands are available through the main menu item or through the context menu of the code editor.
')
Here are the main functions:
- Code Assistant
- Code navigation (code structure, document map)
- Code formatting
- Patterns and code generators
- Refactoring functions
- Interactive SQL Input Help System
- Getting the script of the object (the script to create a table, procedures, functions, etc.)
- Quick table view
- Scripting, import, export data
- Separate SQL Execution
- SQL Execution Scheduler (using Windows Scheduler)
- Test Data Generator
- Unit Testing Framework
- Background syntax checking
- Query Performance Analyzer
- Integration with version control system
- Spell checker
- Code comparison
- Bookmarks
Basic information can be found on the official website and in the documentation (links at the end of the article). And here I want to talk about the most important function of this utility, the assistant itself - the context-sensitive pop-up window that appears in the code editor.
Practice
The assistant provides assistance depending on the place where he was called, starting from prompts for autocompletion of typed words and ending with building relationships between tables. Writing a query with a bunch of tables takes much less time. You will not make a mistake in the name of the tables and fields, there is no need to remember the primary key fields and foreign key fields to build relationships. You only need to specify the names of the tables from the list, and SQL Assitant will finish the join construct based on the selected foreign key.
I start writing a query from the from section. The name of the first table is specified, I dial the inner join and the assistant in the window shows a list of tables for which foreign keys exist from the Product table. Red highlights the fields by which the two tables will be joined. I select the connection to the ProductInventory table and get the code:
SELECT
FROM
Production. Product p
INNER join Production. ProductInventory pi1 ON pi1. ProductID = p. ProductID
Then I continue building the query, dial the inner join and the assistant shows a list of tables for which foreign keys already exist from the ProductInventory table, but connections from the Product table are also available.
Building links works exactly in the from section, i.e. and for select, insert, delete, update. If you need to build a connection between tables that are not linked by a foreign key, then you need to specify these tables, and SQL Assistant can suggest building a connection using fields with the same name.
Received request code:
SELECT
FROM
Production. Product p
INNER join Production. ProductInventory pi1 ON pi1. ProductID = p. ProductID
INNER join Production. Location l ON l. LocationID = pi1. LocationID
INNER join Production. ProductReview pr ON pr. ProductID = p. ProductID
Be brought to the desired view using the format function. Code formatting rules are customizable.
SELECT
FROM
Production. Product p
INNER join Production. ProductInventory pi1
ON
pi1. ProductID = p. ProductID
INNER join Production. Location l
ON
l. LocationID = pi1. LocationID
INNER join Production. ProductReview pr
ON
pr. ProductID = p. ProductID
By moving the cursor to the select section, the assistant will show a hint for selecting the fields to be included in the selection from the tables listed in the from section. The same principle in any query section (where, group by, order by).
In addition to information about the type of the field, an icon in the form of a yellow key is displayed for the fields that make up the primary key, the green key is the foreign key field, and the lightning has an index built across the field.
Other objects from the current database of other server databases are also available in the list. If you select a field from a table that is not in the from section, it will be automatically added to the from section. It remains only to add the necessary conditions of communication.
In general, the following objects can be displayed in the list:
- local variables;
- temporary tables;
- permanent tables;
- representation;
- procedures;
- functions;
- synonyms;
- scheme;
- Database;
- built-in functions.
It all depends on the context of the helper call. So, if you write the insert Production.Product construction, then when you call the assistant, you can select the required fields of the table for which the insert statement is written.
But it is more convenient to use an assistant at the time of writing the operator. After I wrote the insert statement, the assistant prompts me to select a database object.
After I select the desired table, I will automatically receive this code:
INSERT INTO Production. Location
(
- LocationID - this column value is auto-generated
Name,
CostRate,
Availability
ModifiedDate
)
VALUES
(
/ * {Name} * / ,
/ * {CostRate} * / ,
/ * {Availability} * / ,
/ * {ModifiedDate} * /
)
Similarly it turns out and with the update operator. Automatically generated code with the same input sequence:
UPDATE Production. Location
SET
- LocationID =? - this column value is auto-generated
Name = ?,
CostRate = ?
Availability = ?,
ModifiedDate = ?
You can even declare variables based on the structure of the table. I type declare and select the Location table, as a result the code is generated:
DECLARE
@LocationID SMALLINT ,
@Name name,
@CostRate SMALLMONEY ,
@Availability DECIMAL ( 8 , 2 ) ,
@ModifiedDate DATETIME
Constructing a call to a procedure or function.
Even objects that are created or declared in code become available in the helper window. It takes into account the creation of temporary tables, the declaration of variables, including tabular ones.
If the code I meet a variable, then holding down the Ctrl key and clicking on this variable, I will move to the place where this variable was declared. But it is easier to bring the cursor to this variable in order to get a pop-up hint in which the type of the variable will be indicated. The table will display its structure and links for getting a script to create, quick viewing of the contents and getting additional information. Systemic
For the procedure - a list of parameters, etc. for other objects.
For built-in functions, a tooltip with a brief description of the function. A helper will help when entering parameters.
Conclusion
Here are the main functions that I use daily, working in Management Studio. This is not all the features of SQL Assistant. There are still many interesting features, features, pleasant things. But, I think that building links, a context list for selecting tables and table fields will already be able to ease the programmer's work by an order of magnitude. By the way, SQL Assistant keeps statistics on how many characters the user has entered and how many characters the assistant has added, and then the percentage of productivity increase is calculated from this data.
Links
Official website of SQL Assistant
Documentation (PDF, 7.2Mb)
Source: https://habr.com/ru/post/141084/
All Articles