![]() ![]() It can perform 'heavyweight' refactoring processes, such as renaming objects or splitting tables, which require a separate window or wizard to accomplish. SQL Prompt supports refactoring SQL objects and code in several ways. You'll notice that it is not quite the same, because some actions were included with the formatting and these have changed the code slightly. CREATE TABLE Pilot_Skills ( Pilot_Name CHAR(15) NOT NULL, Plane_Name CHAR(15) NOT NULL, PRIMARY KEY ( Pilot_Name, Plane_Name ) ) CREATE TABLE Hangar ( Plane_Name CHAR(15) NOT NULL PRIMARY KEY ) SELECT DISTINCT Pilot_Name FROM Pilot_Skills AS ps1 WHERE NOT EXISTS ( SELECT Plane_Name FROM dbo.Hangar WHERE NOT EXISTS ( SELECT Pilot_Name, Plane_Name FROM Pilot_Skills AS ps2 WHERE(Pilot_Name = Pilot_Name) AND (Plane_Name = Hangar.Plane_Name) ) ) Listing 3 ![]() Apply Unformat to Listing 2, and you'll end up with code that looks as shown in Listing 3. The Unformat item, found in the Actions list, will remove all formatting whitespace from the selected code. This displays a submenu that allows you to select a formatting style from any current styles. You can change the current style at any time from the Active Style menu item, accessible from the Context menu or Prompt menu. window, accessible from the Prompt menu that displays the Formatting styles dialog box. ![]() To change the way that formatting is done, there is the Edit Formatting Styles. In this case, you can prevent areas of code being formatted with the Disable formatting for selected text from the actions list, which adds comments before and after the selected text to prevent that block from being formatted. Occasionally, your code is laid out in a special way that mustn't be reformatted. We apply formatting using the Format SQL command, reached from the Actions List, Context menu, Prompt menu, or keyboard Ctrl (K, Y). There are several useful commands that allow us to apply and control code formatting in SQL Prompt. We'll cover Actions when we reach Refactoring Redoubt. This is because we can configure the Format SQL command to apply additional Actions, at the same time as applying the current formatting style. The sharp-eyed explorer will have noticed, in Listing 2, that as well as laying out the SQL nicely with spaces and line-breaks, Prompt has made some additional code changes it's added semicolons, and changed the case of the identifiers to be the same as in the initial CREATE statement. You can reformat any code that is error-free SQL Prompt must compile the code to format it, so it can't deal with any code with syntax that it can't understand. It is best to start with a style that works well enough and then to modify it to your taste as you go along. ![]() There are some built-in styles to try out, and it is easy to modify the way that they work. When we run the Format SQL command, SQL Prompt will format the SQL according to the layout defined by the current Active Style. WHERE(Pilot_Name = Pilot_Name) AND (Plane_Name = Hangar.Plane_Name) SQL Prompt refers to this as 'Applying the layout'. With a few keystrokes, to run SQL Prompt's Format SQL command, the code is formatted as shown in Listing 2. CREATE TABLE Pilot_Skills ( Pilot_Name CHAR(15) NOT NULL, Plane_Name CHAR(15) NOT NULL, PRIMARY KEY ( Pilot_Name, Plane_Name ) ) CREATE TABLE Hangar ( Plane_Name CHAR(15) NOT NULL PRIMARY KEY ) SELECT DISTINCT PILOT_NAME FROM PILOT_SKILLS AS PS1 WHERE NOT EXISTS(SELECT * FROM HANGAR WHERE NOT EXISTS(SELECT * FROM PILOT_SKILLS AS PS2 WHERE(PS1.PILOT_NAME=PS2.PILOT_NAME)AND(PS2.PLANE_NAME=HANGAR.PLANE_NAME))) Listing 1 Listing 1 shows some suitably impenetrable code (Thanks to Joe Celko for this). If we remove all formatting from a piece of code, it will continue to function exactly as before. Formatting changes are 'decorative' changes that affect only white-space, such as line breaks, tabs and spaces they do not change the meaning of that code in any way. SQL Prompt supports built-in and custom 'styles' that you can apply to the code in a query window, or any highlighted portion of that code, to format SQL code exactly as you like it.įormatting is all about taking some code and making sure that it is easy to read and understand. Within an SSMS results pane, the right-click context menu provides features to 'refactor' query results into code, as well as to export results to Excel, for reporting.Ī lot of Prompt treasure is also available via a secret Prompt keyboard shortcut – see the Prompt Functionality Finder for full details. Within SSMS object explorer the context menu will provide Tab Management functionality (if a server or database is highlighted), or refactoring functionality (if a database object is highlighted). SQL Prompt Treasure Island Skip to content ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |