SP_HELPTEXT without formatting

Question

When a Trigger is compiled by TOAD for SQL, using the F9 button and not F5, it is inserted into syscomments with wrong formatting, as if it were a single line, where line breaks are disregarded. The problem is that when using sp_helptext to bring the text of the object, it searches in syscomments and consequently the result are broken lines without the slightest sense, and I can’t use this text to recompile the object.

If you access SQL Management Studio and do the following procedure on Trigger that was compiled with F9 in TOAD for SQL:

To modify a DML trigger Object Researcher, connect to an instance of the Database Mechanism and expand it. Tables and expand the table that contains the trigger you want to modify. Triggers, right-click the trigger to be modified and click Modify. Execute.

It opens a text editor with the correctly formatted Trigger.

My question is to find out what this SQL Manegement Studio Modify option performs so that you can format the object correctly. Because I need to take what sp_helptext brings, format in the right way, to be able to create this Rigger in another table.

Answer 1

Try using the Precedent below.

 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

Taken from Soen