Management Studio level-up

Download BitTorrentMagnet – Audio podcast:

Target Audience:

Anyone who frequently uses SQL Server Management Studio.

Abstract:

Move up to Management Studio Superstar with these great productivity tips! We’ll explore stuff like keyboard shortcuts, configuration options, how to keep your SSMS settings synchronized across multiple environments and other really useful everyday tricks and hacks. Think “hey, wait, how’d you do that!?”

Did you, for instance, know that you can hold down the Alt key while selecting text? The “Alt trick” works for copying, pasting, moving, even typing. Handy if you want to re-use text horizontally instead of just vertically. Like comparing the contents of columns in two tables:

Some of the other things I’ll cover:

  • Previewing and quick-reference stuff you can do with keyboard shortcuts
  • Quickly navigating your code to find errors or bookmarks

No undocumented stuff, no registry editor, and nothing you wouldn’t put into production. Just really clever productivity tips that will take some of the burden off your everyday work.

But I’m not going to sugarcoat it. Your code may still run slow, and you’ll probably write just as many bugs – but you’ll write them quicker and with so much more style and panache.

Why I Want to Present This Session:

On a serious note, these are things I don’t even consciously think about anymore – I just use them every day at work, and my clients and colleagues frequently ask me what I just did there. I want to share that knowledge.

Additional Resources:

Slides used in the presentation: go.strd.co/ssmslvlup

The answer to what “Execute […] without additional execution options” does, provided by Aaron Bertrand. TL;DR: leave it unchecked unless you need to change it.

Session Transcript:

Brent Ozar: In this session at GroupBy, we’re going to talk about the tool that probably all of us use every single day: SQL Server Management Studio. Daniel is going to show all kinds of tips and tricks that you can use to go be a better SQL Server professional. So take it away, Daniel.

Daniel Hutmacher: Yeah, for those of you who haven’t read or seen anything by me, I’m Daniel. Let’s take it from the beginning. I started working on SQL Server sometime like 1999, 1998. I seem to recall it was SQL Server 7, which to me felt like it was a cool idea to migrate after working with Microsoft Access. You can judge by my surprise when I realized I had to change all the DBA functions and everything. So either way, I’ve been a consultant for most of my life, which is close to some 20 professional years or so. I’m completely stuck in working with SQL Server.

I used to be a web developer, I don’t do that anymore. It’s all SQL Server. I just code T-SQL. As such, I spend a lot of time in Management Studio. I tend to end up in situations where I sit with customers. I do something, I show them something, a quick keyboard command, or I execute something. They’re like, “Wait, wait, wait. What did you just do there?” And I realize that this is not so obvious to everyone.

So for most of you people who are watching this session, it’s going to be something like a level 100 or a level 50. You’re going to recognize and know exactly what I’m showing you for 90% of the cases. But I’m thinking that every single one of you will probably see something somewhere in this presentation that will appeal to you. Where you’ll think like, “Oh, hey. This is interesting. Why haven’t I used this?”

I think Aaron Bertrand said, “Never code in a demo,” and that’s a pretty much everything I’m going to do. You’re going to see very few PowerPoint slides. I’m not even going to put them up on the site unless you really want it, Brent. But there’s not really much information in the PowerPoint. You can just check the video again when it comes up on YouTube.

Yeah, so just because I have to say it, SQL Server Management Studio used to be tied to SQL Server releases, which meant that when SQL Server 2012 was released, you got SQL Server Management Studio 2012. When 2014 was released, you got Management Studio 2014, etcetera, etcetera. With Azure and other developments, this had to change. So nowadays, Management Studio is a standalone tool. The latest version is 2016, right?

Brent Ozar: Mm-hmm.

Daniel Hutmacher: Which resembles Visual Studio 2015 a lot, in my opinion. You can see that these are pretty much the same product under the hood. Management Studio is backward compatible with all previous supported SQL Server versions, though I personally find that if you should run across an unsupported previous version, like 2005, it will probably still work.

Anyway, so I’m going to jump right in. This will appear like, pretty much, a large list of stuff that I’ve compiled that I thought of when I—I wrote the abstract first and then the presentation. Which is a good way to go.

Brent Ozar: Of course, of course.

Daniel Hutmacher: Yeah. “They’ll never bite. They’ll never bite, don’t worry… Oh, shit, I’m presenting.”

Brent Ozar: Everyone wants to see, yes, everyone wants—”Oh. Oh gosh. Here goes everything.”

Daniel Hutmacher: So the splitter, everyone has used the splitter. What you might not know is that if you want to make the splitter go away, the easy solution is just to drag it up to the top again. But it’s too simple and it gives you no control. So what I usually do, say you’re further down in the code, you’re in the thousands of line numbers somewhere. The pane that you want to keep, the upper or lower, you can choose that by putting the cursor on that pane, and when you have the cursor on the correct pane, you double-click the splitter and it resumes on that side. Let’s try that. If we scroll all the way up to the top, I place my cursor here now, and double-click, and boom. We’re in the 150s.

Brent Ozar: Nice.

Daniel Hutmacher: So that’s pretty nice. I just copied and pasted one of my big housekeeping scripts. Let’s see if this runs at all. Yeah. Let’s say we introduce some type of little error here somewhere. I’ll just remove this comma here just to break the script. Here’s another double-click. See, “Incorrect syntax near ‘reads.'” This is in a script of what? 1,500 lines or something and you might not even be in where that error happened. You can double-click this red text and that will take you to where the error actually happened.

That’s also something I use all the time. It doesn’t work with all the re-throw logic if you have tried CATCH, and in the CATCH block, you throw something, and if that’s nested or in a stored procedure, it won’t work. But whenever the error is clearly identifiable, just double-clicking the error message will take you to that line, the offending line.

Brent Ozar: A couple of people have already asked, “Wait a minute. What’s the splitter? How did you get there again?”

Daniel Hutmacher: Oh, okay, that level. I’m sorry, I was just assuming everyone is a DBA with multiple years’ experience.

Brent Ozar: Oh no, no. No, no, no.

Daniel Hutmacher: Right. So up here in the right end, up in the upper right corner, this is the splitter. It’s the same that you have in Word actually or in Excel.

Brent Ozar: Very nice. There you go.

Daniel Hutmacher: That splits the window. It’s still the same script. Everything here is the same script. I can execute the script and it’s as if you have two windows of exactly the same code, except…

Brent Ozar: Right. Or if for, like what you’ve got is a DECLARE of a table variable, and then later in the code—or a temp table—and then, later in the code, you’re working on that table and you’re like, “What are those fields again? What are the definitions?”

Daniel Hutmacher: Yes, exactly. So you might use [inaudible] here somewhere and you have to look up what are the column definitions. For that, it’s really, really practical.

Brent Ozar: J.D. says he’s embarrassed. He’s been working as a DBA for more than 10 years and he’s never known about the splitter. So there you go. J.D., you can leave the session, you’ve got enough.

Daniel Hutmacher: Okay, so you’re not developers, I suppose. I couldn’t set up a failover cluster if my life depended on it. I hope none of my customers are watching because eventually I’m going to set up a failover cluster.

Brent Ozar: Yes.

Daniel Hutmacher: If not else because I bought Brent’s excellent online course on setting up high availability and disaster recovery stuff.

Brent Ozar: Oh, cool.

Daniel Hutmacher: Which is really, really great. Thanks.

Brent Ozar: Oh, good. Glad you like them.

Daniel Hutmacher: So once again, going to double-click the splitter to make it go away. Keyboard shortcuts, this is what sells it for me. I’m not going to show you the slides because slides are boring. Whenever you select something, a table name for instance, or a view, or a stored procedure, you can press ALT F1. ALT F1 will show you the information on that object. This is pretty handy stuff.

What happens under the shell is it runs a special command in the master database called sp_help. So if I copy this and I write sp_help ‘sys.dm_exec_sessions’ and I run that, this is the output. It’s really practical, especially if you have to look up the column definitions or something on an object you’re working with. You get the object. You can see that it’s a view because it’s a DMV. These are the columns and their data types. You would see if there’s an index or all sorts of stuff. So this is something that I use all the time. I’ve used sp_help with the ALT F1 shortcut for as long as I can remember. It even worked in Query Analyzer. Anyone here worked with Query Analyzer?

Brent Ozar: Yes, sadly, yes.

Daniel Hutmacher: The cool thing is you can set your own query shortcuts. If you go to ‘Tools,’ ‘Options,’ and you look for ‘Environment,’ ‘Keyboard,’ ‘Query Shortcuts,’ you can actually enter pretty much anything you want to. The ALT F1 here, it’s hard-coded, you can’t change it because I suppose Microsoft says this is the way it’s supposed to be. But I tend to use CTRL F1.

Some of the neat things that you can do, it doesn’t have to be a stored procedure. Because under the hood—I’m not going to show you the exact stuff with profiler because I might lose all of those last viewers that are still on—it checks if this shortcut is a stored procedure or if it is a SQL statement. In this case, it’s a SQL statement. It says, “Select top 1,000* from,” I don’t know if you can see that. I’m going to show you how that looks. If I select any random arbitrary code here, say, the name of this DMV, and I press CTRL F1, to which I assigned that shortcut, it does a select top 1,000* from this table.

Brent Ozar: That’s perfect because I could use it to set up DROP TABLE and I can just drop tables very quickly.

Daniel Hutmacher: Yeah [laughs] and our job. The smooth thing is it’s all just passed through T-SQL. So I can add a condition, WHERE session_id>=50. If I mark all of this and CTRL F1, it will give me just the sessions of 50 or higher.

Brent Ozar: Spiffy.

Daniel Hutmacher: So this stuff I use continuously. Whenever I’m at a new machine with a new Management Studio, because clients won’t let me bring my own hardware into their environments, I press CTRL F1 and I wonder why isn’t it working? So to recap, you can use ‘Tools,’ ‘Options,’ ‘Environment,’ ‘Keyboard,’ ‘Query Shortcuts.’

What I’ve done is I’ve—I’m going to shamelessly plug one of my posts. I’ve developed a stored procedure which looks like sp_help but in my humble opinion, does a lot more. So it goes totally overboard in some respects. I’m going to use a different table because it doesn’t work well with DMVs, but it shows you the columns and plain text readable column definitions. You can pretty much just copy this and paste it into a CREATE TABLE statement. It gives you unique constraints, and indexes, and foreign key constraints, and everything ready-scriptable. So you just copy it into a new window. I even had a little attempt at some ASCII art. You can keep dependencies on different tables.

Brent Ozar: Oh, that’s cute.

Daniel Hutmacher: If you have a hammer, everything looks like a nail. I’m a hammer type of guy, right?

Brent Ozar: That’s cute.

Daniel Hutmacher: I don’t do anything else. I used to be able to code a pretty web page. Nowadays, it’s all Model-View-Controllers, and I don’t know the half of it. I couldn’t code a web page.

Brent Ozar: Oh, that’s excellent.

Daniel Hutmacher: Unless it’s in WordPress.

Brent Ozar: Yeah.

Daniel Hutmacher: So you could assign pretty much any shortcut you like to—one of the things I do is—this is a database name. I assigned USE to one of my keyboard shortcuts. Now, note up here, when I press CTRL 0, I change the database. Might not apply to you, might not be useful, but you can pretty much put any code you like in one of those shortcuts.

Brent Ozar: I like it.

Daniel Hutmacher: If there is an MVP or anyone out there who knows what “execute stored procedure shortcuts without additional execution options” means, I have no idea. I’ve Googled, I found Books Online information where the URL crashes, I have no idea what that means. Hit me up on Twitter.

Brent Ozar: I was going to say nothing has popped into Twitter or into Slack yet, so we’ll see if anybody comes up with it by the end of the session.

Daniel Hutmacher: Microsoft, call me.

Brent Ozar: Yeah, yes.

Daniel Hutmacher: Right, so yeah, next up. GO is a statement separator which is also pretty useful. You can use GO in Management Studio and most of you have probably seen this. You can SELECT 1 as x GO. All these scripts, when you script a table, or script indexes or stuff, it comes out like this with GO statements. GO isn’t, strictly speaking, T-SQL. That’s why I consider it part of Management Studio. I don’t technically know if it’s part of the protocol or—what it does is, it separates batches of statements. So I was wrong in saying statement separator, I mean batch separator. Let’s run this, and it returns a 1, so everything’s fine here. If I do DECLARE x int=1 and GO and SELECT x, that won’t work because you’ve declared the x here in this batch, and this is a new batch where x isn’t declared any longer.

So this is good to know, which also means that if you SELECT X/ (X-1) or whatever—it’s a bit contrived, I know. You add another statement here. If you run this, you will get a divide by zero error here. You can verify that by double-clicking. It gives us this error. But, then it will run the next batch. So GO means “We’re done, everything is forgiven. Execute the next batch.”

An unknown practical purpose of GO is that you can have it repeat code over and over and over. Typically, if you’re setting up demo environments or if you’re populating big tables with repetitive data, you can just add a number after GO and have it execute several times over. If I write GO 10, that means execute this batch 10 times. So you can see, I’ve got 10 results x batch. So that’s something I use pretty often just to populate temp tables for demo purposes or whatever.

Brent Ozar: Another person says, “I saw that in another session today and I’d never seen that before either.” So yeah.

Daniel Hutmacher: Yeah. I saw in Aaron’s session, he wrote, “IF EXISTS DROP TABLE dbo.a dbo.b dbo.c.” I had no idea you could do this.

Brent Ozar: That’s brand new in 2016. I think Aaron will correct me in Slack if I’m wrong. I think it’s DROP IF EXISTS.

Daniel Hutmacher: DROP IF EXISTS, that might be. The thing is, I just tried DROP TABLE and if I—I don’t have tables named A, B, and C, but you can parse it and it will look okay. This is a 2014 box, the instance I’m connected to right here. But DROP TABLE, A, B, C works, which is, “Oh. I learned something.” So that’s nice.

Brent Ozar: Yeah, Hugo Kornelis says the DROP IF EXISTS is new in 2016 but using a comma separated list of table names is not new. Who on earth is dropping so many tables that they need to put in a comma delimited list? Who are these people?

Daniel Hutmacher: Yeah, that’s a pathological drop behavior.

Brent Ozar: Aaron points out “DROP blank IF EXISTS” so it needs to be like DROP TABLE IF EXISTS. That’s the technical answer on that one, okay, good.

Daniel Hutmacher: Yeah, that’s going to be a spine reaction some time in the future. I’m going to do that everywhere, “DROP IF EXISTS.”

Brent Ozar: Yes.

Daniel Hutmacher: Technically, when you’re a developer, if you don’t—I’ve heard that there are people who don’t version their code in TFS and things like that. You have these long scripts that you save somewhere, and all those scripts start with DROP IF EXISTS or CREATE or ALTER or yeah. So you want to be able to run the script if the object exists or not.

Here’s another thing that I pretty much never use, but I can see how it’s practical for some purposes. Let me just copy this row and paste it in there. And I’m going to copy this row and paste it in there. Finally, I’m going to copy this row. It doesn’t have to work, I’m just copying rows at random. What you can do is instead of CTRL V, which is paste, you can press CTRL SHIFT V, which cycles your clipboard ring. You’re saying, “clipboard ring?” Yeah. Management Studio, and I suppose, Visual Studio, keeps track of the last—the mileage might vary, I’ve had 10 or 15 last click things that you’ve used. So if you press CTRL SHIFT V repetitively, it will cycle through all these things that I’ve copied in the past. I hope that none of these are compromising.

Brent Ozar: [Laughs] Right? Resume, or whatever. Man, every time I sit through an SSMS presentation I see this and I forget every freaking time. Every single time.

Daniel Hutmacher: This is a really nice feature, it’s really practical. While we’re on the SHIFT thing, I’m going to do my CTRL F1 trick again, just to select all the rows from the DMV. If I select a bunch of fields and I right-click, you can see that copy is CTRL C, but copy with headers is CTRL SHIFT C. Being in BI, which is what pays my bills, this is really, really handy when you copy with headers and you paste something into, say, Excel, especially if you have 35 column names or something. You get the column header as well as the values. You don’t have to select the top row, you can just select any row or any column anywhere. So CTRL SHIFT C will actually include the header names. This stuff, I use all the time. Definitely recommend it if you haven’t tried it.

There is a trick, another one. Say you just want to get a list of all the column names from this DMV. You can add WHERE 1=0. You can do the CTRL F1 or SELECT *. You get an empty result set. I place the focus, so I click in the empty result set. I click CTRL A to select everything, to select all, and CTRL SHIFT C to copy with headers. I’m copying an empty result set with headers and I can just paste it here, and I get all these column names.

Brent Ozar: Oh, that’s funny.

Daniel Hutmacher: There’s another trick for this, which is—might even be better, is that you can browse in Object Explorer. You can drag these columns here and that will give you a comma-separated list of all the columns, which it doesn’t get much prettier than this.

Brent Ozar: Yeah, it makes your life much easier, much easier.

Daniel Hutmacher: Yeah. Again, the coolness factor. Being a cyclist, it’s all about coolness, looking cool, and looking like it’s not an effort at all. Just, “This is what I do every day,” right? “I’m not trying to show off.”

Brent Ozar: Drag and drop. Drag and drop.

Daniel Hutmacher: I’m going to trust you to just fire up any questions that pop up.

Brent Ozar: Oh, yeah. The one thing they said is, so GO, the batch separator, you can change that under ‘Tools’ and ‘Options.’

Daniel Hutmacher: Oh.

Brent Ozar: Yeah, under ‘Tools,’ ‘Options,’ ‘Query Execution.’ So ‘Query Execution,’ keep going down, and it’s like four lines down. ‘Query Execution,’ ‘SQL Server,’ ‘General.’ Down, there’s ‘Batch Separator.’ So there’s two things that are funny about this. One is you can change it to something like SELECT and it will break people’s queries. So they’ll get incorrect syntax in their SELECT. My personal experience, [inaudible] also points out that you shouldn’t ever make it WHERE. Because it’s funny when their SELECTs take a really long time, but when they run a DELETE— the DELETE dumps the whole table. The other thing that I’ve seen—and I don’t know, I haven’t reproduced this on 2016 yet—but I’ve even typed in SELECT there to be funny and clicked cancel. And it took effect even though I clicked cancel. So just be really careful in the batch separator window.

Daniel Hutmacher: Ouch, that’s horrible. Okay, so going back to my really awkwardly huge script. If I select something and I click CTRL F, or if I just choose ‘Find.’ I don’t know, ‘Edit,’ ‘Find,’ I suppose? ‘Quick Find.’ I could just press CTRL F. You’ll notice that if I leave focus, if I place the cursor back in the editor again, I can just keep scrolling. At some point, I’m going to find—did I choose the one that I’m not using? Yeah, you see where this going. So it highlights all the searches, and if you’re like me, you don’t want to find the next by just clicking…

Brent Ozar: The arrow there.

Daniel Hutmacher: ‘Find Next.’

Brent Ozar: Yeah.

Daniel Hutmacher: You can just see, particularly, if you search for something that’s very common. Say you want to search for NOT NULL and CTRL F, it just highlights every column that is NOT NULL, which is, I find that really, really practical. That also goes for—The thing with the pre-population thing is that you can also have it look for strange characters. Try to enter a tab here, it doesn’t work.

Brent Ozar: Oh, yeah.

Daniel Hutmacher: But you can select the tab and CTRL F, and it will find all the tabs for you. You could even replace the tabs with three dots, or…

Brent Ozar: Spaces, yeah.

Daniel Hutmacher: Or spaces, or yeah. Yeah, if you don’t want to break your code, yeah, you could do that, I suppose.

Brent Ozar: I like that, I like that. You can do that with enters too, you can find and replace enters if you highlight the enters.

Daniel Hutmacher: You know, I’ve tried it when I wrote the—like this?

Brent Ozar: Exactly, yeah. Oh.

Daniel Hutmacher: I seem to recall that this has worked at some point, but I couldn’t—maybe I can copy this, and paste it in there… No.

Brent Ozar: Know I’ve got to fire up my SSMS to see what’s going on. All right, carry on.

Daniel Hutmacher: You probably have to use some type of…

Brent Ozar: I have to know now, I have to know. All right, you keep going.

Daniel Hutmacher: Yeah. So you can replace tabs with—like we had here with the column names. I can search and replace this tab with comma space. I’m lazy so I could click ‘Replace All,’ but I just click ALT A, but that’s personal preference.

What was I going to talk about more? Yes. Oh. Another really important thing: You can replace within a section. Note that if I find, or CTRL H for replace, for NOT NULL for instance, it will search the current document. So the pre-selected context is ‘Current Document.’ You could change this to look for ‘All Open Documents’ or the ‘Entire Solution’ if you’re using solutions.

What I like to do is select a block of code and press CTRL H, and it will only look for this value within this selection. So I can change this to NULL and it will only change my selection. It won’t touch the NULLs up here. So that’s kind of powerful.

Brent Ozar: Jim points out that he likes using regular expressions. If you use /N in regular expressions, you can do things like enters in the window like, “That’s powerful.”

Daniel Hutmacher: Yeah, I was going to see that coming. RegEx is scary. I don’t know RegEx.

Brent Ozar: Sean and Jen McCown seem to be the people to talk in the SQL Server community about that. They’ve had a couple of good sessions on it.

Daniel Hutmacher: Where was I going with this? Oh, yeah, we’ve looked at ‘Find and Replace.’ The ALT selection thing, that’s what usually kicks off this reaction like, “Hey, what, what, what?” When you make a section like this, it moves out on the end of the line and it continues on the next line, and so on. But if you hold down the ALT key while you’re making selections, you can make this type of selection instead, which is really, really nice.

So let’s find one where the indent is correct. Or, yeah, let’s just do this. So if I select this block of text, I can copy it. Here’s the kicker: I can make a selection like this, and I can paste it here. So, this is typically useful in repetitive code type of—where was I going with this?

Brent Ozar: Building a table, building a table is really common.

Daniel Hutmacher: Yeah, exactly. So if I move back to the correct database and I look for this table. Let’s say I want to compare columns from two different temp tables. I have a SELECT FROM a AS a INNER JOIN b AS b ON a.x=b.x. So when I look for all the columns in the A table and their respective counterparts in the B table, and I want to see if there’s a difference in any of them. I just list all my columns from the A table. I can use the ALT key and select this. You can see how this would be useful if you had 100 columns. I just type A, so I can go on typing here with this selection, which is really weird.

Brent Ozar: That is so cool, an army of marching typing.

Daniel Hutmacher: Yeah. Then I select with the ALT trick here, and copy. Make another selection like this, it’s just a vertical bar, I’m not actually selecting any cells. I can type b. and paste. I do the same thing here. Then, I can write something like ISNULL a. and paste, or something. You get the idea.

Brent Ozar: Yeah, cool.

Daniel Hutmacher: The trick is you can copy this, you can paste this, but you can also type. To me, this is—yeah, I use this all the time without even thinking about it. This is typically one of those things that I want to show in this session.

Brent Ozar: R.Z. is saying, “This one is absolutely amazing to me.” T. Friedman says he uses it all the time for pasting values from an IN-clause from Excel. You copy-paste the column in and then you can just put in a list of commas.

Daniel Hutmacher: Yeah, so I’m going to show that now.

Brent Ozar: Okay.

Daniel Hutmacher: If you copy all these values, I copy them here, and you can paste them, and—oops. We’ll just add a single quote there and you select this, add a single quote, or an apostrophe and a comma, and you just remove the last. Oh yeah, we’re going to need parenthesis. There, SELECT * FROM VALUES. Some indenting. ‘AS x [y]’ or something.

Brent Ozar: I didn’t know you could do that. SELECT * FROM VALUES, I’ll be doggone, I didn’t know that.

Daniel Hutmacher: Yeah. This works just like any table. But the thing is, you don’t have any column names, so you have to explicitly assign them. The columns go here just like with a column table expression.

Brent Ozar: Praveen says, “This is really amazing.”

Daniel Hutmacher: Yeah. Right. How much time do we have? I’ve been 35 minutes…

Brent Ozar: You’re the last one so you’re kind of okay.

Daniel Hutmacher: Yeah, I’m the last one but this is going to be a fairly short session because I can sense how people are just leaving for their Friday nights.

Okay. This is a really simple one. It’s also under the category “I don’t even think about it.” You could use your scroll wheel to move the cursor, but if you use a mouse, you might not want to reach for the mouse. You might just want to keep your hands on the keyboard. So, you can hold down CTRL and press cursor up and cursor down. If you don’t press CTRL, the cursor, obviously will move. If you hold down CTRL, you can just take a peek at what’s up there and still stay in one place with the cursor, which is nice. But notice that when the cursor reaches the bottom of the screen, it starts following you up. So it’s not perfect, but yeah. For those occasions where you’re working on this query and you forgot the definition of the table tran_locks, you can just scroll up with CTRL and the up and down arrows. So this is practical.

Also, I’m just moving right ahead here. I can feel, sometimes, that there’s not really this red thread that connects everything. I’m just going through my list of my cool stuff that I want to show people on the internet. So typically, you can right-click a table and you ‘Script Table,’ ‘Create To,’ ‘New Query Editor Window.’ This is really practical.

But what if someone wants you to script twelve tables? You can’t shift-click tables here, it doesn’t work that way. What you can do is you can click the parent container in the Object Explorer and click F7, we saw that in the PowerShell demo as well. Here, you can shift-click, and you can CTRL-click, and you can select multiple objects, and you can right-click, and use the context menus. Most of these context menus will actually work on these objects. I can script, create a table, for like seven or eight tables, and they all end up after each other’s. So you have one here. You have the next here. And so on and so on. You get them all in one nice script, which is, I think that’s fabulous.

Brent Ozar: Michael Ehrmantraut says this is a game changer for him, so yes.

Daniel Hutmacher: Glad to hear it. For me, I discovered this when I wanted to script jobs on one server and copy them to another, except I didn’t have just 10 jobs. I had something like 20, 30 jobs. You click on jobs, you click F7, and you select multiple jobs, ‘Script as’ ‘Drop’ by all means, and they’ll just script drop job for all those that you selected. Which to me, is—that’s a time-saver. Or you could just press delete, I suppose. I don’t dare do that because I actually want to keep these jobs, but I think you can actually press delete. You can also filter. You can add additional filters here because you’re in the Object Explorer details view.

Few more things to go. I was at a client recently. They had this complex active directory type of thing setup where you would use a DOS command called run as. Now, I’m in deep waters here. Only a few of our listeners will probably know what I’m talking about. So with run as, you can run a DOS command or a Windows exe with a number of parameters including you can set a different user context. So you can tell this—it’s pretty much equivalent to right-clicking an icon and ‘Run as User.’ You can tell it to use environments or not, or you can tell it to—yeah. I don’t know half of what these variables mean.

But we used it, and it gave us some problems because in SQL Server Management Studio, you will get the correct credentials when you log onto SQL Server. Then, you would open up another application. For instance, I would check in code with TFS. So I would open up Visual Studio to check in my code, except TFS and Visual Studio would run in the wrong user context. So what I started doing was I started adding all those tools, all those utilities that I use under ‘External Tools.’ So, what happens is when you go to ‘External Tools,’ and you launch an application .

Brent Ozar: I think we lost your audio there. We’ll see if it comes back. What I think he’s saying, and it probably is popping on his other monitor—did we lose your…? Are you back yet? He’s checking. I didn’t even know about tools, external tools. There you go. We lost about the last 90 seconds of audio there. The screen worked perfectly, but just not the audio.

Daniel Hutmacher: Oh yeah. I had the same with Aaron’s session.

Brent Ozar: Yeah, yeah.

Daniel Hutmacher: So if I add an exe file to external tools, it will inherit the user context that Management Studio runs under. So if you’re going to all this trouble running Management Studio under different security context, like under different user, or with ‘runas.exe,’ you won’t have to re-enter those credentials. You can start a new application within the same user context, or security context. I’m going to start Management Studio and a separate Management Studio as the user test. Here we go, CPU is maxing. Scary stuff.

Brent Ozar: Yeah, load testing.

Daniel Hutmacher: Yeah. I’m going to open up a new session. Notice that I don’t have my sleek settings here, just SELECT SUSER_SNAME. Not even CTRL E works. What is this witchcraft? It says “test” here, so I’m logged in with my test user. What can we do here? Oh, yeah, here’s one I made earlier. I actually added cmd.exe as my external tools. For those of you who didn’t see it, it’s under ‘Tools,’ ‘External Tools.’

Brent Ozar: Wow. That’s really cool.

Daniel Hutmacher: You can just add as many applications as you like. You could have Visual Studio, you could have Plan Explorer, you could have any number of utilities. Profiler, there’s already a shortcut under ‘Tools’ but you get the gist. So in this demo, I’ve added cmd.exe, which is the standard command prompt. Let me try to launch that. There. I’m going to write “whoami,” and I am test.

Brent Ozar: Wow.

Daniel Hutmacher: This is really handy if you have this really cumbersome process to log in on the network or authenticate for some reason, like in a mixed l./ad/cloud/onprem environment like my last client had.

Brent Ozar: Yeah.

Daniel Hutmacher: This is how I discovered that. I would just launch Visual Studio from the ‘Tools’ menu. I would just launch any DFX competitor tool or anything just—I had them all as a list of external tools.

Brent Ozar: That’s pretty slick.

Daniel Hutmacher: We’ve done with this. And, bring your own settings. I spent so much time customizing all my settings. You can probably tell. I don’t usually use these large fonts, but everything else is like customized to death, really. What you can do then is import and export settings. These are the Visual Studio settings.

Here’s a trick—or no, it’s not a trick, it’s a warning. Read carefully what it says because it’s not entirely intuitive. You can export selected environment settings. This is fine, this is a no-brainer. The options here are—by default, it doesn’t select the import/export settings because it’s like an Inception type of thing, it will loop back on itself. I’m not going to go into detail on that. You just click next, next, next.

You can choose which settings to export. I’m going to put this on my desktop, and “demo settings.” And, click ‘Finish.’ That’s it. We have a file on our desktop, which is… where is it? It’s 163k because it’s an XML file. I have this configuration file that I put in my Dropbox. Whenever I start a new client, whenever I remote desktop to a new server—I know you’re not supposed to run SSMS on a server. So everyone, don’t run SSMS on servers, but sometimes you have to. I can just take this with me and load the settings in, and I’ll be home. I’ll have my settings, my keyboard commands, my everything will just work. This is really, really practical.

It gets trickier when you want to import values. So “Import selected environment settings.” Note that it says, “Yes, save my current settings.” If you don’t read carefully, and I’ve done this, you might just ‘Browse,’ and you take ‘Demo Settings.’ This is the one I want to import, right? What do you mean ‘Replace?’ It’s a good thing it says so, but I think I may have overwritten settings with the standard settings. So just read carefully. If you don’t want to export your old settings, you just click “No, import new settings.” Here, you get the option to browse for your settings that you want to use. So this a gotcha that I fell into a couple of times until I got used to it.

I’m speeding along. I have one more thing to show you. It’s registered servers. I thought everyone was doing this, and it turns out I’ve hardly met anyone who uses it. Notice up here how—I’m not going to show you my clients, but these are my registered servers. These are shortcuts, pretty much. I can just double-click this one, and I was already connected. Double-click this, and I’m connected, or I could right-click, ‘Add a New Query.’

I don’t have to remember the SQL authentication. I don’t need to remember all those quirky connection properties. I can even register it with a color. So for production machines, it’s red. Test machines, I make it yellow. For development machines or toy machines, I make it green. That way, I get this just glance in SQL Server Management Studio if I’m at risk of making anything dangerous.

Word of warning, these colors can get messed up sometimes. I think it might be a bug somewhere, but if you try to right-click and change connection, and you take another server, and connect… I’m not connected to the VPN now. But if I connect to another server, it sometimes inherits this color. That way, you can sometimes overwrite these saved colors if you just use, ‘Connect to.’ Don’t trust the colors, but you can set them just by choosing connection properties and custom color.

Brent Ozar: Nice.

Daniel Hutmacher: Where was I going with this? Oh, yeah. To register, I’m going to create a new SQL Server group here. I’m going to call it Demo. Then, right-click Demo and ‘Tasks.’ No, I’m not—it’s ‘New Server Registration.’ Here, I can register whatever server I want. I can even give a plain text name, My SQL2014 box. I can add a lot of connection properties if I wanted a full database, which is useful for availability groups, for instance. I can choose a network protocol, encrypt connection, all those things, and save. Yeah, I was already connected.

Note that when I connect to this, it actually says in plain text what I named the server as opposed to the server’s actual instance name, which I think is a nice feature. If you don’t like it, make a habit of naming the registered server the same as the actual instance name. Here’s another cool feature: I’m going to register another server here. This is my local 2016 instance. I’m going to call this My SQL2016 box and save it. What do you suppose happens if I create a new query on this container?

Brent Ozar: Magic.

Daniel Hutmacher: That’s messed up. I just discovered this a year ago and it’s probably been there for decades. I’m going to SELECT @@SERVERNAME, which is a built-in variable that gives you the name of the SQL Server instance. I click ‘Execute’ and it returns two rows, and it has added the server name. Let me enhance that. So you have a server name column, which tells you where the rest of the results that came from, and it has the results that—so in this case, it executes this on each of those servers in this group. Which, to me, is it’s probably more of a DBA thing because you want to do maintenance, and diagnostics, or checks, or start some SQL Server Agent job or something on eight different servers at once. But I can see how a developer could use this in some sense also, I suppose.

Brent Ozar: R. Cline says this is how he likes to deploy scripts to multiple servers. He’ll just go run multi-server query and create the stored procedure like sp_whoisactive, sp_Blitz, whatever.

Daniel Hutmacher: Exactly. And, um, “Hello world.” Note that it returns the text output one server at a time. So you can see that My SQL2014 box says, “Hello world,” and my 2016 box says, “Hello world.” When I was toying around, I noted something. Note how the nanoseconds are the same on both instances. What are the odds that this query actually runs the very same nanosecond on two instances, on a tiny laptop? Someone might know.

Brent Ozar: Several people have got great uses for this. Tim says he likes to use this for disabling logins when people leave. Makes sense. I could see it for changing SQL authentication passwords across lots of boxes. Hugo says he uses this to find table and column names and stored procedures across multiple servers. I’m guessing he’s hitting information schema routines, or sys—I forget what that is.

Daniel Hutmacher: Good one.

Brent Ozar: Yeah, all right. No, go ahead, keep going now.

Daniel Hutmacher: Yeah, that’s pretty much it. So, I’m going to chime in with Pinal. Thank you so much for putting up this opportunity for even us who are not used to speaking at anything bigger than a small SQL Saturday somewhere in the outskirts of something. It’s a rush. It’s so much fun to speak here not only because like someone said, “This is totally going in my LinkedIn profile.” I have so many superlatives. Just thank you so much for putting us all together, and the SQL Server community is just the greatest.

Brent Ozar: It really is. I tell people all the time, everyone wants to get people up on stage. You want to get other people presenting. It’s all about giving back and sharing with other people around you. It’s probably the best way we could end up at the end of GroupBy, is talking about that. But we’re really all about letting people get up here and share what they know. It’s all volunteers. Everybody that you’ve seen today and yesterday or last week, it’s all about just giving back to the community in any way that we can. It’s so much easier here when we can do it from home, we can do it from anywhere in the world, people can just sign in.

Daniel Hutmacher: It’s great for professional development. I’m sure I spent, before I got my own business, I was employed for 15 years or something with a number of different employers who wouldn’t or couldn’t or just didn’t see the value in sending me on fancy conferences. Attending an online conference like this or a SQL Saturday is a great opportunity to just learn cool stuff. Just asking questions on Twitter with #SQLHelp. There are so many examples of how this community is unlike any other software community that I’ve seen anyway. It’s just a great place to be.

Brent Ozar: Yeah. Everybody, it’s not like everyone wants to hide their knowledge so you can’t get it. It’s not like a job security thing, “I can’t share my secrets.” It’s all very much about just sharing what you know. The same thing with the career thing. You should be amazed when, if people can Google you and find you, it opens up so many doors. When a hiring manager has a stack of resumes and they can Google you, and they go, “Oh. Well, this person really seems to know what they’re doing.” Oh, my gosh. It really opens a lot of doors.

Daniel Hutmacher: Yeah. It did cross my mind when I started blogging back in 2013. It was like, “Am I putting all my cool tricks and secrets out there?” No one will hire me because a) they’ll know exactly what I know and b) they won’t need me because it’s on the internet, right?

Brent Ozar: At the same time, everything we do is on the internet in one way or another. But it’s how do you find someone who can demonstrate they learned it and you can just grab them and put them in front of a computer, and let them do their thing.

Daniel Hutmacher: Exactly.

Brent Ozar: Well, thanks so much for sharing your time with us today. Really appreciate it. Folks, you can go ahead and go over to SQL Sunday. There were several people who asked how do they get CTRL F3, Daniel’s stored procedure that lists how to—all kinds of metadata. It’s all on sqlsunday.com.

Daniel Hutmacher: Yeah, it’s on sqlsunday.com. Look at the three or so most recent blogposts, or just go to ‘Downloads’ if you’re watching this in a year or two. On the downloads page, there is sp_ctrl3. Try it out. Don’t take code from people on the internet, but it’s safe for development environments. There’s no reason why you would need this in a production environment, but it’s safe for development.

Brent Ozar: That’s not how it works, you’re on the internet. Everybody immediately puts it into production. That’s how this works.

Daniel Hutmacher: Exactly. If you want to pay me cash money to work, my email address is down on the right. Yeah. Shameless plug.

Brent Ozar: Shameless plug. Subscribe to his blog as well over at SQL Sunday. Keep tabs on what he’s doing. Well, thanks for hanging out with us on Friday night. Thanks, everybody, for spending your Friday with us and we will see you at the next time. Adios, everybody.

Daniel Hutmacher: Thank you. Bye.

, , ,
Previous Post
Introduction to SQL PowerShell (the awesome new 2016 version)!
Next Post
Why Defragmenting Your Indexes Isn’t Helping

12 Comments. Leave new

Great session! There were several times my jaw dropped in astonishment. So useful!

Reply

Great Session Daniel, thanks for sharing! Where can I get the slides?

Reply
Chandram Venigalla
January 27, 2017 11:15 pm

Thanks Daniel, for sharing great tips

Reply
Markus Pöhler
March 10, 2017 7:10 am

Hi Daniel, the “Ignore extended options” checkbox below the keyboard shortcuts (in German “Verknüpfungen für gespeicherte Prozeduren ohne zusätzliche Ausführungsoptionen ausführen”) does simply IGNORE the settings that you can define in the SSMS Options on the “Query Execution” => “Sql Server” => “Extended” tab for the default execution. For example SET STATISTICS TIME ON by default can be set ther efor all executions. The checkbox in question will override it. I have just used profiler to discover that. Regards, Markus

Reply
Markus Pöhler
March 14, 2017 3:41 am

Hello Daniel, I have another question: I have inflationary used the keyboard shortcut definition option and defined some further user defined shortcuts there. After exporting the settings and re-importing them on other workstations and servers, the imported settings do not include the previously defined shortcuts, only the defailt settings.
Additionally I had created my individual toolbar with buttons for each of those shortcuts, displayed with text, so they can be clicked or just be used as a crib (to learn the shortcuts). However, after the import those buttons are present but -of course – absolutely useless and not working. Restarting SSMS does not help. Any idea what goes wrong with the export/import here?

Reply

    Yes, I did notice that the query shortcuts and other keyboard don’t always follow along with the settings file. I suppose you could export part of the registry, though I wouldn’t really recommend that for production machines. 🙂

    Reply
Alex Friedman
March 26, 2017 8:19 am

Good stuff! For something a little more robust for clipboard management, I highly recommend the free “ditto” tool. It’s life-changing 🙂

Reply

After working for about 10 years with SSMS, I learned a lot of new things!
It’s just AMAZING, Brent and Daniel THANK YOU!

Concerning the introduction of new tools and the launch of SSMS ‘as different user’, I also have a couple of “secrets” that I think will be useful to colleagues:
1. ‘Runas’ does not allow you to pass the password at startup, so try using ‘PsExec’ by Mark Russinovich (https://docs.microsoft.com/ru-ru/sysinternals/downloads/psexec);
2. If you need to further optimize SSMS, for example, repainting the execution window depending on the environment (test, prod, etc.), open previously closed query tabs(!), store preferred connections, etc. – pay attention to SSMSBOOST (http://www.ssmsboost.com/).

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu