When working as a SQL Server database administrator or developer, you must frequently interact with the database objects using SQL Server Management Studio. For example, you need to check the number of records in a specific table to check if the record is inserted correctly or to modify a specific record in the database table.
Performing such tasks requires browsing the target database under your SQL Server instance to reach the table you plan to check or change, then checking the list of columns and data types in that table. This is because you cannot recall the schema of all your database tables!
The same thing is required when trying to change the definition of a stored procedure, where you need to get the old definition of that stored procedure manually so that you will be able to modify it.
Why SQL Complete
You can take advantage of your time and effort in cooking a delicious meal for yourself instead of doing all these tasks manually.
With SQL Complete tool from Devart, an advanced SQL IntelliSense tool, you can enjoy writing T-SQL commands easily and focus more on the quality of your T-SQL code and making it more understandable. Leave it to SQL Complete to lead you through the syntax of the SQL Server queries and commands.
SQL Complete is an add-in that can be used within SSMS and Microsoft Visual Studio to increase your coding speed. It offers advanced SQL code autocompletion, smart formatting, and refactoring experience. SQL Complete provides as-you-type IntelliSense-style and code completion suggestions and a smart experience while navigating your T-SQL code.
Installing SQL Complete
After downloading it, you can install the SQL Complete add-in to your machine by clicking on the downloaded exe file that will start the straightforward installation wizard below.
In the Welcome page of the SQL Complete installation Wizard, click on the Install button to start the installation process:
After that, you will be asked to select the installation path for SQL Complete, where you have the option to use the suggested path or click on the Browse button to change it to a custom path. Click Next to proceed.
In the Add-in page, check the box beside the SQL Server Management Studio or Visual Studio version(s), to which the SQL Complete will be added, then click Next to proceed:
In the Startup Optimization page, choose the option that controls the performance of the SQL Complete add-in startup performance, where it is recommended to Generate images for all assemblies during the installation process. Then click Next to proceed:
You can track the progress of the installation process from the progress bar as shown below:
If the SQL Complete installation process is completed without any issue, the smart installation wizard will provide you with the indicative message, with the ability to start the SQL Server Management Studio tool directly with the SQL Complete Add-in ready for use:
Getting started with SQL Complete
When you start the SQL Server Management Studio, you will find a new SQL Complete menu bar added to it, with rich options that can be used to control its functionality, as below:
Before using the SQL Complete add-in, you need to ensure that this add-in is enabled, whereas the SQL Complete menu bar will provide you with an option to enable it in case it is disabled. In our scenario, we can see that the SQL Complete add-in is enabled and waiting to help us in our coding!
Coding Experience
The coding experience with SQL Complete is extremely easy and smart. When you start writing your T-SQL statement, SQL Complete provides you with suggestions based on the code you are typing in the SQL editor using the hint pop-up box.
In the example below, when you start typing SELECT, it will provide you with all suggestions for the statements that start with an S letter. Also, it will provide you with a list of tables under the connected database, with the ability to select the table name and the list of the columns to be returned from that table, and the query will be written automatically.
This is what I mean by drawing the query instead of writing it!
The same experience when trying to modify a stored procedure’s definition. When you start typing ALTER statement, SQL Complete will automatically complete the statement by providing suggestions within the hint box and the list of stored procedures within that database.
When you select the name of the stored procedure, just click the TAB button to get the complete definition of the stored procedure without the need to browse it manually within SQL Server Management Studio.
You can also open the Document Outline window from the SQL Complete menu, which helps display the structure of your script, allowing you to navigate through large SQL documents easily.
If you are interested in getting more information about any database object used in your query, just hover the mouse over that object and read the required information from the pop-up box, in addition to highlighting the occurrences of the database object or identifiers within your query, as shown below:
Also, if you need to check the definition of the database object, SQL Complete can provide you with this ability in an easy way, as you can see below:
Suppose you want to execute a stored procedure using the EXEC command. In that case, the stored procedure Parameter info tooltip will automatically appear, providing you with the list of parameters required to be provided to execute that stored procedure and the data type of each parameter, allowing you to easily execute that stored procedure, as shown below:
SQL Complete offers a set of predefined snippets that contain recurring code patterns, such as data selection statements, which you can use within your code, or implement new snippets and share it with the community, as follows:
By default, SQL Complete will write the column’s name in your T-SQL statement with an alias suggested by the add-in for your table. If you are interested in showing the qualified column name with the table name instead of the alias, you can change the default setting from the SQL Complete menu bar under advanced options as shown below:
SQL Complete makes reading data from multiple tables an easy mission. It allows you to draw the query by choosing from the suggested statements in a way that requires basic knowledge in T-SQL language, as follows:
If you are reviewing a large INSERT statement and want to determine which value corresponds to which column, and vice versa, SQL Complete helps you to easily locate the corresponding value for the current column, as shown below:
With all the discussed features for the SQL Complete add-in, we are still at the surface!
Stay tuned for the next articles, in which we will dig deeper into how to use the SQL Complete tool for code formatting, refactoring, and other operations on the result grid.
