| Author |
Topic |
|
lcerni
Starting Member
2 Posts |
 Posted - 2007-09-26 : 14:45:56
|
| We are not only migrating from SQL Server 2000 to SQL Server 2005 but we are also changing our database structure (changing column names, datatypes, table names, etc.). Thus, our one developer who has about 100 stored procedures to change is attempting to find a way to change his procedures without having to go through line by line. I am hoping to find a 3rd Party Tool or code that takes a procedure from an old database with the old table/column names and rewrites it into a new procedure with the new table/column names in the new database.Can anyone help me? |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-26 : 15:10:10
|
| You can write a little tool to get the text from all the procs into a huge text file and do a replace. Run it in a loop for each of the tables. This is however risky and not guaranteed. You might have to run/test/fix is few times before you can arrive at a proper working tool. Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 15:33:27
|
We do this sort of column-rename relatively often. Using a decent Programmers Editor we rarely have any issues, and it doesn't take long.We store ALL Sprocs Tiggers and so on in individual files, so can easily do a Global Find across them all.HOWEVER, we long ago adopted a Unique Column Naming convention - that is to say that each column name is UNIQUE within the database. So we can safely change "foo" to "bar" in the sure knowledge that "foo" is only used for one column in one table. We don;t have a column called [ID[ in every table, for example  Also, we always use the column name for all working variables and application variables, so that makes the likelihood of finding all uses more "certain".None of which probably helps you in your current scenario, but I mention it just-in-case.Kristen |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-26 : 15:42:12
|
quote:
Originally posted by Kristen
We do this sort of column-rename relatively often. Using a decent Programmers Editor we rarely have any issues, and it doesn't take long.
We used to have an editor called Visual Slickedit at my old job and it was very good at doing this sort of thing.
Future guru in the making. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 15:54:24
|
And we still use Visual Slickedit for just this type of thing  Bit scary if you used to work here though .. .  |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-26 : 16:19:22
|
quote:
Originally posted by Kristen
And we still use Visual Slickedit for just this type of thing Bit scary if you used to work here though .. .
Unfortunately no, I'm sure I would be far more knowledgeable if I had worked at your place.
Future guru in the making. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-27 : 02:05:32
|
| Hahahha! I doubt it mate, but we probably would have shot some good pool at lunchtimes! |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-27 : 08:44:42
|
quote:
Originally posted by Kristen
Hahahha! I doubt it mate, but we probably would have shot some good pool at lunchtimes!
You hiring? I would move back to the UK, I grew up there!
Future guru in the making. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-27 : 09:13:34
|
| Not at the moment, sorry. Unless you are a specialist in School's software ... |
|
|
|
|
Rewriting Procedure from Old DB to New DB