How to find matching begin and end statements in SQL Server management Studio 2012?

Question

How to find matching begin and end statements in SQL Server Management Studio 2012? I have a very long stored proc with several levels of multi page nested if statements. The SQL is already formatted. My question is very similar to this one but I am using SSMS 2012 which now uses Visual Studio Shell. I was wondering if MS added this functionality or if there's an external tool or SSMS plugin that does it.

Otherwise I will have to resort to the solutions in the old question.

Solution 1

If anyone is still looking for an answer to this, it's CTRL+].

Solution 2

I seem to be able to get code collapsing for BEGIN / END no problem. Observe my settings under Tools > Options > Text Editor > Transact-SQL:

And note that I have IntelliSense enabled, with "outline statements" checked, under Tools > Options > Text Editor > Transact-SQL > IntelliSense:

The line numbers really help. If I collapse a line that contains a BEGIN, the next line number is the one after the END:

Solution 3

CTRL+] for jumping from BEGIN to matching END. Same key for jumping to matching BEGIN from the END.

CTRL+SHIFT+] to select the block of code as you jump between the BEGIN and END.

If you have installed SQL Prompt, clicking on BEGIN (END), highlights the matching END (BEGIN) in gray.

Solution 4

This is not an answer, I don't have enough reputation to comment yet.

Other than the solutions pointed in the other topic you mentioned I don't think there is a way. What I do is, delete the END, write the END again, it will grey out and the BEGIN that matches this ENDwill grey out as well. Then I just search through the code for grey words.

Solution 5

Function that you need is called Edit.GotoBrace and is present in SSMS v 2008 as well as 2012. It has different shortcuts, depending on language version of your ssms. I have SSMS2008 EN and SSMS2012 German, my keyboard layout is german. So Shortcuts are:

SSMS 2008 EN

Just find matching begin/end

Edit.GotoBrace Text Editor::Ctrl+´

the same, but also extend selection

Edit.GotoBraceExtend Text Editor::Ctrl+Shift+´

SSMS 2012 German

Edit.GotoBrace (Bearbeiten.GehezuKlammer) Text-Editor::Ctrl+´

Edit.GotoBrace (Bearbeiten.GehezuKlammer) Text-Editor::Ctrl+9

Edit.GotoBraceExtend (Bearbeiten.ErweiternbisKlammer) Text-Editor::Ctrl+Shift+´

Edit.GotoBraceExtend (Bearbeiten.ErweiternbisKlammer) Text-Editor::Ctrl+Shift+9

In SSMS 2012 you can use native keyboard shortcut manager to view/change the shortcut for this or any other command. But: Native command has one disadvantage: you have to be on begin/end to jump to paired token.

I develop a free add-in for ssms and have implemented yesterday improved function, that allows to jump to parent begin/end tag from any position in nested code. It will be available in next release. So if you like my idea - wait till next release. Add-in is called SSMSBoost. I have also used it to find quickly answer to your question: - it has "Dump SSMS Shortcuts" & "Dump SSMS commands" functions, which makes a full list of all available functions in ssms (and it allows to re-define shortcuts in SSMS2008, where shortcuts manager is abscent)

View more solutions

Comments

[Andrei Rantsevich]

Update: as promised, we have implemented "go to BEGIN/END" in SSMSBoost add-in. Now Ctrl+Shift-UpArrow will navigate to BEGIN and Ctrl+Shift-DownArrow will navigate to END

[David Atkinson]

Nice. I didn't know that. Has that always been there?

[J Brune]

I think it's been around for a while, just hidden.

[Matthew]

Very helpful thanks! Now I'm trying to get SQL to actually highlight the matching syntax pair - whether begin/end or { } or [ ]. Is there a way to do this?

[user1664043]

Just a caveat - SqlCmd mode on in SSMS disables that.

[Tony_Henrich]

SqlCmd mode disabling it is very annoying.

[Tony_Henrich]

it turns out that if query window is opened in SqlCmd mode, it doesn't work. Super annoying because it's on by default for me because I run quite a few SqlCmd scripts.