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