How to remove empty lines in SSMS?

Question

I have many .sql files with lots of empty lines e.g.

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 ther a way to remove these empty lines in SQL Server Management Studio? 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

Answer 1

Not built in, the find and replace can be used with regex's and someone crafty may have a solution for that.

Answer 2

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

Answer 3

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.