Top 5 SSMS Time Saving Tips

By: Ian Fogelman   |   Updated: 2020-09-29   |   Comments (13)   |   Related: > 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.

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.

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:

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.

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 BOB@MYCOMPANY
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:

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:

Next Steps

• Share some of your productive tips by commenting below
• Download a previous release of SSMS
• Review this tip regarding foreachDB limitations
• Check out the 95+ tips on SQL Server Management Studio on MSSQLTips.com

About the author

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.

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

View all my tips