SQL Server Management Studio (SSMS) 2008 contains some minor but very welcome improvements to scripting. In this post I’ll highlight the ones I’ve noticed so far.
Object Explorer
In Object Explorer you can right click almost anything and chances are there’s a menu option for Script [OBJECT] as with submenu options for CREATE To, ALTER To, DROP To, etc.. In SSMS 2005 there are three choices available: New Query Editor Window, File…, and Clipboard. The screenshot below shows an example of these options when scripting a table:
SSMS 2005 "Script [Object] as" menu options
The screenshot below shows the same menu options in SSMS 2008. In addition to new eye candy – icons for each option – there is a new option to script to an Agent job. Choosing this option opens a new window which allows you to schedule a one time SQL Agent job that will perform the scripted action.
There’s also a new action to DROP And CREATE the object. My typical usage pattern when scripting individual objects in SSMS 2005 was to script the drop to a new window, then script the create to the clipboard and paste it into the query window I just created for the drop. The new action saves me the extra clicks and makes for a good user experience.
SSMS 2008 "Script [Object] as" menu options
Generating Replication Scripts
Some details about publications and subscriptions are only exposed by generating a script. When scripting replication in SSMS 2005 there is only a single option to write the script to a file. For someone like me who works with replication regularly this is a HUGE annoyance. That means I have to write the script to a file, close the scripting window, and open the file I just created. That’s a lot of extra steps for something that should be simple.
Fortunately this has been corrected in SSMS 2008. Now there are options for Save to Clipboard and Open in New Query Window in addition to Save to File. Finally Microsoft doesn’t make you jump through hoops just to look at the underlying script for a publication!
SSMS 2008 Replication scripting options
Scripting Options
Scripts are generated in Object Explorer based on the options set in the SSMS Options dialog. To get to this menu in SSMS 2005 click Tools –> Options, then navigate to Scripting; In SSMS 2008 click Tools –> Options, then navigate to SQL Server Object Explorer\Scripting. Here are screenshots of the options in both versions:
SSMS 2005 Scripting options
SSMS 2008 Scripting options
SSMS 2008 adds two new options. They are:
- Script change tracking: This option compliments the new change tracking features in SQL Server 2008. When this option is enabled, change tracking options that apply to the object will be included in the script. This option has no effect if the target script’s version is set to SQL 200 or SQL 2005.
- Script Data Compression Options: Another SQL 2008 specific option that will include any data compression options relevant to the object being scripted.
Curiously, two existing options were renamed. Include vardecimal options was renamed as IncludeVarDecimal and Include system constraint names was renamed as ScriptDriIncludeSystemNames. These were probably unintentional and just slipped by during QA.
Summary
Though minor, the scripting options enhancements in SSMS 2008 are extremely useful; they eliminate frustrating annoyances and help improve productivity by reducing the time and number of clicks it takes to perform common repetitive tasks. The improvements are a sign that the development team at Microsoft is listening to feedback to help create a more polished tool and better user experience.
About Kendal
Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.
No comments
Post a Comment