How can I remove empty lines in SQL Server Management Studio (SSMS)?

I have many .sql files with lots of empty lines, for example,

WITH

  cteTotalSales (SalesPersonID, NetSales)

  AS

  (

    SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)

    FROM Sales.SalesOrderHeader

    WHERE SalesPersonID IS NOT NULL

    GROUP BY SalesPersonID

  )

SELECT

  sp.FirstName + ' ' + sp.LastName AS FullName,

  sp.City + ', ' + StateProvinceName AS Location,

  ts.NetSales

FROM Sales.vSalesPerson AS sp

  INNER JOIN cteTotalSales AS ts

    ON sp.BusinessEntityID = ts.SalesPersonID

ORDER BY ts.NetSales DESC

Is there a way to remove these empty lines in SQL Server Management Studio (SSMS)?

This is what I would like to have:

WITH
  cteTotalSales (SalesPersonID, NetSales)
  AS
  (
    SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
  )
SELECT
  sp.FirstName + ' ' + sp.LastName AS FullName,
  sp.City + ', ' 开发者_开发知识库+ StateProvinceName AS Location,
  ts.NetSales
FROM Sales.vSalesPerson AS sp
  INNER JOIN cteTotalSales AS ts
    ON sp.BusinessEntityID = ts.SalesPersonID
ORDER BY ts.NetSales DESC

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)

Good luck

I have gone through the below steps, and it worked for me.

1. Menu Tools → Customize → Commands → Add command → Edit → Delete blank line → OK.
2. The Delete Blank Lines menu appears in beside the File menu.
3. Open/Select Query - Click "Delete Blank Lines".
4. Enjoy.

You can use: Find and replace

• Find what: \n\n
• Replace with: \n
• use: 'regular expressions'

it is described here:

http://my.safaribooksonline.com/book/databases/microsoft-sql-server/9781617290473/using-regular-expressions-in-ssms/ch21lev1sec1#X2ludGVybmFsX0h0bWxWaWV3P3htbGlkPTk3ODE2MTcyOTA0NzMlMkZjaDIxbGV2MXNlYzImcXVlcnk9

It is not built in. The find and replace can be used with regex's and someone crafty may have a solution for that.

Menu Tools → Customize → Commands → Add command → Edit → Delete blank line → OK.

Redgate Sql Toolbelt is good for this. This package has Sql Prompt and Sql Refactor which allows easy formatting of your query (even from very very bad formatting). It will allow you to cut on spaces, move stuff around according to your needs.

Code completion As you type SQL Prompt provides unobtrusive support, suggesting appropriate keywords, tables, views, and other database objects. It even suggests complete join conditions based on foreign key constrains or matching column names. Where it makes sense SQL Prompt will complete entire statements for you, such as INSERT or ALTER VIEW.

SQL reformatting (Pro edition only) The Format SQL command reformats any SQL to match your chosen coding style. Clear and accurate formatting make it much easier to understand complex SQL, and helps maintain a consistent style across your entire team.

It's not free but definitely worth a try if you have budget for it.

Those of you finding that a regex find-and-replace of ^\n with blank doesn't work, I had the same problem because my file contained CR LF rather than just LF. In this case using ^\r\n instead worked for me.

Use Find and Replace with fine '^\n' and in replace leave it blank.

Additionally, check Use regular expression in the Find option.