I started working with SQL Server, and MS SQL Server Management Studio about a year ago, and I quickly got frustrated with the View designer module. If you click on "New View" in the object explorer, or if you right click on an existing View and choose "Design," it opens up this multi-pane view designer with a graphical representation of the View on top, followed by some table listing the View elements, followed by the SQL for the view formatted as nearly impenetrable blocks of code. For simple Views the View designer was fine, but for complicated Views I came to hate working with it. I especially hated the fact that if I tried to format the SQL in a more readable layout, the View designer would throw out my formatting the next time I opened the View in Design mode.
I finally decided that there had to be a better way, and thanks to some discussions on Stack Exchange (natch) I pieced together the following:
- You can edit a View in plain SQL, and have your layout and formatting preserved when you save, by doing the following:
- Right click the View
- Select Tasks -> Script View as -> ALTER To -> New Query Editor Window
- Make your edits in the resulting Query Editor window
- Hit Execute when you are done to save your changes. The ALTER TO command replaces the current version of the View with your edited version.
- You can quickly clean up the brain dead SSMS formatting of the SQL of existing Views using the free poorsql.com website. You block and copy the ugly SQL into the window, choose from various formatting styles, and then the site instantly prepares a properly formatted version of the SQL.
