DAT315 – Session Review for SSMS Tips & Tricks Session

One of the attendees of my TechEd session “DAT315 - Manageability Series: Uncover Hidden Secrets of T-SQL Scripts with Microsoft SQL Server Management Studio” suggested that I post the list of demos that I did for the session so that they could remember the ah-ha moments. Here we go.

Object Explorer Details (OED)

1. The property pane below the list of objects can be expanded to show more properties.
2. Right click on the column header in OED to add columns. Columns can be sorted. Settings stored for each object type.
3. When you copy selected rows, the headers are included. the example was to sort tables by row count, copy the top 10 items, paste in Excel. In Excel, press Alt+F1 to chart the result.
4. Navigate as you type works in OED based on the sorted column. The same works in Object Explorer – just need to include the schema name. Use the Filter command to focus on a set of objects you are working with.
5. Use the Search command in the toolbar area of OED to look for object names. If you are in a database, search looks for all objects with the matching name. Use LIKE expressions. If you start the search at the server level, search works across all databases. The Stop command halts the search.

Shell Tips

1. Customize the toolbar through Tools | Customize or right click on toolbar and choose Customize command. Move toolbar buttons like Debug, Stop, and Parse to the end of your toolbar so that you don’t click them by accident.
2. While in customize, take commands like Edit.SelectAll, and Edit.Delete and drag them on to the toolbar to duplicate the old Query Analyzer Erase button.
3. Showed off the Go to Find Combo box for Find, Help, and Commands.
4. Use Dynamic Help command to show help for commands based on keywords the cursor is on with the T-SQL editor window.

Solutions and Projects

1. Create Projects for your scripts. You can use multiple projects per solution to organize your work.
2. Set the Connect for your project to use as the default connection for scripts in your project.
3. Bookmarks allow you to save positions within your scripts for marking TODO areas or places of interest. You can create folders to organize your bookmarks within your solution. Use the Edit | Bookmark command set to see the keystrokes. Bookmarks are saved with the solution.
4. Use Templates to store your  standard scripts of interest. Each user has their own folder that you could push changes to using SMS. Use the Ctrl+Shirt+M command to replace template parameters that look like:
   <object_name, datatype, default_value>
5. Showed how you can change the default template for the New Stored Procedure command by going into the Program Files directory C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\Stored Procedure.
6. Modify the SQLFile.sql file in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql to add text to New Query windows.

Editor Tips

1. Change the color of the Editor status bar to help prevent you from executing the wrong query against a production server. Use the connection properties for Registered Servers, or the Connection properties in the Connection dialog for OE or Editor windows to change the color.
2. Use Tools | Options for Fonts & Colors to change the size of your tooltip. You can also change the size of the results in grids, etc.
3. To Block Select text, use Alt+Mouse moving. With the keyboard, use Shift+Alt+ArrowKeys
4. Delete leading Whitespace for a line of text or selected lines of text with Ctrl+K, Ctrl+\ combo.
5. Use the spitter control at the upper right corner of the text editor window to split your file just like MS Word. You can also use the Window | Split command to do the same. Window | Remove Split to do just as the command states.
6. Easily add a new line below the current line – regardless of cursor position in the line using Ctrl+Shift+Enter. Add a line above the current one using Ctrl+Enter.
7. Move a line down using Alt+Shift+T. Sorry – no command to move the line up.
8. Using Ctrl+I to incrementally search for text in the editor. It finds as you type. When you found what you are looking for, use F3 to Find Next and Sift+F3 to find previous. Search term goes into the Go to Find combo.
9. If you collapse regions in you text and can’t find it using the Find command, make sure you set the search option Find in Hidden text.
10. Use Ctrl+J with intellisense to replace one column with another. Use object aliases to make finding items easier.
11. You can set options like – turn off Intellisense when working against a stressed production server – by using the Tools | options setting for the T-SQL Editor.

Debugging

1. Demonstrated the 3 part series that I blogged about. Start with “Basic Debugging”

SSMS Start-Up Options and Activity Monitor

1. Demonstrated how to change your Startup options to launch Activity Monitor each time you start SSMS.
2. Demonstrated how to use Activity Monitor to track down queries with missing indexes. See blog entry “Not more excuses of missing indexes with Activity Monitor in SQL Server 2008”
3. Demonstrated how to use Activity Monitor to track down blocking queries.

Multi-Server Query Execution

1. From registered servers, run queries against any folder. Status bar is in a different color and shows how many servers will execute the query.
2. Configure output options to include Server name and Login name in the results using Tools | Options for Multi-Server queries.
3. Demonstrated using the template file by dragging the ChangeSAPassword template into a blank multi-server query editor. Here is the script example.

-- Change SA password across servers
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD='<Password_String, sysname, SAPassWord>';
GO
SELECT name, sid, modify_date
    FROM sys.sql_logins
    WHERE name = 'sa';
GO

Use the Ctrl+Shift+M key to replace the SA password.

Thank you everyone who attended the session.

Technorati Tags: SQL Server,TechEd,SSMS