SQL SERVER – How to Survive on Free Tools for Month – Enjoy Refactoring – ApexSQL Refactor

ApexSQL Refactor is a free SQL Server Management Studio and Visual Studio add-in for database refactoring and SQL code formatting. ApexSQL Refactor has 11 databases and SQL code refactors including:

• Smart rename – this database refactoring feature generates a SQL script which will change the object name and update all the dependent objects, without breaking dependencies. It applies to: procedures, functions, tables, and views, table/view columns and function/procedure parameters
• Split table – If you have some more frequently used columns and some less frequently in one table, you can apply this feature to split a table into two tables to achieve performance gains
• Add surrogate key – use this feature to replace the natural key with the surrogate key
• Change parameters – Change, add or remove stored procedure and function parameters without breaking dependencies
• Encapsulate as :
  • Stored procedure
  • Scalar Inline function
  • Table Inline function
  • View
• Expand wildcards – use this feature to be sure that you’ll avoid SQL mistakes as using SELECT * in your code and to list all wanted columns without the need of remembering all 40+ columns in your table
• Qualify object names – qualifying object names are important for avoiding code ambiguity and for performance gain in saving optimizers time and for reusing execution plans
• Replace One- to-many relationship with Associative Table

One-to-many relationship is a relationship between two entities in a database where one entity has a multivalued relationship with another. This relationship sometimes must be changed if there are some additional requests in database design. One approach is using association table. This can be achieved by using the ApexSQL Refactor’s Replace One- to-many relationship with Associative Table feature.

Selecting the table in the Object Explorer and choosing the Replace One-to-many Relationship option from the ApexSQL Refactor menu opens the Replace One to many relationship with the Associative Table dialog.

In the Associative table name text box enter the name of the association table and click on the Generate preview button. You can review the changes, and check warnings, dependencies and sequence of the script and changes to be done. You can choose to replace immediately by clicking the Replace now button, or click the Open script button to open the script in the query window.

Beside refactor features the tool also has another two useful features:

Copy SQL code as – to pack your SQL code in a format suitable for one of the offered programming languages​​ or make your own template. Default languages are: Java, VB.NET, C#, Perl, PHP, Delphi, Power Builder, Ruby, and C++

Find unused variables – to find the variable and parameters that are only declared, but never used

In addition to refactoring features there are more than 160 SQL code formatting options. The formatting options include parentheses, commas, capitalization, data statements, schema statements, expressions, a many more formatting options.

One great option is the separate option for formatting the JOIN statements. It offers various options of placing the JOIN object on a new line, placing ON clause on a new line with custom indentation, placing nested operations on a separate line, and more.

Best of all is that every change you make will be shown in the preview window marked the red. For example selecting the Place joined object on a new line option.

Another useful option is the possibility to format more SQL scripts and even folders containing SQL scripts at once. By choosing the Format SQL Scripts feature open the Format SQL files dialog and choose the folders and .sql files.

Enjoy refactoring your database and making your code pretty by any standard you follow and use the possibility to have more formatting templates at once. All that for free with SQL formatter and refactor from ApexSQL. You can read more about it over at ApexSQL Solution Center.

Reference: Pinal Dave (https://blog.sqlauthority.com)