How to remove line breaks in SSMS?

I am dealing with SQL that looks as follows

create procedure


as


begin

Notice the large gaps e.g. between as and begin.

How can I remove these? I used SQL formatter but this is not working.

In SQL Server Management Studio (SSMS) there is a command to Delete Blank Lines, though it is not available on a menu, toolbar icon, or keyboard shortcut by default.

If you have to do this regularly, edit your menus, toolbars, or shortcuts to add this command. For example, to add this command to the Edit/Advanced menu:

From the Tools menu, choose Customize:

In the window that appears, choose the Commands tab, then drop down the Menu Bar list to select Edit | Advanced:

Then click Add Command

Now select the Edit category and scroll the Commands list to select Delete Blank Lines:

Once this is done, Delete Blank Lines will appear in the Edit/Advanced menu. There are other options in the Customize dialogs shown above to position the command in the menu, add a keyboard shortcut, or add an icon for it to a toolbar.

You can do this with find and replace but be warned that if you have string literals with embedded line breaks these will be altered too.

The following finds all instances of consecutive line breaks and replaces them with a single one.

1. Ctrl + H to open the Find And Replace dialogue
2. Find What: n+
3. Replace With n
4. Tick Use -> Regular Expressions
5. Click Replace/ Replace All
6. Un-tick Use -> Regular Expressions to avoid confusing results next time you use that dialogue.

Not a direct answer, but useful and related.

If you are using SSMS more frequently, you can use number of auto-formatting tools for this. Usually they have setting to remove empty lines.

E.g. ApexSQL Refactor add-in is available for free and can do this with one of the preset formatting styles. Of course, this will change all formatting, not only remove empty lines.

You can do it using the regular expression in SSMS:

1. Ctrl-H to bring up the Find And Replace window
2. Select USE -> Regular Expressions
3. Put ^n in the Find What
4. Keep Replace With empty
5. Click Replace (All)

The new line often consists of the r n characters,
so in Martin’s reply try to replace the sub-point
2. Find What: (rn)+
3. Replace With: rn
or better in Nilesh’s answer try to replace the sub-point
3. Put ^(rn)+
This minimizes the number of occurrences of changes

SSMS, in regular expression mode, replace rn with blank string