Database - how to create regions in TSQL (SQL Serv

问题:

Is there a way to create collapsable regions in the SQL script file like we create in VS using #region ..... #endregion ?

--EDIT--

Script file contains DDL statements like Alter Table and Alter View etc. and i want to group them in regions like "Table Related Statements" "View Related Statements" etc.

回答1:

Yes, with SSMS Tools Pack

Regions and Debug sections

Regions behave in the same way as in Visual Studio. You can collapse them and expand them. Debug sections are sections that get commented when you change your script to Release configuration. A debug section is also a collapsable region. If you deploy a script in debug mode with added debug sections it will fail when run from SSMS without SSMS Tools Pack installed. You can of course comment those sections yourself by simply searching for start and end text of the debug sections

回答2:

No, but you can simulate it in SSMS by using begin..end blocks as follows:

--Region 1
begin
  --Do Something
end

--Region 2
begin 
  --Do Something Else
end

The begin..end pair is collapsible.

EDIT:

This only works for DML scripts. DDL scripts are more picky as many statements require to be the first in the batch (original question did not state DDL use.)

I don't think there's a way to do this within one script file for DDL - you're into breaking down the script into smaller scripts or stored procs.

回答3:

I have added regions support into my add-in SSMSBoost (starting from v 2.12)

Syntax:

--#region [OptionalName]

--#endregion

回答4:

to respond to the "Alter must be the only statement in the batch" error, you may "seperate" your Begin and End batches with the GO command, that way you'd avoid this message, like so:

begin 
--do something
end
GO

begin
--do something else
end
GO

and so forth