SQL Server Management Studio Recovery, Reporting, Key Combinations and Efficiency Tips

By:   |   Comments (1)   |   Related: > SQL Server Management Studio Configuration


Problem

I work with SQL Server Management Studio (SSMS) all the time.  How can I get more efficient with the product?  Are there any options to reduce my typing?  Are there any options to recover files if SSMS crashes?  Does SSMS have any reporting capabilities?

Solution

Let’s start our investigation of another five common SSMS tips!  For my earlier tips, please check out - How to Install SQL Server Management Studio on your Local Computer and SQL Server Management Studio Productivity Tips.

Drag and drop objects from Object Explorer to the Query Window

Are you writing a query that selects data from a table with lots of columns?  Does the select list we need to have all or almost all of these columns? Remember, that it is possible to drag and drop objects from Object Explorer to the Query Window. By dragging and dropping the “Columns” node under the table from Object Explorer to the Query Window we can paste all of the column names from that table separated by a comma as shown below:

 Drag and drop objects from Object Explorer to the Query Window

It is also possible to drag and drop columns separately and table name by dragging and dropping it to the “Query Window”. We can also do this for other objects (stored procedures, functions, users, etc.) to the query editor as well.

Recovering unsaved files in SSMS

In SQL Server Management Studio there is an auto-recover option to retrieve unsaved files. By default it is enabled and we can find it by clicking "Options" > "Options" > "Environment" > "AutoRecover":

Recovering unsaved files in SSMS

You can change the auto-recover settings or disable it. There are two queries below: the first one is saved as a file that is modified (new code is added) and not saved after modification. The second window has code typed in a new query window:

Two Query Windows in SSMS

Now let’s end the SSMS process in Task Manager without saving the files:

end program in Windows Task Manager

Next time when we open SSMS, it will suggest recovering the unsaved files:

SQL Server Management Studio Recovered Files

Note that only the code from the first query stored in a file is recoverable. The query in the second window was lost. So, the auto recovery feature works only for queries that have been saved as a file before an issue occurs. By pressing “Recover selected files” we can see that our file successfully recovered and the added code is not lost:

Recovered code from SSMS

We can find recovered files and their original versions in “C:\Users\[UserName]\Documents\SQL Server Management Studio\Backup Files\Solution1”:

sql server management studio recovered files

Useful SSMS Key Combinations

Select a Code Block in SSMS

In SSMS you can select blocks by pressing the “Alt” key and left-clicking the mouse:

Select a Code Block in SSMS

This way we can copy the selected block of data.

Convert code to Upper Case in SSMS

Selecting the piece of code and pressing the “Ctrl”+”Shift”+”U” keys together converts our code to upper case:

Convert code to Upper Case in SSMS

Convert code to Lower Case in SSMS

The combination of keys “Ctrl”+”Shift”+”L” converts the selected text to lower case.

 Comment and Uncomment Code in SSMS

Sometimes it is needed to comment part of the code quickly and key combinations can help us in these situations. To comment the selected code we can press “Ctrl”+”K” together and after that press “Ctrl”+”C”:

Comment and Uncomment Code in SSMS

To uncomment the selected code we can press “Ctrl”+”K” together and after that press “Ctrl”+”U”.

Open a New Query Window in SSMS

The combination of keys “Ctrl”+”N” opens a new query window in the current connection. 

Delete Words in SSMS

Using the combination of “Ctrl”+”DELETE” we can delete the word after the cursor. For example in the following position of the cursor let’s press “Ctrl”+”DELETE”:

Delete a word in SSMS

As we can see the FROM word is deleted:

test table a with the FROM word deleted

Pressing “Ctrl”+”DELETE” once more will delete the word TestTableA.

Display Execution Plan in SSMS

The combination of keys “Ctrl”+”M” displays actual execution plan and “Ctrl”+”L” displays estimated execution plan.

Query Shortcuts in SSMS

The combination of keys “Alt”+”F1”, “Ctrl”+”1” and “Ctrl”+”2” keys run sp_help, sp_who, sp_lock stored procedures correspondingly. By navigating to “Tools” > ”Options” > ”Environment” > ”Keyboard” > ”Query Shortcuts” we can assign stored procedures to the open key combinations:

SQL Server Management Studio Query Shortcuts

In our example we have assigned TestProcA from ABC database to the “Ctrl”+”4” combination and now pressing “Ctrl”+”4” our procedure runs:

“Ctrl”+”4” combination and now pressing “Ctrl”+”4” our procedure runs

Refresh IntelliSense Local Cache in SSMS

Have you just created a new object in the database, but IntelliSense still underlines it with a red line in the query window?

IntelliSense without updated cache

To correct this, you need to refresh the IntelliSense local cache by navigating to “Edit” > “IntelliSense” > ”Refresh Local Cache” or press “Ctrl”+”Shift”+”R”:

SQL Server Management Studio Intellisense Refresh

After doing that red line under new object will disappear.

SSMS Standard Reports

There are a lot of standard reports in SSMS which can be very helpful for monitoring our SQL Server. Instead of writing standard queries to monitor SQL Server we can use these reports which are predefined and their results are visually nice and understandable.  By right-clicking on the SQL Server instance and navigating to “Reports” > ”Standard Reports”, we will see the predefined SSMS reports:

SQL Server Management Studio Reports

Let’s choose “Performance-Top Queries by Average CPU Time”:

SQL Server Management Studio Top Queries by Average CPU Time Report

By right-clicking on database node and choosing “Reports” > “Standard Reports”, we will see database specific reports:

SQL Server Management Studio Database Reports

For example, “Disk Usage by Table”:

SQL Server Management Studio Disk Usage by Table Report

On the SQL Server Agent node we can see jobs specific reports, for example “Top Jobs”:

SQL Server Management Studio Top Jobs Report

Conclusion

All in all, this article is the sequel of the previous “SQL Server Management Studio Common Tips – Part 2” and here we looked at common tips SSMS that will facilitate your work with SSMS and allows you to use it more confidently.  

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, August 8, 2017 - 3:28:18 AM - Herbert Tobisch Back To Top (64285)

 Good and helpful overview.

Anyway - do not have those standard reports and do not have the time to find out how to install them.

So, I would appreciate very much if yo could include a description of installation process.

best regards herbert















get free sql tips
agree to terms