June 4, 2009 at 10:49 am
Hello All
I am searching for a string (collate for collation settings)from all procedures, I have found all the procedures which use collate within a join, but instead of opening them one by one and removing them, I was wondering if there is a way to automatically do this ?
Thanks.
June 4, 2009 at 1:04 pm
There is a nice tool I have used called WildEdit that applys reqular expressions to search and modify a set of files. The tool comes from the same folks that make TextPad.
There are some limitations on the free download but it will do a lot. The licensing is reasonable and worth the effort.
The only draw back is that you need to be able to define your changes in terms of regular expressions.
June 4, 2009 at 1:14 pm
Output all your procs into a single script. Modify the script so that it gets rid of the string for you (Ctrl-H in Management Studio ought to do the trick). Change all the create commands to alter (Ctrl-H again, but be careful with it so you don't end up changing anything inside the procs). Run the resulting script.
Test it on a dev copy of the database first, but it should do what you need.
Used to have a database with over 2,000 procs in it (over 400 tables). Had to make a change that affected literally hundreds of them once, for a really good system-wide enhancement. Used that technique. Took me literally five minutes to build the script and give it an initial test run. Took longer than that to test the results in the database, of course. When I was happy with the tests, ran the script in QA, and so on. Very fast, very easy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply