Stupid pet tricks (5 minutes)

Transcript

Now for what I like to call, “stupid pet tricks.”

My pets wouldn’t do these things

But they aren’t quite as innocent as stupid pet tricks. The things I’m going to show you — don’t run around and do these things where you shouldn’t.

I’m mainly showing you just because these are things that can get messed up or that you might happen to find that are really weird. I am not suggesting that you do them. These are wacky and can make things go crazy.

Batch separator madness

The most common prank that I’ve seen people play on one another, these are like DBA pranks, but also developer pranks, is that if someone leaves their keyboard unlocked, someone else very quickly goes into Tools and Options, and then goes into Query Execution down here, goes to SQL Server, and changes the Batch Separator.

This is Tools / Options, we’re in Query Execution, and here is that default Batch Separator of GO. I am going to change it to GOO, G-O-O, and then I’m going to click OK.

Part of why this prank is so evil , I’m saying it’s evil, it’s bad, is in my current window, everything still works fine because that change only applies to new windows.

I’m going to now copy this code, do Control + N, paste it in, and now, when I run this command, I get this weird message that if i don’t realize anyone messed with my Management Studio, I’m like, “What’s wrong with the word GO?” “GO is totally fine.”

Suddenly, none of my scripts work.

You can see why this might not be funny to everyone, right? Be very careful.

I’m telling you this just in case you get pranked, do not pass the fear on to other people.

Hiding tables in Object Explorer

Another really weird thing is that you can hide a table from all instances of Management Studio. This might be fun for a trivia event.

This is going to affect anyone who uses that database, it’s not just you.

But if you’re at some sort of super nerdy party, with DBAs and developers, maybe this would be appropriate for that context. This hack, I found on Kenneth Fisher’s great blog, sqlstudies.com, and it’s really strange. You can hide a table.

I’m going to go into Object Explorer, and I’m going to look at our good old WideWorldImporters database. Let’s check out Application.Cities. [This is] the first table in the list. It’s right there, visible for everyone.

Well, I’m going to use the database, and I’m going to create an extended property. This is technically undocumented, not supported by Microsoft. If you create the property with this specific name and the value Hide, it will disappear from Object Explorer.

Now that I created that extended property, when I refresh it, Application.Cities isn’t there anymore. I’ve just got Application.Countries.

This isn’t a form of security because I can still see it in sys.objects. Right? It still exists. I can see it, I can query it just fine. I have sysadmin permissions. Now NOBODY, this isn’t just me, nobody can see it in Object Explorer, but we can see it when we query it and interact with it.

So we’ve made Management Studio half ignorant of it in a really weird way. It is something you can monitor for because it’s got to have that very specific extended property name to work.

We can query the extended properties table, and if you’ve got something with this name, you could find it and be like, okay the value is Hide. This is trouble, but hopefully, hopefully nobody is going to do this.

If you’re like, oh, somebody on my team might do this, you’ve got more of an HR problem than a monitoring problem, because hopefully nobody thinks this is a good idea. I’m going to go ahead and remove the extended property by using drop extended property and now, when I refresh the table, whew, Application.Cities is back and appears as normal.

So maybe good for a trivia night sometime, I don’t know what context that one might be okay with, but like I said, these stupid pet tricks, these are not friendly things. These are not good for keeping your job. Use the force wisely. But you know, sometimes have fun in a safe place, right?

Back to: SQL Server Management Studio Shortcuts & Secrets (1 hour 30 minutes) > Stupid pet tricks
Menu