There’s absolutely no doubt that SQL Server Management Studio (SSMS) is where SQL Server professionals spend the vast majority of their time. SSMS has evolved a lot over the years and incorporates a lot of functionality that’s easy to overlook. I’ll show you seven lesser-known tips for working more effectively with SSMS.
Related: SQL Server Management Studio Keyboard Shortcuts
7. Use Query Editor’s splitter bar
Just like Word’s splitter bar, you can split your Query Editor window by clicking the splitter bar in the upper right corner of the Query Editor window, then dragging the bar down. The two editing windows can independently work on different parts of the same file. Alternatively, you can use the Window, Split menu option to split the editing Window and the Window | Remove Split to remove the split.
6. Use Query Editor’s block select
Block select is a columnar alternative to the normal line-oriented select and is handy for helping to align lists of columns and fixing other formatting issues. To use Query Editor’s block selection, hold down the Alt key and move the mouse over the block of test you want to select. Alternatively you can use the Shift+Alt+Arrow keys.
5. Use Query Editor’s bookmarks
Bookmarks let you save multiple line positions within your scripts and quickly jump to the different sections you are working on. Bookmarks are particularly handy in long scripts and stored procedures. You enable Bookmarks using the Edit Bookmarks Enable Bookmark menu options. You can toggle Bookmarks on and off using Ctrl+K. Jump ahead to the next Bookmark using Ctrl+N and jump back to the previous Bookmark with Ctrl+P.
4. Use automatic replace to edit templates
Templates are boilerplate files that can jumpstart your coding. You can use SQL Server’s build in Templates or create your own using the View Template Explorer option. One great feature is the ability to automatically substitute values for all the template parameters without needing to manually edit them all. Selecting the Query, Specify Values for Template Parameters option brings up a dialog that lets you replace the values for all the replaceable parameters in your templates.
3. Customize the Default Templates
You can change the default content for the templates and add your own templates to SSMS by going into the C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql folder. All of the templates are listed in the folders under this directory. To customize a template, simply edit the .sql files.
2. Customize the Integration Services Explorer
Even though you don’t use SSMS to develop SSIS packages, SSMS lets you manage your SSIS packages. By default, SSMS groups the packages according to File System or MSDB. You can add folders under these levels by right-clicking and using the New Folder option. If you want to change the top level folders you can customize the C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml file and modify the elements under the <TopLevelFolders> node.
1. Use the Object Explorer’s Details page to select multiple objects
The tree navigation shown in Object Explorer’s left pane lets you only select single objects to work with. However, you can select multiple objects by using the Details page. Navigating to a database’s Tables node, then pressing F7 opens the Details page. From there you can press Ctrl, then left-click with the mouse to select multiple objects.
Related: Using Registered Servers in SSMS
