SQL Server Management Studio (SSMS) Tips and Tricks

1.     Change the status bar in the “New Query” window to red when connected to production or green when connected to development.

I recently uninstalled the SSMS Tools Pack (www.ssmstoolspack.com) because it was crashing whenever I connected to an Analysis Services server.  One feature I missed was the ability to change the query window to red when connected to production and green when connected to Development.  I discovered this feature is built in for SQL Server 2008!

Open SSMS and in the “Connect to Server” dialog click the “Options” button.  Select the “Connection Properties” tab and check the box next to “Use custom color”.  Select your desired color and that’s it!  While you’re in there it might be a good idea to set your default database to something other than “Master.”

image

2.     Install a Tool to Automatically Format your T-SQL Code

I’m cheap.  I use Poor Man’s T-SQL Formatter.

3.     Set up some Keyboard Shortcuts

In SSMS click Tools, and then Options.  Select Keyboard from the Environment tree and set up the things you are like to type very often.  These shortcuts execute as soon as you press the hotkey.  This makes their usefulness somewhat limited in my opinion.  A good one to add is CTRL + F1 as sp_helptext.  This allows you to highlight a stored procedure, function, or view and quickly look at it’s code.

image

Highlight an entity within the Query window and press ALT+F1 to execute sp_help on the object. This will give you a breakdown of any columns, indexes, parameters, etc.

image

Note: If you need to specify an object in a different schema, you have to use the two part name “schema.Object” and put it in single quotes.  For example, ‘dbo.MyTable’.

I use a program called called Clavier + to assign keyboard shortcuts to things I want typed but not executed right away.

image

These keyboard shortcuts are built-in to Management Studio:

Keyboard Shortcuts

  • F5, CTRL + E or ALT + X – execute currently selected TSQL code
  • CTRL + R – show/hide Results Pane
  • CTRL + N – Open New Query Window
  • CTRL + L – Display query execution plan
  • CTRL + SHIFT + Q – Display the query designer (noob.)
  • CTRL + I – Incremental search.  After pressing Ctrl=I, look at the bottom left of your SSMS window.  Here you type what you want to search for.  Press F3 to cycle.

Editing Shortcuts

  • CTRL + K + C and CTRL + K + U – comment/uncomment selected block of code
  • CTRL + SHIFT + U and CTRL + SHIFT + L – changes selected text to UPPER/lower case
  • ALT + Select text with mouse and drag – select/cut/copy/paste a rectangular block of text

Another big typing saver is to right click a table within object explorer and selecting “Script Table As” and choosing one of the many options.

image

3.    Modify Startup Options

In SSMS click Tools, and then Options.  Select Environment in the navigation tree.  Here I like to change the first setting to always “Always Open Object Explorer and new query.”  I also crank up the recently used files setting to the max and hide the system objects in Object Explorer.  If you have never tried the MDI environment, you might like it better than the tabbed documents. This screen is also where you change that setting.

image

I also like to disable the splash screen.  This cuts the time it takes SQL Server Management Studio to load in half.  Right click your shortcut to SSMS and select properties.  Enter the text “-nosplash” (without the quotes) right after the ending quote in the path.

image

Finally, it is useful to create a solution of commonly used SQL scripts to always load at start-up.  To do this, first display the Solution Explorer by pressing Control+Alt+L or clicking View, Solution Explorer.  Now, right click the “Solution ‘Solution1’ (0 projects)” text and select Add, New Project.

image

Use the default “SQL Server Scripts” template and give your solution a clever name.

image

Rename all of your SQL Code Snippets so the extension is .SQL.  Drag them into the queries folder within the Solution Explorer.

image

Click File Save All and save your solution.  Note the location.  Next, open Windows explorer and browse to the location of your solution.  Copy file location address to your clipboard.  Go back to your SSMS shortcut properties and add within double quotes the location and file name of your solution before the “–nosplash”.

This is the complete text within my shortcut properties:

“C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe” “C:\Users\mmilligan1\Documents\SQL Server Management Studio\Projects\MySQLServerScripts.ssmssln” –nosplash

4.     Refresh IntelliSense

IntelliSense saves time by helping you type table, database, and column names.  First, make sure it is enabled.  Tools, Options.  Text Editor, Transact-SQL, IntelliSense.

image

If you create a new table and IntelliSense just doesn’t seem to be working then press control+shift+R or click Edit, IntelliSense, Refresh Local Cache.

image

5.    Drag the Word “Columns” to the Query Window to Type all Column Names Quickly

image

6.     Implicit Transactions by Default

If you can never remember to start a transaction before your deletes you can go into options and turn IMPLICIT TRANSACTIONS ON by default.

image

7.     Isolation Level

Again with the options…  set the isolation level to READ_UNCOMMITED by default.  Now you don’t need to type WITH (NOLOCK) in all of your ad-hoc queries!

image

8.     Object Explorer Details

Use Object Explorer Details to quickly delete many objects at once.  Also you can use it to view your tables as you can press the first letter of the table name and it jumps to that table.

9.     Template Explorer

Become familiar with the items in the template explorer.  Bring it up by pressing CTRL + ALT + T or clicking View, Template Explorer.  These act as a quick reference for getting the proper syntax for many common functions.

image

10.     Display Line Numbers

Click Tools, Options.  Expand Text Editor and select Transact-SQL.  Check the box next to Line numbers.

image

11.     Filter your Objects in Object Explorer

Right click the Tables folder (or any folder for that matter) and select Filter, Filter Settings.  Now you can simplify your view of the objects in the Object Explorer by showing only the objects you need to see.

image

12.     Automagically include column headers when copying and pasting results

Tools, Options, Query Results, SQL Server, Results to Grid.  Check the box to always include column headers when copying or saving results.  You may also like to try having it always display results in a separate tab and to switch to the results tab after the query executes.

image

13.     Do NOT Prevent Saving Changes that Require Table Re-Creation

I uncheck the “Prevent saving changes that require table re-creation” option.  Tools, Options.  Designers, Table and Database Designers.

image

That’s all for now.  I’ll add more as I think of them or come across new tips.  Please leave a comment if you have a useful tip you’d like to share.

Related Links

DAT315 – Session Review for SSMS Tips & Tricks Session

SQL Server Management Studio Tips

Advertisements

2 thoughts on “SQL Server Management Studio (SSMS) Tips and Tricks

  1. Nice collection of tips. I develop SSMSBoost add-in – it would be nice If you could have a look at it. You might find it useful (for example connection coloring is done more flexible). You can script objects from SQL Editor, Locate them in Object Explorer, Search in Results Grid, export it to Excel and much more…. Comments are welcome.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s