SQL server 2012 SP_HELPTEXT extra lines issue

Question

I am using SQL server 2012, & always use SP_HELPTEXT to get my previously created Stored Procedures, In previous versions of SQL server there were no issues in this process but in 2012, My Stored Procedures come with extra lines, for example this is the procedure that I wrote

Create proc SP_Test
as
begin
 Select * 
 from table_ABC
end

Now after using SP_HELPTEXT with this procedure (or any other procedure), I am getting this output

Create proc SP_Test

as

begin

 Select * 

 from table_ABC

end

Do any one else also facing this problem or I am the only one on this planet to struggle with this issue ?? Does any body know how to solve this issue ??

Configuration of my SQL server is as follows (copied from Help -> About )

Microsoft SQL Server Management Studio          11.0.2100.60
Microsoft Analysis Services Client Tools        11.0.2100.60
Microsoft Data Access Components (MDAC)         6.1.7601.17514
Microsoft MSXML                     3.0 6.0 
Microsoft Internet Explorer             9.0.8112.16421
Microsoft .NET Framework                4.0.30319.269
Operating System                    6.1.7601

Thanx in advance.

Solution 1

I can replicate this behaviour if I run sp_helptext with Results to grid set, then copy and paste the results from grid into a new query or any other text editor.

This seems to be a change in the behaviour of sp_helptext from previous editions, since this effect isn't displayed with standard grid result sets.

The simplest work-around will be to run sp_helptext with Results to text set (Query -> Results to > Results to text, shortcut CTRL + T.

You may need to increase the maximum number of characters per line in Results to text to get the output you expect - Tools > Options > Query Results > Results to text - set "maximum number of characters displayed in each column" to the maximum value of 8192.

Solution 2

A better workaround (compared to use Results to text) in my opinion is to create an sp_helptext2 storedproc as explained here:

http://sql-javier-villegas.blogspot.com/2012/08/a-workaround-for-sphelptext-bug-in-ssms.html

Note: that solution has a bug leaving out the last line if there is no new line at the end. Corrected T-SQL:

CREATE PROCEDURE [dbo].[sp_helptext2] (@ProcName NVARCHAR(256))
AS
BEGIN
  DECLARE @PROC_TABLE TABLE (X1  NVARCHAR(MAX))

  DECLARE @Proc NVARCHAR(MAX)
  DECLARE @Procedure NVARCHAR(MAX)
  DECLARE @ProcLines TABLE (PLID INT IDENTITY(1,1), Line NVARCHAR(MAX))

  SELECT @Procedure = 'SELECT DEFINITION FROM '+db_name()+'.SYS.SQL_MODULES WHERE OBJECT_ID = OBJECT_ID('''+@ProcName+''')'

  insert into @PROC_TABLE (X1)
        exec  (@Procedure)

  SELECT @Proc=X1 from @PROC_TABLE

  WHILE CHARINDEX(CHAR(13)+CHAR(10),@Proc) > 0
  BEGIN
        INSERT @ProcLines
        SELECT LEFT(@Proc,CHARINDEX(CHAR(13)+CHAR(10),@Proc)-1)
        SELECT @Proc = SUBSTRING(@Proc,CHARINDEX(CHAR(13)+CHAR(10),@Proc)+2,LEN(@Proc))
  END
  --* inserts last line
  insert @ProcLines 
  select @Proc ;

  SELECT Line FROM @ProcLines ORDER BY PLID
END

Solution 3

The answer posted by Rufo still produces blank lines. A little change in the last line of code solved the issue for me. Here is the edited code:

CREATE PROCEDURE [dbo].[sp_helptext2] (@ProcName NVARCHAR(256))
AS
BEGIN
  DECLARE @PROC_TABLE TABLE (X1  NVARCHAR(MAX))

  DECLARE @Proc NVARCHAR(MAX)
  DECLARE @Procedure NVARCHAR(MAX)
  DECLARE @ProcLines TABLE (PLID INT IDENTITY(1,1), Line NVARCHAR(MAX))

  SELECT @Procedure = 'SELECT DEFINITION FROM '+db_name()+'.SYS.SQL_MODULES WHERE OBJECT_ID = OBJECT_ID('''+@ProcName+''')'

  insert into @PROC_TABLE (X1)
        exec  (@Procedure)

  SELECT @Proc=X1 from @PROC_TABLE

  WHILE CHARINDEX(CHAR(13)+CHAR(10),@Proc) > 0
  BEGIN
        INSERT @ProcLines
        SELECT LEFT(@Proc,CHARINDEX(CHAR(13)+CHAR(10),@Proc)-1)
        SELECT @Proc = SUBSTRING(@Proc,CHARINDEX(CHAR(13)+CHAR(10),@Proc)+2,LEN(@Proc))
  END
 --* inserts last line
 insert @ProcLines 
 select @Proc ;

 --edited here. (where Line<>'')
 SELECT Line FROM @ProcLines where Line<>'' ORDER BY PLID
END

Even though this removes the "original carriage returns", but this is way better to live with. I migrated by DB from SQL 2008 to SQL 2012 and all the stored procedures (more than 200) were returned with around 5 carriage returns after every line of code when using sp_helptext.

The above code helped me resolve it.

Comments

[rufo]

@Dram: sorry - it worked for me for years. I just recently stopped using because it is not necessary if you have SSMS 2014.

[Toby Speight]

That sounds more like a method to post-process the output, rather than actually changing the output format.