Object Explorer Details (7 minutes)

Part of "SQL Server Management Studio Shortcuts and Secrets (1 hour 30 minutes)"

I’m a fan of Object Explorer Details for one big reason

It’s all about the ability to script multiple items. I show that first!

I talk about why I’m not such a big fan of using it for object search starting around 2:45.


Let’s talk about Object Explorer Details.

I really like Object Explorer Details for its ability to let you script out more than one object at once.

Not so crazy about using it for other things. It does do other things and I’ll show you why I’m a little hesitant about them, but I’m a huge fan when it comes to being able to quickly script out multiple things at once.

Scripting out multiple indexes

Let’s look just in plain old Object Explorer at a table. Let’s look at the Sales [Schema]. What table do we want? Sales.Customers is our demo table. It has a bunch of indexes on it.

Here I am in Sales.Customers, and I can try to select multiple indexes I’m hitting shift and I’m just not. I’m only able to select one. That is normal. Object Explorer, plain old just Object Explorer doesn’t do that.

F7 opens Object Explorer Details

What we need is its little friend, Object Explorer Details. You can open this by using F7.

If you want to use the keyboard you can hit that Alt button and go up to the view menu up top and get to it that way as well. When I have Object Explorer Details, I’m going to pin this here and it does give me a URL. I can make this window even bigger. It gives me a URL to try to help it be really really clear where I am. I get a little tool tip there.

Now I can highlight multiple items holding down the shift key.

I can also do control + a to highlight them all.

When I right click and say script index, I can now script them all as create to a new query window and I have them all here.

In a previous demo I compressed an index with data compression. Is that here? I just searched for the word data, because honestly I was like, “is it ‘compressed’, is it ‘compression’, what’s the exact word?” It is in fact DATA_COMPRESSION = PAGE This is the AlternateContactPersonID index that we rebuilt with DATA_COMPRESSION = PAGE.

Yes, tweaking the settings in tools options for Object Explorer is inherited by Object Explorer Details.

I really like that.

Features I often avoid in Object Explorer Details

I am a little careful with using the synchronize button and I’m a little careful with using the Find functionality. I don’t like really doing those in production databases. I’m going to go back into Object Explorer and show you what they do. Especially in test or non-production databases, sometimes I will use these there.

I’m going to use Back just to kind of navigate around in Object Explorer. You actually can do that if you want, right? And see how I got, you know, sometimes navigation gets a little weird. See how I got the hourglass there? It’s like, “where do you want to go back to?”

I do find that clicking around a lot in Object Explorer Details like this can lead me to situations where I’ve got something like an hourglass or like a grayed out pane and I’m left like, “What query are you running in the background?” That’s why I get a little iffy in production servers. I’m going into Object Explorer and I’m going to click up here on FASTER01, Object Explorer Details catches up with me.

What I’m going to do is use the Search bar here and I’m going to search for everything that contains the letter “a” using wild cards. And it says hey I didn’t get data for your request. Well okay I’m going to click on WideWorldImporters in Object Explorer and Object Explorer Details catches up and I’m going to say okay I want to run this search in that context and see my context bar confirms there.

Well now that I’m searching in that database I have found 1,658 things because I did a broad search. But the thing is there’s databases out there that have thousands and thousands and thousands of objects in them. Sometimes searching, even with a more specific search than this can be kind of a big query and that’s why I tend to be hesitant and I’m not super eager to use this everywhere.

I’m going to go to just a random table and right click and say synchronize to synchronize Object Explorer with it and I’m back to that hourglass, it’s having to run these queries in the background. I’ve asked for something that isn’t always a small request and I’m getting like it’s running queries. Well, could those queries cause blocking on my metadata? Maybe. Right.

I don’t know and that’s why I don’t like to do this too much in a production database. Sometimes it is fast and I get to my synchronized location quickly, but, you know, not always. I’m going to go back and it reruns the search.

I like to use this really sparingly because WideWorldImporters is not a particularly database that’s large in terms of the number of objects that it has really. It’s a small sample database. With Object Explorer Details I am a huge fan when it comes to the ability to script out multiple items at once in terms of usage outside of a very safe test environment, that’s about all I would do.

Even in a production environment, hopefully all of my indexes are all nicely scripted out and checked into source control already and I don’t need to go scripting them, but hey no judgment if you’re just getting there and you’re just getting stuff checked in. We all got to do what we got to do.