The world is full of exciting challenges, and we are constantly running daily, chasing our dreams. Especially in the software ecosystem, it is not about literally our dreams but about the release scheduled in the next couple of months. We are constantly running daily, and we stop appreciating our journey. As a developer or DBA, we have worked with SQL Server. In this process, the default tool is SQL Server Management Studio. We will look at some of the capabilities available with SQL Server Management Studio that will make us more productive.
SSMS Startup
The most common way to invoke SSMS is using the shortcut from our start menu. If you are on a Windows 8 or 10 machine, you can search for SQL Server Management Studio to invoke the program.
There is yet another method to invoke SSMS using the command prompt. In fact, the shortcut is SSMS.
C:\> ssms
This will bring up the SSMS tool, just like the shortcut we invoked before. The more exciting way to look at this shortcut is to check the various options available with SSMS command line parameters. To check them, use the following command:
C:\> ssms /?
The most attractive option is the capability to use the –S option for SQL Server Name. The steps to operationalize an SSMS session for work are close to 3-4 clicks. Using the –E option, we can load the SQL Server Management Studio directly with a query window using the- E option. This is the simplest way to get productive in one go.
Invoke SSMS with Integrated Authentication to the default server.
C:\> ssms –E
Invoke SSMS using the Server name, user name, and Pwd.
C:\> ssms –S BigSQL –U Pinal –P C0mp!exPwd
Another extension to this tip is the ability to change how the SSMS Window starts.
If we check the Tools > Options Dialog box, we can see:
There are several options, and the default is Open Object Explorer.
We can change any other values, which will take effect the next time we call SSMS.
Object Explorer Details
The best way to learn about the various capabilities of Object Explorer Details is by using them daily at your work. But how can one use them if we are still determining their capabilities? I have personally seen this Explorer as a productivity enabler.
To bring up the Object Explorer Details pane, use the F7 shortcut, which can be accessed from Toolbar > View > Object Explorer Details. When I talk about the Details pane, developers sometimes need clarification with the standard Object Explorer pane they are used to.
Delete Multiple Objects
This is a neat trick to ask at local user group meetups. I ask, “Is there a way to drop multiple objects in one keystroke?” The bonus question is, “Is there a way to script the drop of multiple objects in one keystroke?” Either way, the simple answer is with Object Explorer Details.
To achieve this, get to the correct folder from your Object Explorer – let us assume this to be the Table node. Select the two objects from the Object Explorer Details pane and press the DELETE key. We will be presented with a dialog to delete, and from the top toolbar, we can also script them out quickly.
Properties for Objects / Node
Depending on where the Object Explorer Details node is, we can get some exciting and additional information which might take a lot of time. Here are a couple of these details that would be valuable.
The two images show us completely different data: the first is around server-level information, and the second is at an individual DB level.
All of these data are available in dynamic management views (DMVs). But as a DBA, having these handy in a single click via SQL Server Management Studio is really powerful. Think about questions like:
- How much RAM is on the Server?
- How many Databases are available on a given instance of Server?
- What is the Data Space used by each Database?
- How much free space is open on a shared Database?
- When was the last backup taken for a shared database?
- What are the recovery models and compatibility levels for current databases?
We can quickly get these simple yet powerful answers using the details pane.
Exploring Copy Data Capability
Think about a requirement to beautifully represent data about our database in a graphical form. For example, we want to build a graph of the top 10 tables by size within our database. This can be easily achieved by performing the following steps:
- Get to the Databases and then Tables folder in Object Explorer Details.
- Right-click the top ribbon and add the Data Space Used (KB) column to the details pane.
- Remove other columns not of interest from the Object Explorer Details (with a right click like in Step 2)
- Now click on this column to sort data in descending order.
- Select the top 10 or as many rows as desired next.
- Press Ctrl + C to copy the contents into the Clipboard. If you were wondering, yes – this does work.
- Open Excel and paste the data.
- Press Alt+F1 to build a graph.
Shown below is an output of the above steps from my AdventureWorks Database
Type and Navigate
I have had the opportunity to work on databases with several thousand tables. It is sometimes difficult to get to the object from the Object Explorer and Object Explorer Details because we need to scroll through tons of data. Lesser known is the trick that we can type the object name, and both Object Explorer and Object Explorer Details can directly take us to that point.
In this example, we can either type from the Tables node under Object Explorer or select the Object Explorer Details pane and type the object name. The end result for both is the same: we will automatically get scrolled to the point where the object is. The only difference is, in Object Explorer, we need to also type the Schema name followed by the object, while in the Details pane, we can directly type the Object name. Neat, isn’t it?
Searching Objects from the Whole Database
Let us take a scenario where we need to search for a keyword across our database for objects having this keyword. Now we must search across object types like Tables, Stored Procedures, Triggers, Synonyms, etc. This can be achieved using the Object Explorer Details Pane. A search bar on the top allows you to type the keywords into that box.
In this example, we searched for the keyword “person” and used “%” as a wildcard character. This tells SQL Server Management Studio to display all objects with the word “person” in their name.
As we can see from the “Type” column, we have a mix of Indexes, Stored Procedures, and Schema as results of the search we just performed. The top bar shows that the “Search for ‘%person%’ in database ‘AdventureWorks2014’ has been Completed.”
We can expand on this capability and do the exact search at an instance level. If we select the Server name under Object Explorer and perform a search here with wildcards, this search will happen across databases across all objects. As you can see, the message is slightly different from what we saw previously.
Sync with Object Explorer Details
When we often start our work using Object Explorer Details, we can work inside a shell that is not in sync with the Object Explorer pane. Sometimes we would love to get to that node on our Object Explorer pane, and this can be easily achieved using the Sync button on top of the Object Explorer Details pane.
Know Your Filters
If you work with enterprise-level software, the chances of dealing with hundreds of tables, if not thousands, is entirely possible. I have seen developers and DBAs struggle with scrolling through tons of data. Earlier in this blog, we showed you ways to search and type to get to the location. There is yet another method to work with the data, which is using Filters.
There are a couple of ways we can invoke the filters.
- Use the Object Explorer to get to the given node, and in the above example, we are at the “Tables” node and have used the “Filter” icon available in Object Explorer.
- We can get the same effect of the filter and the dialog from our “Object Explorer Details” pane. Like the earlier point, drill down to the node under question and click on the “Filter” icon from the toolbar.
Adding a filter for an object like ‘Person,’ ‘Sales,’ or anything else will filter both the Object Explorer pane and Object Explorer Details pane at the same time.
I highly recommend this technique because we do not have to scroll through tons of data before narrowing it down to our desired content.
To learn more about why SQL Server health checks can save you a lot of heartache in the long run, then please take some time and review this 20-page whitepaper, “Five Reasons Why SQL Server Health Checks are Life Savers.”
Idera provides robust solutions for SQL Server, Azure SQL Database, and Amazon RDS for SQL Server:
- SQL Diagnostic Manager quickly finds and fixes database performance problems
- SQL Doctor tunes database performance, backup, and security via expert recommendations and executable scripts