SQL Server Management Studio (SSMS) is a wonderful tool but there are so many ways built-in shortcuts that many users don’t seem to be aware of.
A simple example, is if I want to script all the indexes on a table (or all the tables, all the stored procedures, etc, etc.). Yes you can do that the long way by using the Generate Scripts option but there’s a better way.
Let’s use the Purchasing.PurchaseOrderLines table from WideWorldImporters as an example. Here are the indexes on it:
The scripting options are well-known. You right-click the object, and can navigate to the scripting option:
But this is tedious when you want to script a whole set of them. Instead, start by clicking on the Indexes node itself:
When it’s selected, click F7 to open the Object Explorer Details pane:
Then click the top item, shift-click the bottom item (to select them all), and finally right-click to see that you can script all of them at once:
I hope that helps someone out there who wasn’t aware of this.
I love the versatility of the Object Explorer Details pane so much that I often refer to it as the WTF pane.
Yep, this and dragging the column names into the script window are the 2 best kept SSMS secrets.
Yes, was going to post about that next week too – it's a favourite
That's awesome.
But what I really need is a way to get the SSMS 2008 script drop/create functionality back. I used to be able to set the "test for existence" option on, and get basically:
IF exists (the thing you're trying to drop/create)
DROP theThing;
Create theThing;
But since they moved to the new SSMS, I can't seem to get this. I end up with the CREATE wrapped in dynamic SQL which I don't want OR NEED. If I'm scripting for Drop/Create, the CREATE is safe to run without a test for existence. This is highly annoying.
Is there any way to get this back?
Are you on 17.3 ? When I choose the "test for existence" thing, and choose either script CREATE or script DROP and CREATE, I just get pretty normal code with lots of IF EXISTS stuff. But the CREATE isn't dynamic.
Try scripting a view, and you'll see what I mean. They changed it around 2012, and I hate it… I want the 2008/2008R2 behavior. I have to keep those things around to do a lot of things (like restoring databases) because the newer versions have really mucked up the UI and do the wrong things and cause all sorts of problems.
Wow, I just tried it on a Stored Procedure using 17.3, and it creates broken code… a correct IF test on the drop, then a broken IF test with dynamic create, then an ALTER. UGH!
Sorry, the IF test for the drop is broken… there isn't one. Sorry. It just scripts a DROP (which breaks if it's not there), then scripts a dynamic SQL create of an empty stored procedure if it doesn't exist, and then ALTERS. UGH. This is why I have to keep SSMS 2008/2008R2 around… every version since has just been fundamentally broken like nobody actually USES the scripting features.
Is this against earlier versions that messes up? I'll have to try it against 2008. The code for a view on 2017 isn't pretty but would work.
For 2017, and a proc, it generates a DROP PROC IF EXISTS, then creates a skeleton proc, then does an ALTER PROC. That looks like it should work. So, I'm gathering the problem is that the test for dropping against 2008 isn't there? I'll check it out.
Yes the test for dropping isn't there. And I just don't like the nonsense it goes through when the 2008/2008R2 versions were so much cleaner, more logical, more desirable.
All I want is:
IF EXISTS, DROP.
CREATE.
It's simple, it works, it's safe, and it's re-runnable (run the script multiple times, it doesn't fail), and it works whether it's the first time it ever runs (the object doesn't exist yet) or the 10,000th.
I have no idea why they changed everything up and made it so much worse in subsequent versions. I have to keep SSMS 2008R2 around just to script out things (and to restore backups, but that's another thread… they completely broke restoring backups for 99% of my use-cases from 2012-2016… 2017.3 is mostly better, but still not as simple as before).
I presume they were trying to get it into the same batch. You can't logically say IF NOT EXISTS, CREATE PROC in the same batch without dynamic code until SQL 2016 SP1.
If there's some way to turn that off… I don't care if it's in separate batches, that's fine. I'd rather have clean, easy to use, easy to understand SQL scripted than the contorted nonsense that is currently produced.