By: Ian Fogelman | 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:
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.
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
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips