How to remove empty lines in SSMS?

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

Answers


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


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


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


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

  1. TOOLS-> CUSTOMIZE-> COMMANDS-> ADD COMMAND-> EDIT-> DELETE BLANK LINE-> OK.
  2. Delete Blank Lines Menu appears in beside File menu.
  3. Open/Select Query - Click "Delete Blank Lines".
  4. Enjoy.

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.


Use Find and Replace with fine '^\n' and in replace let blank additionally check Use regular expression in Find option.


TOOLS-> CUSTOMIZE-> COMMANDS-> ADD COMMAND-> EDIT-> DELETE BLANK LINE-> OK


Need Your Help

AngularJS $http object not showing all headers from response

javascript ajax angularjs

I'm having a problem with AngularJS's $http service not returning all of the headers from the API I'm polling. Here's what I've got:

Optimizing for space instead of speed in C++

c++ optimization embedded

When you say "optimization", people tend to think "speed". But what about embedded systems where speed isn't all that critical, but memory is a major constraint? What are some guidelines, technique...