Hello, and welcome to this video presentation in which we'll introduce ApexSQL refactor, a SQL server Management Studio and Visual Studio add-in for formatting and refactoring SQL code with 15 code refactors and over 200 formatting options. It formats SQL, expands wildcards, fully qualifies object names, and safely renames SQL database objects and parameters without breaking dependencies. First, we start with formatting options. Let's paste some code in the Query Editor.
To format SQL code inside SSMS or Visual Studio, select Code in the query window. And from the Format SQL submenu, choose one of the four built-in formatting profiles. Another way to access the formatting profiles is via the ApexSQL refactor toolbar. You can modify the built-in formatting profiles, or create your own profile from scratch by defining the layout using the options from the options form, which is located under the ApexSQL refactor menu.
All options are grouped in tabs and subtabs. Let's create a profile. Click the New button and enter the name for the profile. Now let's change some formatting options in our profile. Let's add the Add Spaces Before Commas option. In the Capitalization tab, set the options to be in uppercase.
As you can see, all the changes we made can be seen in the code preview section. If we click on the Use Current SQL button above the code preview section, the code which is located in the query window will be captured and displayed in the code preview section. Now let's go to the Statements tab and set some additional options for our profile. After everything is set, click the Save button. As you can see, the newly created profile is added to the Format SQL list.
Now let's format SQL code in the query window. Another useful formatting feature that comes with ApexSQL refactor is Format SQL Objects. The Format SQL Objects allow formatting one or more database objects with the specified formatting profile without having to script them first. Let's select the database from which we want to format objects. Right click, and from the Context menu, choose Format SQL Objects, or from the submenu Other Formatting.
In the ApexSQL Refactor main menu, select the same option. This will open the Format SQL Objects form, which will list all script-based objects-- for example, stored procedures and functions. Let's, from the profile dropdown box, select our newly created profile, and uncheck all checkboxes, and choose the objects that we want to format. The red X under OK means that the object's format doesn't match the new formatting profile. Additionally, before formatting, we can compare a SQL objects script as it is now to how it will look after formatting.
Right click on the script object from the list, and click the Compare button, or we can use the Control plus G shortcut. On the left side of the form is the code not formatted, and the right side of the form shows us how the code will look after formatting. Another cool feature that comes with ApexSQL refactor is the wildcard expansion. This will replace the star-- wildcard-- with the list of the referenced column names. Let's type some query with the star wild card and click the wildcard expansion button. As you can see, the star is replaced with columns that are in the address table.
One more interesting and useful feature that can be found in ApexSQL Refactor toolbar is Qualify Object Names. Qualify Object Names fully qualifies all the objects referenced in your queries. Let's clarify this using examples. Paste some code in the Query Editor and click the Qualify Object Names button. As you can see, the Qualify Object Names added the name of the table in front of each column. ApexSQL Refactor provides us with a feature that finds unused variables and parameters in the scripts.
Let's open Procedure and add one parameter. Now, click the Unused Variables and Parameters button from the toolbar. After the process of finding unused parameters and variables is finished, the unused parameters will be underlined. You can mouse over them to see a tooltip that clarifies the parameter or variable.
One more very useful feature is Safe Rename. The Safe Rename refactor changes the name and the schema tables, views, stored procedures, functions, columns, parameters without breaking any dependencies. Let's now safely rename a table. Change the schema name and add a new name for the table. In order to see the changes on the table we've modified, let's refresh the database.
If everything went fine, we see the table with a new schema and a new name. The Add Surrogate Key option can replace a complex natural key with a simple surrogate key, thus keeping referential integrity, unifying key support for the whole database, and improving database refactoring performance. Let's select some table in the Object Explorer, and from the sub menu, Other Refactors. In the ApexSQL Refactor main menu, choose the Add Surrogate Key option.
As you can see, the Add Surrogate Key automatically generates a new name for the surrogate column name. But you can set the name of the surrogate column to whatever you want. For now, we will leave the automatically generated name in the surrogate column name box. Refresh the database, and let's see the results. As you can see, the natural key is replaced with the surrogate key.
ApexSQL Refactor has the Convert to Code option that provides a simple way to convert your T-SQL script into client-language specific syntax, like Java, C#, Perl, PHP, Delphi, C++, or other custom languages for which you have defined conversion rules. Let's paste some code in the Query Editor. Now, let's select this code, and from the ApexSQL Refactor main menu and, from the list, choose one of the languages. The SQL script, which has been converted into the selected language, is now stored in the clipboard.
Now, all that needs to be done is to simply paste it into the desired project. Another useful feature