Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (1)   |   Related Tips: More > SQL Server Management Studio

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, August 08, 2017 - 3:28:18 AM - Herbert Tobisch Back To Top

 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


Learn more about SQL Server tools