When sp_HelpText is not so helpful!

  • Comments posted to this topic are about the item When sp_HelpText is not so helpful!

  • Another "bug" is when you rename a stored procedure and run sp_helptext.
    It returns the create of stored proc with the old name:
    create procedure A
    as
    select 1 as a
    go
    exec sp_rename 'A','A_OLD'
    exec sp_helptext 'A_OLD'
    Result:
    create procedure A
    as
    select 1 as a

  • Interesting (and not a little amusing) "feature".

    Well spotted and thanks for item

  • The best way is to print the results to text not grid.

  • I concur with GPolelo.
    Another thing about SP_HelpText - and maybe its just me or some weird config that I am rolling, but SP_HelpText, when printing to GRID and copy/paste immediately afterward - it adds a CRLF to every line.  So when you go to PASTE somewhere, like to another SSMS query window,  you now have double the rows. that make up your script.  It becomes especially more annoying when you alter said script with all the extra lines.

  • gpolelo - Monday, September 25, 2017 6:34 AM

    The best way is to print the results to text not grid.

    I agree with printing to text, but you do get the same issue with results to text. Unless I'm much mistaken.

  • Carlo Romagnano - Monday, September 25, 2017 1:48 AM

    Another "bug" is when you rename a stored procedure and run sp_helptext.
    It returns the create of stored proc with the old name:
    create procedure A
    as
    select 1 as a
    go
    exec sp_rename 'A','A_OLD'
    exec sp_helptext 'A_OLD'
    Result:
    create procedure A
    as
    select 1 as a

    Thanks. If I wasn't a little nervous of using sp_helptext before then I'm definitely nervous now!

  • select definition from sys.sql_modules
    where object_id = object_id('MyTestProc')

  • SQL Guy 1 - Monday, September 25, 2017 8:37 AM

    select definition from sys.sql_modules
    where object_id = object_id('MyTestProc')

    SELECT OBJECT_DEFINITION(OBJECT_ID('MyTestProc'))

  • gpolelo - Monday, September 25, 2017 6:34 AM

    The best way is to print the results to text not grid.

    Obviously, you did NOT test it yourself. Change it to Text mode, and you still get the same result as if you use the Grid mode. (Tested in SSMS2016 ver 13.0.16106.4) against a sql server 2016 Dev Edition (ver 13.0.4466.0)

  • You can always go to Menu: Query,
    Then Query options
    On the results node select text and set the maximum number of character to (say) 4000....
    HTH

  • Carlo Romagnano - Monday, September 25, 2017 1:48 AM

    Another "bug" is when you rename a stored procedure and run sp_helptext.
    It returns the create of stored proc with the old name:
    create procedure A
    as
    select 1 as a
    go
    exec sp_rename 'A','A_OLD'
    exec sp_helptext 'A_OLD'
    Result:
    create procedure A
    as
    select 1 as a

    This was one of the feedback to Microsoft but, they closed it, not going to fix. :angry:

  • I've used sp_helptext successfully with stored procs for years
    However just last week, I saw the wrapping behavior twice when using it to script table valued functions and I'm quite sure the lines were no where near 255 characters.
    I just tried it again and of course it worked perfectly.

    I give...

  • Not likely to happen, but definitely possible.:w00t:

  • WHAAT a Bug !

Viewing 15 posts - 1 through 15 (of 15 total)

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