SP_HELPTEXT without formatting

Navigation
0

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

If I access SQL Management Studio and do the following in the trigger that was compiled with F9 in TOAD for SQL:

To modify a DML trigger Object Browser, connect to an instance of the Database Engine 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. Run.

It opens a text editor with the trigger formatted correctly.

My question is to find out what this Modify option in SQL Manegement Studio performs so that it can format the object correctly. Well I need to get what sp_helptext brings, format it correctly, to be able to create this trigger in another table.

    
asked by anonymous 28.08.2015 / 20:11

1 answer

0

Try using the procedure 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

Retrieved from SOen

    
28.08.2015 / 21:03
Use java to connect in WebService Magento API SOAP v1? Problem with rest controller in Spring