February 3, 2010 at 7:42 pm
Well Paul, sorry you didn't like the article. Sometimes you just find yourself in a situation and you have to make the best of it. This was my solution of making the best of it.
Ben
February 3, 2010 at 8:27 pm
I get you. Nothing against the article per-se. Was quite a good read, actually. 🙂
February 16, 2010 at 12:21 pm
I see that the SP uses varchar(max) so the base must be at least SQLS2005, so probably you should not have considered using syscomments, which exists in 2005 and 2008 only for backwards compatibility and has been replaced by the sys.sql_modules system view.
Even if you use syscomments to get the text you can get all of it, not only 4000 characters as you suggested, because you can loop through the chunks (colid is the chunk sequence number for a multi-chunk object in syscomments) - but it would be a bit pointless given that sys.sql_modules is available.
Rather than changing create proc to alter proc you could prepend two lines to drop the proc and end a batch (create proc has to be the first SQL statement - apart from comments - in a batch) - this is less problematic than restricting the replace to the first 4 lines because SPs often have have the create proc statement after more than 4 lines of comment; and although matching "create proc" won't alter any non-proc create statements the first match you find may be in a comment so looping through lines until you get a match and then stopping is a risky too. Then there is no need for any sort of loop: neither to do the wanted changes, nor for changing "create" to "alter" - the whole thing becomes much simpler with no temp table and no loop controls, and this will make in perform a bit better. But given that this is probably something which isn't done all that often and isn't performance critical and your version using sp_helptext already works in your system there might not be much point in changing it.
Tom
February 17, 2010 at 5:03 am
Hey Tom,
You make a lot of good points. Thanks for you comments.
Ben
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply