How to format all SQL objects in your database

The Format SQL objects feature allows users formatting one or more database objects with the specified formatting profile, without having to script them first.

There are three ways to invoke the Format SQL objects feature:

  1. From the ApexSQL Refactor menu, under the Other formatting sub-menu , select the Format SQL objects command:
  2. It can be initiated by using the “Ctrl + Shift +D “keyboard shortcut

  3. Right-click on a database in Object Explorer or Server Explorer, select the Format SQL objects command:

Before initiating this feature, a database from which objects will be formatted needs to be selected. Otherwise, if the Format SQL objects feature is initiated, the following message will be shown:

Quick tip:

Before initiating the process of formatting, make sure that you have the appropriate read/write permissions to manipulate the objects in a database. Otherwise, an empty list appears in the Format SQL objects window.

Initiating the feature opens the Format SQL objects window, with the list of all script based objects, e.g. stored procedures:

From the Profile drop-down list, choose a formatting profile and from the list choose desired objects that will be formatted.

To format all selected scripts, click the Create script button, which will open script in the Query editor, then click Execute button:

Additionally, before formatting, a SQL object’s script as it is now and how it will look like after formatting can be compared.

Right-click on the script object from the list and click the Compare button or use the “Ctrl + G” shortcut:

The Format SQL objects window allows users grouping of objects based on the name of the selected column. Drag a column header to the Drag a column header here to group by that column section in order to group the items from the list, based on one or more columns:

Q: What do the icons in the “OK” column mean?

A: A red cross mark icon indicates that these objects are not formatted for the selected profile from the Profile drop-down list and green check mark icon indicates that the objects are already formatted with the selected profile:

Q: Why can’t I select certain rows for formatting?

A: Only objects that were not previously formatted with the selected profile in the Profile drop-down list can be checked/unchecked. Objects that were previously formatted with the selected profile from Profile drop-down list cannot be checked or unchecked.

Q: I formatted some objects with a specific profile already. What happens if I then change the profile?

A: These objects will show as not “OK” the next time the objects are scanned and will need to be re-formatted.

 

January 25, 2016