SQL Server Management Studio (SSMS) is the most frequently used tool in the SQL Server 2005 management suite. Even if you use it daily, however, you might not realize the tasks you can do with it.
Create Shared Server Registrations
Do you get tired of having to re-register your servers when you move to a different SSMS workstation? You can save and restore your server registrations. Open the Registered Servers window using View, Registered Servers; right-click a node, then select Export. To import the registration information file to another SSMS system, on that system open the Registered Servers window, then right-click a node and select Import.
Manage Integration Services
By default, SSMS’s Connect to Server opens to the Database server. However, by selecting Integration Services you can manage running packages and view stored packages. You can also use the SSMS Connect To dialog box to connect to Analysis Services, Reporting Services, and even SQL Server Compact Edition.
Change the SSMS Startup Window
If you often use Query Editor, you can save some work by changing the SSMS startup options. Go to Tools, Options, Environment, then use the drop-down menu to change the At startup setting. I prefer Open Object Explorer and new query.
Get Quick System Summary Info
Using the Server Dashboard The Server Dashboard, new in SQL Server 2005, shows an overview of your current server status including CPU utilization, total server memory used, active sessions, blocked transactions, and more. You can view the Server Dashboard by opening Object Explorer, then right-clicking a server node. From the context menu, select Reports, Standard Reports, Server Dashboard.
Jumpstart Your T-SQL Scripts
Using Templates SSMS includes templates for a wide array of T-SQL tasks, from creating triggers and stored procedures to configuring Database Mail. Select View, Template Explorer and expand the template node you want to use, then double-click the template to pull it up in SSMS’s Query Editor.
Highlight T-SQL Errors
Although it doesn’t debug T-SQL, Query Editor can find the lines causing T-SQL errors. Go to the Messages pane and double-click the error, displayed in red. The error line will be highlighted in the Editing pane. Display Line Numbers in Query Editor Query Editor’s Messages pane by default doesn’t display the line numbers of T-SQL syntax errors. To show line numbers in Query Editor, go to Tools, Options then expand All Languages and select the Line numbers check box.
Automatically Generate Scripts
The Script button in the top left corner of almost all SSMS dialog boxes makes it easy to create and save T-SQL scripts. To use it, just fill out your SSMS dialog box, then instead of clicking OK (which would normally execute the action), click the Script button. The T-SQL code to perform the action will be written to a new Query Editor window. This scripting option is also a great tool for novices looking to learn more about T-SQL.