store output of sp_helptext to local variable

  • Hi,

    I have a stored procedure mySP and I'd like to use a local variable to store the ouput from sp_helptext to that variable. However, I got the error "... Incorrect syntax near "mySP'."

    declare @v varchar(8000)

    set @v = sp_helptext mySP

    Is there is better way to do such and to avoid the error?

    Thank in advance.

  • It's not pretty, but you might try something like this:

    declare @t table(line varchar(8000))

    insert @t exec sp_helptext 'myProc'

    declare @ddl varchar(8000)

    select @ddl = ''

    select @ddl = @ddl + line

    from @t

    print @ddl

  • For some reason, this does not work for me. Keeps telling me that I have to "[font="Courier New"]declare the scalar variable @ddl[/font]"

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • duh. Its a typo, should be [font="Courier New"]@ddl[/font] on the select line.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • sorry about that. I've edited the original post to fix the error.

  • Thanks for the example Ralph. I was just mad at myself for not noticing the typo before I posted my reply...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I realize this post is old, but I found it handy for a problem I was working today.

    One thing I added was to change the CREATE to an ALTER. This was to facilitate backing up an existing object.

    Here is what my finished code looks like, should it help someone else.

    SET NOCOUNT ON;

    USE <DATABASENAME>;

    DECLARE

    @objName varchar(128);

    SET @objName = <OBJECT NAME>;

    /* Script out objects if they exist*/

    IF EXISTS (SELECT *

    FROM sys.objects

    WHERE object_id

    =

    OBJECT_ID(@objName)

    AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT',N'P'))

    BEGIN

    DECLARE

    @t TABLE(line varchar(8000))

    END;

    INSERT INTO @t

    EXEC sp_helptext @objName;

    DECLARE

    @ddl varchar(8000);

    SELECT @ddl = '';

    /* Change CREATE to ALTER because this object all ready exists */

    SELECT @ddl = @ddl + REPLACE(line, 'CREATE procedure', 'ALTER PROCEDURE')

    FROM @t;

    PRINT @ddl;

  • I also realize that this post is really old but below is a script I put together that could be helpful to others running across this post. This script finds the lines for every stored procedure or function.

    IF OBJECT_ID('tempdb..#SP_Lines') IS NOT NULL

    BEGIN DROP TABLE #SP_Lines END;

    CREATE TABLE #SP_Lines (SPName varchar(200), Type varchar(4), LineNbr int, LineText text)

    DECLARE @SPlines TABLE(Type varchar(4), LineText TEXT)

    DECLARE @SP_name varchar(150)

    DECLARE @SP_type varchar(4)

    --LOOP through each SP

    DECLARE dbfn_cursor CURSOR FOR

    SELECT O.NAME, O.TYPE

    FROM sys.objects O

    INNER JOIN

    sys.sql_modules S ON O.object_id = S.object_id

    WHERE O.TYPE IN ('P','FN')

    ORDER BY O.TYPE Desc, O.name

    OPEN dbfn_cursor

    FETCH NEXT FROM dbfn_cursor INTO @SP_name, @SP_type

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --SELECT @SP_name

    INSERT INTO @SPlines (LineText)

    EXEC sp_helptext @SP_name

    INSERT #SP_Lines

    SELECT @SP_name As SPName, @SP_type As Type, ROW_NUMBER () OVER(Order BY (Select 1)) As LineNbr, LineText

    FROM @SPlines

    DELETE FROM @SPlines

    FETCH NEXT FROM dbfn_cursor INTO @SP_name, @SP_type

    END

    CLOSE dbfn_cursor

    DEALLOCATE dbfn_cursor

    SELECT *

    FROM #SP_Lines

    ORDER BY SPName, LineNbr

     

  • seequl wrote:

    I also realize that this post is really old but below is a script I put together that could be helpful to others running across this post. This script finds the lines for every stored procedure or function.

    There is no reason to use a CURSOR here.  This can be done quite simply without the very expensive overhead from a CURSOR.

    SELECT O.NAME, O.TYPE, OBJECT_DEFINITION(O.OBJECT_ID) AS ROUTINE_DEFINITION
    FROM sys.objects O
    INNER JOIN
    sys.sql_modules S ON O.object_id = S.object_id
    WHERE O.TYPE IN ('P','FN')

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen wrote:

    seequl wrote:

    I also realize that this post is really old but below is a script I put together that could be helpful to others running across this post. This script finds the lines for every stored procedure or function.

    There is no reason to use a CURSOR here.  This can be done quite simply without the very expensive overhead from a CURSOR.

    SELECT O.NAME, O.TYPE, OBJECT_DEFINITION(O.OBJECT_ID) AS ROUTINE_DEFINITION
    FROM sys.objects O
    INNER JOIN
    sys.sql_modules S ON O.object_id = S.object_id
    WHERE O.TYPE IN ('P','FN')

    Drew

    If you use this method to display in a grid you should also make sure from the SSMS menu option Query\Options that "Retain CR/LF on copy or save" is checked.

    Capture

     

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply