By: Sergey Gigoyan | Updated: 2017-07-11 | 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:
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":
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:
Now let’s end the SSMS process in Task Manager without saving the files:
Next time when we open SSMS, it will suggest recovering the unsaved 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:
We can find recovered files and their original versions in “C:\Users\[UserName]\Documents\SQL Server Management Studio\Backup Files\Solution1”:
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:
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 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”:
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”:
As we can see the FROM word is 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:
In our example we have assigned TestProcA from ABC database to the “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?
To correct this, you need to refresh the IntelliSense local cache by navigating to “Edit” > “IntelliSense” > ”Refresh Local Cache” or press “Ctrl”+”Shift”+”R”:
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:
Let’s choose “Performance-Top Queries by Average CPU Time”:
By right-clicking on database node and choosing “Reports” > “Standard Reports”, we will see database specific reports:
For example, “Disk Usage by Table”:
On the SQL Server Agent node we can see jobs specific reports, for example “Top Jobs”:
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
- Check out this related information:
About the author
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
Article Last Updated: 2017-07-11
