Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Format TSQL onto one line

Our company uses an old app which reads TSQL from a .INI file. Due to how the app process the INI file the TSQL has to be all on one line. I use Poor Mans TSQL Formatter to get everything nice and tidy for things like SPs, but am wondering if there's something out there to do the reverse - take nicely formatted TSQL and shove it all onto one line (removing carriage returns , line breaks etc). I'm working in SSMS but also use Notepad++, and will happily use some other editor if it has the functionality.

like image 793
KrustyGString Avatar asked Jul 18 '12 16:07

KrustyGString


People also ask

Can SQL be one line?

PL/SQL supports two comment styles: single-line and multi-line. Single-line comments begin with a double hyphen ( - - ) anywhere on a line and extend to the end of the line. Multi-line comments begin with a slash-asterisk ( /* ), end with an asterisk-slash ( */ ), and can span multiple lines.

How do I beautify a SQL script?

MYSQL Formatter allows loading the SQL URL to beautify. Use your SQL URL to beautify. Click on the URL button, Enter URL and Submit. Users can also beautify and remove the comments from SQL.

How do I indent MySQL code?

To indent code Select the text you want to indent. Press TAB, or click the Indent button on the Standard toolbar.


5 Answers

Using Notepad++ (Without any plugin)

After lot's of googling I found that there are no plugins like TextFX and PoorMansTSqlFormatter are available in x64 bit version of notepad++ even not needed.

Notepad++ --> Write Query --> Edit --> Blank Options --> Remove Unnecessary Blank and EOL.

enter image description here

That's it.

like image 60
Gopal00005 Avatar answered Oct 18 '22 22:10

Gopal00005


Using Notepad++

Select the statement that is over multiple lines then on the menu: TextFX>TextFX Edit>Unwrap Text

enter image description here

And for even greater ease you can assign it to a keyboard shortcut using the shortcut mapper (Settings>Shortcut Mapper)

enter image description here

like image 32
Martin Avatar answered Oct 18 '22 23:10

Martin


You can use the minify comment to remove all the unnecessary space in the Poor Mans TSQL Formatter

[minify]

[/minify]
like image 35
nobody Avatar answered Oct 18 '22 23:10

nobody


I like Martin's answer and that is probably the way to go. But I'll point out that you can just use string manipulation to turn carriage returns and line feeds into spaces. This is particularly easy in the later versions of SSMS that enable limted use of regex in the find/replace dialog.

like image 41
TimothyAWiseman Avatar answered Oct 18 '22 23:10

TimothyAWiseman


In SQL Server 2012:

  1. Highlight the selected text and use 'Find and Replace' (ctrl + h)

  2. Check: use Regular Expressions

  3. Find: \n

  4. Replace with: LEAVE BLANK

like image 40
PPJN Avatar answered Oct 18 '22 23:10

PPJN