Top 5 SSMS Time Saving Tips


By:   |   Updated: 2020-09-29   |   Comments (10)   |   Related: More > SQL Server Management Studio


Problem

I can't think of a tool which I have spent more time in my career thus far than SQL Server Management Studio (SSMS). Even though I have worked in it almost every day for over 7 years, I am still finding time saving efficiencies from time to time. Here is my top 5 list of SSMS features I wish I knew about sooner.

Solution

Below are 5 tips that I have found to be very useful when working with SSMS.

1 - Dragging Object Names into Query Editor with SQL Server Management Studio

Most people keep the Object Explorer open to browse tables, procedures and databases, but it also has a drag and drop feature which can assist in query writing.

  • In this example, first I build a SELECT statement by dragging the table name from the Object Explorer.
  • Then build a DELETE statement by dragging the table name and column name from the Object Explorer.
  • Lastly, I pull the object definition and execute a stored procedure by pulling in a stored procedure name.

Watch in action below.

dragging objects

2 - Locally Hosted Reporting Services Reports in SQL Server Management Studio

Reporting Services is great. It gives a deployable target for sharing your business reports with coworkers for business needs. However, there is a bit of legwork to get a reporting instance server up and running. What if you just need to run a quick ad-hoc report to keep an eye on one of your databases? If you have SQL Server Data tools (or the modern equivalent) on your work station, you can utilize custom reports.

You can build a SSRS report and copy the rdl file into the custom reports directory. The path should be similar to: C:\Users\Ian\Documents\SQL Server Management Studio\Custom Reports.

For example, I created a SSRS report to run the following code.  For this report I used the MSforeachdb command in combination with some dynamic SQL and the SYS.OBJECTS view to query each database for object counts and types.

IF OBJECT_ID('TEMPDB..#TEMP_RESULTS') IS NOT NULL DROP TABLE #TEMP_RESULTS
CREATE TABLE #TEMP_RESULTS
(
[DB] VARCHAR(MAX),
[ObjectType] VARCHAR(MAX),
[Count] VARCHAR(MAX)
)
 
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; 
INSERT INTO #TEMP_RESULTS
SELECT DISTINCT DB_NAME() DB, TYPE_DESC , S2.OBJECTCOUNT
FROM SYS.OBJECTS AS S1
       CROSS JOIN (SELECT TYPE,COUNT(*) AS ObjectCount FROM SYS.OBJECTS GROUP BY TYPE ) AS S2
WHERE  S1.TYPE = S2.TYPE;'
 
SELECT * FROM #TEMP_RESULTS

After the report is built, copy and paste the report rdl file into the location mentioned above.

To access the report in SSMS, right click on Databases > Reports > Custom Reports and you should see the new report listed.

When we click on the report, we can see the reports render inside of SSMS.

ssrs reports

3 - Filtering Objects in SQL Server Management Studio

In some databases you may have an overwhelming number of objects. I once worked in a database that had so many views it would crash SSMS when opening the views tab. A useful tip to help with an issue like this is to apply object filters.

Each hierarchy that you can right click under a database may be filtered. The four main operators available for filtering are as follows:

Operator Description
Equals Exact match to a single value
Does not Equal Inverse match excluding a single value
Contains Can be used to match partial object property, such as like “%searchterm%”
Does not contain Inverse match where value is not like ‘%searchterm%’

These operators can be applied to the name, schema, owner, durability type, in-memory optimized and creation date meta data attributes of SQL server objects. Additionally, when dealing with the creation date property, less than greater than, between and not between operators can be used.

Here is an example below.

filtering objects

4 - Managing Notifications with Operator Groups in SQL Server Management Studio

Many DBA’s use the sp_send_dbmail procedure to alert on important database events. It is tempting when using this procedure to hard code the recipients email address. However, this can lead to manual intervention when the distribution group for the alert needs to change. Utilizing the msdb table that houses operators can help alleviate these changes to hard coded emails.

In this example I will have an operator group called AnnualSalesReportGroup which will have a single user. I will execute the query twice, 10 seconds apart. In-between each query I will modify the operator group under the SQL Server Agent node in Management Studio. The idea here is that the operator groups will be changed and are easier to maintain rather than hardcoded recipients in the code that uses the sp_send_dbmail procedure.

--FIRST RUN, SINGLE USER [email protected]
DECLARE @EMAILADDRESSES VARCHAR(MAX)
SET @EMAILADDRESSES = (SELECT email_address FROM [msdb].[dbo].[sysoperators] where name = 'AnnualSalesReportGroup')
SELECT @EMAILADDRESSES 
 
WAITFOR DELAY '00:00:10'
--SECOND RUN, TWO USERS ADD : [email protected];[email protected]
SET @EMAILADDRESSES = (SELECT email_address FROM [msdb].[dbo].[sysoperators] where name = 'AnnualSalesReportGroup')
SELECT @EMAILADDRESSES 

See in action below:

using operators

Now anytime you need to update distribution list for anything that uses email, you can return to the operators and edit accordingly.

5 - Critical Query Analysis with SQL Server Management Studio Debug

One great feature of SSMS prior to 18.XX was the debug tab, it sits between the project and tools task bar items. Debug mode allows you to step through your code step by step, assign break points and analyze your code. Sadly this feature was deprecated in the latest release of SSMS, you can vote to have it reinstated here. For this reason, I keep both an 18.XX release and a previous release of SSMS on my work station.

In this code block I will increment the value of the variable @I and set the @FINISHED bit variable equal to 1 when @I is greater than 5. This is just to demo the functionality of the debug feature, press F10 to kick off the debugger and F10 again to continue to each step.

DECLARE @I INT = 1, @FINISHED BIT = 0
 
SET @I = 2
 
WHILE (@I < 5)
BEGIN
   SET @I = @I + 1
END
 
IF(@I = 5) 
BEGIN
   SET @FINISHED = 1
END

See in action below:

debug

Next Steps


Last Updated: 2020-09-29


get scripts

next tip button



About the author
MSSQLTips author Ian Fogelman Ian Fogelman is professional DBA with a hunger and interest for data science and application development. Ian enjoys leveraging cloud-based technologies and data to solve and automate tasks. Ian holds a MS in computer science with a concentration in software development. Ian is also a member of his local SQL Server user group and a member of his local data science user group in Columbus Georgia.

View all my tips
Related Resources





Comments For This Article




Monday, October 19, 2020 - 8:02:22 PM - Ian Fogelman Back To Top (86660)
@Barak,

Thank you for your additions! I think keyboard shortcuts are extremely valuable and a great point when talking efficiency in SSMS!

Sunday, October 18, 2020 - 11:57:45 AM - Ian Fogelman Back To Top (86654)
Hello @BK,

1) As far as I know there is not a way to save filter settings, it is more adhoc use case in nature. One alternative would be to use the SYS objects DMV and save that query to find objects quickly. For example this would return the same results as a object filter for the contains 'Employee' : SELECT * FROM SYS.OBJECTS WHERE TYPE = 'V' AND NAME LIKE '%EMPLOYEE%'

2) I have slowed the gif to 50% speed and will get it uploaded to the original post, for now you can view that slowed down version in my S3 bucket : https://fogelstatic.s3.us-east-2.amazonaws.com/4_50.gif

Thursday, October 15, 2020 - 3:45:05 PM - BK Back To Top (86646)
Thanks for the tips. They are really useful. It would be great if you could respond two a couple of my questions. (1) Tip-3: How to save the filter settings, so that I don't have to enter them every time? (2) Tip-4: How to pause the video (animated gif), so I that I can note down the process in detail? It is just a bit fast for me to follow. Thanks.

Thursday, October 15, 2020 - 8:33:31 AM - John Steven Bigler Back To Top (86644)
Nice article Ian. Thank you.

Thursday, October 15, 2020 - 2:56:32 AM - Simon Singh Back To Top (86639)
Some great tips there. I try to minimise the use of the mouse wherever possible, so keyboard shortcuts are the key for me. Further to Barak's comments above, I recommend customising your own keyboard shortcuts (Tools-Options-Keyboard-Query Shortcuts). I have the following:

Ctrl-5 - SELECT TOP 100 * FROM - highlight a table/view to get top 100.
Ctrl-4 - USE - highlight a database name to switch database.
Ctrl-3 - sp_helptext - highlight a stored procedure or function to get the definition (you need to be in the correct database, so use shortcut above)


Monday, October 05, 2020 - 5:23:46 AM - Barak Back To Top (86598)
The N 1 top tip for SSMS time saving is to use keyboard shortcuts.
We are typing code. By avoiding going to the mouse... move... get back to the keyboard you save a lot of time !
Not with the very specific tasks, but with the most common ones we do all the time.

CTRL + R - minimize / pop Results pane
CTRL + SHIFT + R to refresh Intellisense, so when you create / modify a SP, table or other objects it recognize the changes.
Also the ones we all know but I have seen some guys who don't:
Ctrl-Z is roll back, but CTRL + Y is redo. You can go back to copy prev state and redo everything you've changes.
CTRL + L - Estimated Exec Plan
CTRL + M - Actual Exec Plan
F8 Object Explorer

Once you start using them you'll look up and learn the rest.

Wednesday, September 30, 2020 - 10:12:02 AM - Ian Fogelman Back To Top (86562)
Hi @Brian,

That is correct, you can drag in all columns from a table by clicking and dragging the columns hierarchy to the query canvas.

Tuesday, September 29, 2020 - 6:41:40 PM - Shamsu Zoha Back To Top (86557)
I really appreciate the contributions. These tips are really helpful.

Regards,
Shamsu

Tuesday, September 29, 2020 - 5:53:52 PM - Brian Back To Top (86556)
Great tips. Also you can drag and drop all of a table's columns at once by dragging the Columns folder for that table. This is useful for tables with a lot of columns.

Tuesday, September 29, 2020 - 2:12:35 AM - Grzegorz Lyp Back To Top (86549)
Debugger is available in SSDT with VS2019.


download





Recommended Reading

Different Options for Query Results in SQL Server Management Studio

Display Line Numbers in a SQL Server Management Studio Query Window

Connect to SQL Servers in another domain using Windows Authentication

Executing large scripts in SQL Server Management Studio with Insufficient Memory Failures

SQL Server Database Diagram Index was Outside the Bounds of the Array














get free sql tips
agree to terms