Hi Experts,
Is there any way to remove extra blank lines from stored code of SQL Server 2012.
Thanks
Are you trying to "minify" them? or something else perhaps?
Blank lines, and other whitespace, inside an Stored Procedure is benign and doesn't cause any problems.
We "minify" all our SProcs here because it makes it faster to deploy them and gives us a small measure of code-security by obfuscating the code (e.g. by removing all comments)
Actually my stp code was not more than 60 lines but currently it has around 3 lakh blank lines.
And the extra lines are increase automatically when we open stp code .
How are you opening it? Just in SSMS perhaps?
We save our SProcs in individual files, and they never change - unless we type something into the code.
We most of the time using sp_helptext to open stp.
sp_helptext will add a line-break at 128 character position (on any line longer than that), Maybe that is what is causing the problem.
sp_helptext will break the code at exactly 128 character position, so any line longer than that, which does not have benign white-space at that point, will cause either a logic-change (e.g. if the line break was in the middle of a string constant) or a compile error (e.g. if the break is in a keyword / object name)
Personally I wouldn't use sp_HelpText for this purpose (that said: I have no idea why this "error" still persists, after all these years, in sp_HelpText).
If you Right-Click - EDIT in SSMS you won't have this problem.
BUT .... IMHO much better to store all the SProc / Trigger / VIEW definitions etc. in external files and store them in a change-control repository. That helps with two developers working on the same SProc at the same time, and changes applied A-before-B and so on, not to mention being able to go back when you discover "That isn't right, I'm sure that was working OK before, I wonder what changed?".
Replace using regular expression, and
find line breaks at the beginnign of line: ^\n.
Repalce with nothing.
That will also find & replace any blank lines within constant strings. Might not be any of course ...
I prefer to only replace multiple-blank lines (e.g. where there are 5-or-more, rather than just one-or-two) rather than single blank lines