192

Can I create regions in sql server editor (like #region and #endregion in C#) ?

Rauf
  • 12,326
  • 20
  • 77
  • 126

8 Answers8

327

Not really, Sorry! But...

Adding begin and end.. with a comment on the begin creates regions which would look like this...bit of hack though!

screenshot of begin end region code

Otherwise you can only expand and collapse you just can't dictate what should be expanded and collapsed. Not without a third-party tool such as SSMS Tools Pack.

Kols
  • 3,641
  • 2
  • 34
  • 42
Dog Ears
  • 9,637
  • 5
  • 37
  • 54
  • 4
    Ensure you have Outline Statement enabled under Text Editor > Transact-SQL>Intellisense>Enable Intellisense – GatesReign Apr 22 '15 at 17:10
  • 3
    https://technet.microsoft.com/en-us/library/aa225998(v=sql.80).aspx. Although all Transact-SQL statements are valid within a BEGIN...END block, certain Transact-SQL statements should not be grouped together within the same batch (statement block). Could anyone tell me why they should not be grouped? – Jacob Phan May 04 '16 at 04:26
  • 2
    Useful hack, but don't forget to add `go` after the `end` statement if you want to create multiple regions/sections. – marchWest Jul 03 '18 at 19:38
  • 1
    Fine! If you have comment block only. You have to add something with not comment within. for example: print – inon Jun 07 '20 at 08:18
  • 1
    This doesn't seem to work in every case, unless I'm missing something. It seems to break at least if you're using `CREATE SCHEMA`. – Panzercrisis Oct 05 '20 at 23:22
24

(I am developer of SSMSBoost add-in for SSMS)

We have recently added support for this syntax into our SSMSBoost add-in.

--#region [Optional Name]
--#endregion

It has also an option to automatically "recognize" regions when opening scripts.

Andrei Rantsevich
  • 2,879
  • 20
  • 25
11

BEGIN...END works, you just have to add a commented section. The easiest way to do this is to add a section name! Another route is to add a comment block. See below:

BEGIN  -- Section Name
/* 
Comment block some stuff  --end comment should be on next line
*/

 --Very long query
SELECT * FROM FOO
SELECT * FROM BAR
END
elixenide
  • 44,308
  • 16
  • 74
  • 100
BClaydon
  • 1,900
  • 2
  • 20
  • 34
7

It is just a matter of using text indentation in the query editor.

Expanded View:

Expanded

Collapsed View:

Collapsed

openshac
  • 4,966
  • 5
  • 46
  • 77
Bharath theorare
  • 524
  • 7
  • 27
6

Not out of the box in Sql Server Management Studio, but it is a feature of the very good SSMS Tools Pack

Andrius Naruševičius
  • 8,348
  • 7
  • 49
  • 78
Pero P.
  • 25,813
  • 9
  • 61
  • 85
4

No, #region does not exist in the T-SQL language.

You can get code-folding using begin-end blocks:

-- my region
begin
    -- code goes here
end

I'm not sure I'd recommend using them for this unless the code cannot be acceptably refactored by other means though!

Matt
  • 498
  • 1
  • 3
  • 14
3

I've used a technique similar to McVitie's, and only in stored procedures or scripts that are pretty long. I will break down certain functional portions like this:

BEGIN /** delete queries **/

DELETE FROM blah_blah

END /** delete queries **/

BEGIN /** update queries **/

UPDATE sometable SET something = 1

END /** update queries **/

This method shows up fairly nice in management studio and is really helpful in reviewing code. The collapsed piece looks sort of like:

BEGIN /** delete queries **/ ... /** delete queries **/

I actually prefer it this way because I know that my BEGIN matches with the END this way.

Jason Blevins
  • 658
  • 5
  • 17
andylize
  • 31
  • 1
2

Another option is

if your purpose is analyse your query, Notepad+ has useful automatic wrapper for Sql.

Tekin Güllü
  • 363
  • 1
  • 3
  • 18