SQLTeam.com | Weblogs | Forums

Remove Blank Space from STP


#1

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.


#2

I've not been able to duplicate the problem.


#3

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 !!


#4

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


#5

Is there an easy way to work-around the 8,000 character limit of Query Display tools?


#6

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.


#7

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 :frowning:

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.


#8

Is there any way to remove current balnk lines fro my existing store procedur because it has many 1000 lines .


#9

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

#10

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 :slight_smile: