Today’s blog is inspired from the use of common tools that we use day-in-day-out. For me when playing with SQL Server, it will be SQL Server Management Tool (SSMS.exe). This blog has a number of posts around SSMS Tips like – SSMS: T-SQL Debugger Shortcuts, SQL Server: SSMS Tips II, SQL Server 2012 : SSMS Tips, SQL Server 2012: Snippets and IntelliSense and few more. So do look at them too.
In this blog let me just talk about a simple addendum screen that we hardly use but has tons of valuable information hidden inside SSMS – it is Object Explorer Details. For easier understanding I am just going to use a number of pictures to illustrate what I have actually done :).
Once you are in SSMS, click on F7 shortcut or use the View –> Object Explorer Details keyboard navigation as shown in picture below.
This bring the Object Explorer Details in the usual location where we normally have the Query Window. You can navigate to any node like the Object Explorer or click on a node on Object Explorer and it will populate the related details in the Details pane as shown in the next image. Below we are at the AdventureWorksDW database and have selected the Tables node. This enumerates and lists all the tables in the given database with few interesting properties.
Some additional capability in the Details pane is the ability to search for an object using the Search box on the top and once we are at the object, we can go ahead and click the double arrow (as in figure) to synchronize the Object Explorer View. It is quite an handy feature to use.
The Columns in the Object Explorer Details are customizable and quite interesting. From the header if you right click we will be presented with a list of additional columns that we can include in our view. The list changes based on the node that you are currently on. So “right-click” every header and play to your hearts desire.
In the above options, I selected Data Space Used (KB) and Row Count for my example. We can also remove any unnecessary columns which we don’t want to see too. Now with this data it becomes interesting. In the below figure I have done two things, a) sorted the “Row Count” Column and then b) selected about 10 rows from the OED pane. Now just CTRL+C and take these values to Excel :).
I have pasted the same inside Excel and it looks like below.
Inside Excel we have the freedom to do a number of things. One of the features that I have loved using the suggested charts, formatting of Excel 2013. When the table is selected, a small icon appears on the bottom right side – click on it and I have selected the Formatting –> Data Labels.
Other shortcut to play around is ALT+F1 to get a chart quickly populated. Below is a sample output that I have shown.
I am sure you have played around with Object Explorer Details before. If you haven’t played around till date then I am sure you will do now. Do let me know if you find this interesting and we will followup with many of these tips in the future.