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

http://connect.microsoft.com/SQLServer/feedback/details/668022/ssms-puts-extra-lines-in-the-result-set-in-grid-view-for-sp-helptext

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