When sp_HelpText is not so helpful!

Question

When sp_HelpText is not so helpful!

allinadazework

SSCarpal Tunnel

Points: 4506
More actions
September 24, 2017 at 11:39 pm

#333007

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

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

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

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 1

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

CyclingYogi SSC Rookie Points: 34 More actions · Answer 2

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

Well spotted and thanks for item

gpolelo SSC Veteran Points: 230 More actions · Answer 3

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

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 4

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.

allinadazework SSCarpal Tunnel Points: 4506 More actions · Answer 5

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.

allinadazework SSCarpal Tunnel Points: 4506 More actions · Answer 6

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!

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 7

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

allinadazework SSCarpal Tunnel Points: 4506 More actions · Answer 8

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'))

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 9

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)

ianp SSC Veteran Points: 284 More actions · Answer 10

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

Avi1 SSCrazy Points: 2323 More actions · Answer 11

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:

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 12

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...

orer1983 SSC Rookie Points: 39 More actions · Answer 13

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

hemantkchitale Old Hand Points: 369 More actions · Answer 14

hemantkchitale

Old Hand

Points: 369

September 27, 2017 at 3:14 am

#1961110

WHAAT a Bug !