Hi Experts,
I have issue in sql server 2012 stored procedure.
When ever I'm using sp_helptext to open stp its create balnk horizontal space in stp. Please let me know how can I remove that existing spce from stp and how can I prevent stp for future.
Thanks in advance.
I've not been able to duplicate the problem.
STP = Stored Procedure? If so not seen that abbreviation before. Maybe its a Stored Template Gallery STP file in Sharepoint though ...
If its a Stored Procedure then sp_HelpText will introduce line breaks for any line that is more than 128 (I think?) characters. If a line is a few more than 128 characters but ends in space(s) then that would give you a blank line. Either way, long lines are going to have (incorrect) line breaks when output using sp_HelpText.
I have no idea why MS haven't fixed this (well, not in SQL2012 at least ...), its been like that for as long as I can remember --- SQL 6.1 probably!
If this matters then script the SProc in SSMS instead.
If the question WAS about Stored Template Gallery STP files in Sharepoint then I have no idea, sorry !!
You could try...
select object_definition(object_id('proc name'))
Then you can copy/paste the results to a query window. This will preserve the formatting of the procedure... unlike sp_hepltext
Is there an easy way to work-around the 8,000 character limit of Query Display tools?
I believe the 8K character issue happens with the print statement. When you use select and the object_definition function, you get everything. Then you can copy the result column to the clipboard and paste it to a query window. I couldn't find the upper limit for the print statement... but, I'm guessing it's 8000 characters.
Its a limitation of the GRID display in SSMS (and other, similar, tools). Can't remember what the default is, but I think it might be 255 or 8,000; mine is currently set to 65,535 - it will not take a larger value. I almost certainly have SProcs bigger than that 
Why sp_HelpText can't just chunk-up the data and SPLIT it into ROWS on actual linebreaks beats me. The only problem then would be individual lines that were longer than 8,000 (or 65,535) characters - which would be much less likely to occur.
Is there any way to remove current balnk lines fro my existing store procedur because it has many 1000 lines .
Use a Programmers Editor, or similar, and replace "\n\n\n\n\n" with "\n" repeatedly and maybe (but check them carefully) then replace "\n\n" with "\n"
You could do that with REPLACE if you like:
DECLARE @Sproc nvarchar(MAX)
, @replace nvarchar(1000)
, @BeforeLength int
SELECT @replace = CHAR(13)+CHAR(10)
SELECT @Sproc = object_definition(object_id('MySproc'))
SELECT @BeforeLength = LEN(@Sproc)
SELECT @Sproc = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
@Sproc
, REPLICATE(CHAR(13)+CHAR(10), 10), CHAR(13)+CHAR(10))
, REPLICATE(CHAR(13)+CHAR(10), 10), CHAR(13)+CHAR(10))
, REPLICATE(CHAR(13)+CHAR(10), 10), CHAR(13)+CHAR(10))
, REPLICATE(CHAR(13)+CHAR(10), 10), CHAR(13)+CHAR(10))
, REPLICATE(CHAR(13)+CHAR(10), 10), CHAR(13)+CHAR(10))
SELECT [Linebreaks removed] = (@BeforeLength - LEN(@Sproc)) / 2
SELECT [-- Sproc] = @Sproc
In the past I have scripted out the stored procedure using SMS, copy the contents of the query window paste into notepad ++ and use line operations in there to remove the blank lines.
Copy the newly formatted sql and paste it back into SSMS using alter proc 