InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 23:34:16 GMT20http://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxHey Tom,You make a lot of good points. Thanks for you comments.BenWed, 17 Feb 2010 05:03:09 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxI 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.Tue, 16 Feb 2010 12:20:59 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxI get you. Nothing against the article per-se. Was quite a good read, actually. :)Wed, 03 Feb 2010 20:27:49 GMTPaul Harvey-458236http://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxWell 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.BenWed, 03 Feb 2010 19:42:39 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxDont like the cumbersome approach in this article. We use Linked Servers, although aliases would be better.Wed, 03 Feb 2010 16:57:20 GMTPaul Harvey-458236http://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxNope. LV. Visited San Diego last summer.Wed, 03 Feb 2010 15:03:49 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxDo you live in San Diego? I grew up there. Or perhaps you live in Orlando.BenWed, 03 Feb 2010 13:20:35 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxI guess I was wrong about seeing their show live. I just remembered that they have a show at SeaWorld too.I will have to get down to the strip and catch one of their shows sometime.Wed, 03 Feb 2010 13:12:46 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxI have yet to see one of their shows "live." I like very much what they do/achieve. I thought it was fitting with location and job. Jason,I had the opportunity to see them twice. I have heard the show in Las Vegas is really crazy since they have water all over the place and you never know when they are diving into the water or landing on the stage. You are right sometimes work can be a circus.BenWed, 03 Feb 2010 13:04:47 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspx[quote][b]bkubicek (2/3/2010)[/b][hr]Hey Jason,I am glad you liked the article. By the way I like your sqlservercentral name. CirquedeSQLeil is very clever. I have seen their show, it was enjoyable.Ben[/quote]I have yet to see one of their shows "live." I like very much what they do/achieve. I thought it was fitting with location and job.Wed, 03 Feb 2010 12:47:36 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxHey Jason,I am glad you liked the article. By the way I like your sqlservercentral name. CirquedeSQLeil is very clever. I have seen their show, it was enjoyable.BenWed, 03 Feb 2010 12:38:58 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxBen,Thanks for the article. I have to agree with the others, sometimes the hoops we jump through (to make our job a little easier) are astounding. Nice solution.Wed, 03 Feb 2010 12:35:33 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxThanks for your suggestions on the replace. I did feel that part of the code was a little weak. Perhaps your suggestions will be in version two of this script.BenWed, 03 Feb 2010 08:32:09 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxHey James, glad you liked the article. I was pretty happy when I figured this out. I had been thinking about a solution to this for some time. It was nice when I finally figured one out.BenWed, 03 Feb 2010 08:29:54 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxI agree that there are better solutions, this script helps you when you have a legacy system that you didn't design. We all have those. Btw, be careful with the replace command. If you have "CREATE TABLE" statements in your stored procedures, this will change them to "ALTER" table statements.I solved this by finding the words CREATE PROCEDURE, CREATE PROC, CREATE TRIGGER, CREATE FUNCTION, etc with several variations (like 2 and 3 spaces between the words -- remember I didn't write these!). Regular expressions might be a better solution, but mine works.I made sure that my script replaced all known variations in production until the changed rows matches the number of procedures in production.Wed, 03 Feb 2010 08:08:54 GMTpbarbinhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxWe have a similar situation using SS2005, however we use synonyms. When I worked on SS2000, I used linked databases to provide a layer of abstraction.Wed, 03 Feb 2010 08:01:39 GMTAaron N. Cutshallhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxThanks for the article. I had to do a similar thing for our development and test environment. We have two databases in production that have views that point to the other database. Therefore, if we wanted to run 2 separate copies of these databases, we'd run into hardcoded names in the procedural code. So I wrote a similar script to make the changes. FYI, I used the Sys.Sql_Modules catalog view instead of sp_helptext.Wed, 03 Feb 2010 07:53:06 GMTpbarbinhttp://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxSigh...the ideas we need to come up with sometimes to make things easier. Thanks for the post Ben.Thankfully MS added Synonyms to SQL Server 2005 to make this process easier for everyone.-JamesWed, 03 Feb 2010 07:51:37 GMTjwalsh84http://www.sqlservercentral.com/Forums/Topic858419-2611-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Search/69253/">A Search and Replace Script when Moving Objects from Prod to Test</A>[/B]Wed, 03 Feb 2010 00:01:45 GMTbkubicek