Refactoring T-SQL Codes With SQL Complete

The new era of T-SQL coding starts when the SQL Complete tool from Devart is added, as an add-in, to the SQL Server Management Studio or Microsoft Visual Studio. See how easy our coding experience is with the different types of code completion scenarios.

Recall that dbForge SQL Complete is an advanced-as-you-type SQL IntelliSense tool that helps increase the coding speed due to the advanced SQL code autocompletion, smart formatting, and refactoring experience offered by this tool.

In this article, we will see how we can use dbForge SQL Complete in the SQL code refactoring.

But, before start using the SQL Complete add-in, make sure that it is enabled. This can be achieved by opening the SQL Complete menu and choosing the Enable Code Completion option if not enabled yet.

Rename Automatically Generated Aliases

When a query is generated using the dbForge SQL Complete tool, aliases will be included automatically for the tables, views, synonyms, and TVFs. 

Find that the generated alias does not match your company's coding style. You can easily change all occurrences of that alias easily, without causing any conflict when the name of the alias is similar to any column/ variable name or part of it.

To rename an alias in a SQL query:

1. Place the pointer over the alias name to be renamed.
2. Right-click on the alias and select Rename option. Alternatively, you can choose the Rename option from the SQL Complete task menu.
3. Although the places where the alias name will be changed are surrounded by a dotted square, you can preview the code changes before applying the changes by clicking on F2.
4. Provide the alternative name for the alias and click tab/enter to apply the new name for all alias occurrences in the query.

Below you can see an example of renaming the aliases in the SQL query using different ways:

The same procedure can also be used to rename the local variables and the SQLCMD variables in your T-SQL script.

Also, this Rename feature can be very helpful when you need to replace a specific database object name, such as a table, column, trigger, login… etc., in your code, making sure that you will be able to automatically find and correct all references to that object.

Semicolon Insertion

Having standards in your code will make it readable and editable by anyone working on it. Using semicolons at the end of each T-SQL statement properly is one of these methods that make the T-SQL code readable.

dbForge SQL Complete can be the best tool that helps us in such a mission. If you want to insert the missing semicolons at the end of each statement in your query:

1. Open your script in SQL Server Management Studio or Visual Studio.
2. Right-click on the query window that contains the script and chooses Insert Semicolons. You can achieve the same by choosing the Insert Semicolons option from the SQL Complete menu.

If you prefer using the shortcuts, just click on Ctrl+B and then Ctrl+C to insert all missing semicolons.

The quick demo below shows how to insert all missing semicolons from a stored procedure:

Convert EXEC to Script

dbForge SQL Complete provides us with the ability to inline the code of the stored procedure or the scalar function in the EXE statement that is used to execute it within the query window in SQL Server Management Studio or Visual Studio.

This feature is very helpful in replacing the stored procedure or the scalar function execution statement with the inline code and reviewing the underlying query that will be executed before being executed.

The Conver EXEC to Script feature can also be used to unwrap the parameterized queries executed within the sp_executesql statement and eliminate the apostrophes around that SQL statement, making it more friendly and easier to work.

To use the Conver EXEC to Script feature to inline a stored procedure or scalar function code or to unwrap a dynamic SQL into parameterized query within an execute statement:

1. Point the cursor to the name of the stored procedure.
2. Right-click on the query window and select the Convert Exec to Script option, and the stored procedure name will be replaced with its definition.

The demo below shows how to inline a stored procedure code within a SQL code:

Generate CRUD Procedures for a Table

CRUD operations are the CREATE, READ, UPDATE, and DELETE operations performed to process the data hosted in our tables. T-SQL developers write stored procedures to make it easier for the users to perform these operations on the database tables.

One of the most useful features in the dbForge SQL Complete tool is the Script Table as CRUD, which generates customizable stored procedures for the four CRUD operations:

·         CREATE stored procedure for inserting data into the table.

·         READ stored procedure for retrieving data from the table.

·         UPDATE stored procedure for modifying data in the table.

·         DELETE stored procedure for removing data from the table.

To generate the CRUD procedure for a specific table:

1. Browse the target table from the Object Explorer in SQL Server Management Studio.
2. Right-click on the table name and choose the Script Table as CRUD option from the SQL Complete menu.

The demo below is used to generate CRUD procedure using SQL Complete within SQL Server Management Studio for a database table:

Script Database Object

Suppose you have a simple or complex query that contains database objects, and you want to check the definition of these database objects. In that case, you need to search for these database objects under the database using the Object Explorer and check/ script the definition of that object from there.

dbForge SQL Complete makes this operation easier and quicker. From your query, you can easily script any database object as ALTER (for Stored procedures, views, functions, and triggers) or script these objects as CREATE (for the tables, schemas, synonyms…etc.).

To script a database object using dbForge SQL Complete as create or alter:

1. Select the database object to be scripted in your T-SQL query.
2. Right-click on that database object and select Script Object as CREATE/ALTER option.

You can achieve the same by using the Ctrl+F12 keyboard shortcut.

The demo below shows how to script a database table as created:

The demo below shows how to script a stored procedure as ALTER:

Stay tuned for the next article, in which we will discuss how to use dbForge SQL Complete for code formatting.