Note: This blog is based on ApexSQL Refactor, download it and play along with is this blog post.
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:
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 (http://blog.sqlauthority.com)