25

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.

26

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:

Tools menu window

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

Customize window

Then click Add Command

Add Command selection

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

Add Command window

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.

1
  • 3
    In fact, if you explore what commands are in there, you can create shortcuts for all kinds of editing options. It makes me wonder whether you could almost create a bunch of settings to make the SQL editor window behave like vi or emacs...
    – Rob Farley
    Dec 31 '15 at 8:37
23

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.

enter image description here

0
1

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)
1

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. ApexSQL Refactor

0

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

0

SSMS, in regular expression mode, replace \r\n with blank string

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.