June 18, 2015 at 9:45 am
I fixed this bug in the sp_helptext in 2002 in SQL2000 in the same way. I changed the length from 255 to 4000, but the problem is not solved completely. In some case, the "syntax error" returns.
June 18, 2015 at 9:45 am
But who is using sp_helptext in SQL scripts anyway? All DBA's are using
select definition from sys.sql_modules
where object_id = object_id('cProcWithLongStrings')
for any scripting.
For using any S.P. that returns values, you need to create @temp and run insert select. Won't direct select from a sys table be much easier?
And as some posters replied above, why to use old technology from an era of SQL Server 2000 and before? Back then they did not have such large stored procedures with so wide lanes of code.
June 18, 2015 at 2:17 pm
Setting the length to 4000 isn't fool proof, because some fool may have a longer line.
I agree with others, and would not be using this solution, but if I had too, I would adjust it to use word wrapping instead of just inserting a CRLF at a fixed point. Once you've hit your limit, backup to the last space and replace that space with a CRLF. And if for some reason, you've gone 255 characters (or maybe less if you want the output to better fit a screen/page) look for the next space and live with a line or two that are a little longer.
While I certainly won't say that I fully understand the entire procedure, I have a routine of my own that takes an input source and line width, and outputs word wrapped text, without being nearly as complex. Of course, I don't have much in the way of error trapping since I'm the only one who uses is 🙂
June 18, 2015 at 3:33 pm
I'm going to chime in on the 'Yay for sp_helptext' team.
The stored procedure might be old (and indeed in need for some TLC, thumbs up for the article!), it still gets its job done: it quickly shows me what's behind some call without too much fuss.
Yes, I know I can get the same information from the sql_modules table, but it's more typing and SSMS will truncate the output too.
Yes, I know I can get the same information from the object explorer, but it requires me getting my mouse and it's way slower, especially if you have lots of databases and stored procedures. (I know about filtering, thank you)
Yes, I know I need to use the CVS when I want to make changes, but 99 out of 100 cases I simply want to see what's there, not modify it. Having to go figure out what repository to use and what version might be installed is not my idea of 'quick', heck , the object explorer might turn out to be faster solution =)
FWIW: I have sp_helptext 'hidden' under CTRL-6 in SSMS. I browse through some code, want to see what's in a called function or view or stored procedure : select the text and press ctrl-6. If the output is big I might copy-paste it to a new window, but all in all my hands never leave the keyboard. The only sad thing about this is that CTRL-6 does not work on the result-(text)-pane. So if I want to dig even deeper I have to copy-paste it 'up' to sp_helptext something else.
It's true that the object explorer has many tricks up its sleeve, but productivity wise I don't think it is ever the fastest (or easiest) way to go. Those 'old' stored procedures still have a lot of merit IMHO.
FYI:
alt-F1 : sp_help (hardcoded)
ctrl-1 : sp_who (hardcoded)
ctrl-2 : sp_lock
ctrl-3 : SELECT TOP 1000 FROM
ctrl-4 : inhouse procedure that shows different syntaxes of selected object
ctlr-5 : inhouse procedure that shows sysmessages for selected number, or extended properties for selected object
ctrl-6 : sp_helptext
ctrl-7 : sp_helpindex2 (based on code from Kimberly L. Tripp)
ctrl-8 : sp_helpconstraint
ctrl-9 : SELECT [@@TRANCOUNT] = @@TRANCOUNT
ctrl-0 : sp_who2
That said, I *HATE* how with every new version (and move into Visual Studio) the short-cuts get hollowed out. ctrl-D? ctrl-M? ctrl-L? (!!), ctrl-U? etc...
June 18, 2015 at 3:41 pm
deroby (6/18/2015)
I'm going to chime in on the 'Yay for sp_helptext' team.The stored procedure might be old (and indeed in need for some TLC, thumbs up for the article!), it still gets its job done: it quickly shows me what's behind some call without too much fuss.
Yes, I know I can get the same information from the sql_modules table, but it's more typing and SSMS will truncate the output too.
Yes, I know I can get the same information from the object explorer, but it requires me getting my mouse and it's way slower, especially if you have lots of databases and stored procedures. (I know about filtering, thank you)
Yes, I know I need to use the CVS when I want to make changes, but 99 out of 100 cases I simply want to see what's there, not modify it. Having to go figure out what repository to use and what version might be installed is not my idea of 'quick', heck , the object explorer might turn out to be faster solution =)
FWIW: I have sp_helptext 'hidden' under CTRL-6 in SSMS. I browse through some code, want to see what's in a called function or view or stored procedure : select the text and press ctrl-6. If the output is big I might copy-paste it to a new window, but all in all my hands never leave the keyboard. The only sad thing about this is that CTRL-6 does not work on the result-(text)-pane. So if I want to dig even deeper I have to copy-paste it 'up' to sp_helptext something else.
It's true that the object explorer has many tricks up its sleeve, but productivity wise I don't think it is ever the fastest (or easiest) way to go. Those 'old' stored procedures still have a lot of merit IMHO.
FYI:
alt-F1 : sp_help (hardcoded)
ctrl-1 : sp_who (hardcoded)
ctrl-2 : sp_lock
ctrl-3 : SELECT TOP 1000 FROM
ctrl-4 : inhouse procedure that shows different syntaxes of selected object
ctlr-5 : inhouse procedure that shows sysmessages for selected number, or extended properties for selected object
ctrl-6 : sp_helptext
ctrl-7 : sp_helpindex2 (based on code from Kimberly L. Tripp)
ctrl-8 : sp_helpconstraint
ctrl-9 : SELECT [@@TRANCOUNT] = @@TRANCOUNT
ctrl-0 : sp_who2
That said, I *HATE* how with every new version (and move into Visual Studio) the short-cuts get hollowed out. ctrl-D? ctrl-M? ctrl-L? (!!), ctrl-U? etc...
+1
It seems that we work in the same team!
😀
June 18, 2015 at 4:05 pm
[sp_helptext] seems like a bad choice to get source code from stored procedures if you're a DBA or works with SQL Server 2005 or +. But for others that works as developers (like me), and in some cases don't have access to SSMS; or use others DBMS like Sybase Adaptive Server, [sp_helptext] would be a real lifesaver.
June 19, 2015 at 1:45 am
Just a few more points:
June 19, 2015 at 9:52 am
I'm going to chime in on the 'Yay for sp_helptext' team.
The stored procedure might be old (and indeed in need for some TLC, thumbs up for the article!), it still gets its job done: it quickly shows me what's behind some call without too much fuss.
Okay, I'm going to both stick to my original reply here and agree with @deroby.
Why? Because I am not a db admin, but a singular C#, C++ (etc.) programmer. Since I have no need to spend my time analyzing and repairing db objects, my only reason to have SSMS open is to work on the one or two scripts supporting my other code. Thus, excellent shortcuts (like the Ctrl- and Alt- ones mentioned by @deroby) provide little to no benefit for my workflow while in SSMS.
Now, at one time I was a Unix/C programmer, and programmable editor shortcuts were my lifeblood. However, I have been carried off with the currents that have moved many businesses into Microsoft fealty, and so I have now become accustomed to mouse clicks.
I figure this background would help explain my stance and show that I harbor no animosity toward the OP or any of the opinionated replies that seem to be accumulating; nor do I have any real preference other than to find the best methods for *my* workflow, as I am sure is the case with just about everyone else here. 😉
June 20, 2015 at 9:46 pm
I prefer Information schema when I need a copy of code. But I only use it to search across all objects looking for that something I can't remember which database it was in.
Thanks for the article.
June 22, 2015 at 3:17 am
Thank you guys. I have learnt a lot from your comments.
June 25, 2015 at 7:57 am
Thanks for the post.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply