Tuesday, August 14, 2012

A workaround for the sp_helptext bug in SSMS 2012 RTM

Hello everyone,

If you use SQL Server Management Studio 2012 for sure you notice the behavior of sp_helptext when you get the results in grid mode and you paste it into a text editor or a new SSMS session. Basically the results have an extra line (carriage returns) between each line of code which is very annoying 


Please see the bug reported under the Microsoft Connect site. Please vote so we can have a permanent fix in the next Cumulative Update or Service Pack


Here is a quick workaround for this bug which I called sp_helptext2.

use master
go
IF EXISTS (select * from sys.procedures where name = 'sp_helptext2')
      DROP PROCEDURE dbo.sp_helptext2
GO
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
      SELECT Line FROM @ProcLines ORDER BY PLID
END
GO

6 comments:

  1. Another workaround is temporarily change the Results to Text instead of Grid. If you copy from there it works fine.
    Note: The bug still exists, all the extra blank lines are sorted above your text, but it is easy to live with.
    (Yes I voted for this on Connect)

    ReplyDelete
  2. Thanks. Note: you have a bug leaving out the last line if it doesn't end in return carriage: see http://stackoverflow.com/questions/11061642/sql-server-2012-sp-helptext-extra-lines-issue/13179699#13179699

    ReplyDelete
  3. Thanks for sharing this post. Your post is really very helpful its students.
    SQL server dba Online Training

    ReplyDelete