miamati
asked on
Change Table Names in MSSQL 2000 and NOT break stored procedures and views!
I want to change table names by using strored procedure (or other method) for example:
EXEC sp_rename 'currentTable', 'newTable' and at the same time I want to change embedded scripts pointing to such table/s in existing stored procedures and views. Can this be done in some programmatic (sp?) way as if you use EXEC sp_rename only the tabe name is changed and scripts in sp/views will not work unless I go through them one by one...a massive piece of work indeed in this case :(
thanks,
m
EXEC sp_rename 'currentTable', 'newTable' and at the same time I want to change embedded scripts pointing to such table/s in existing stored procedures and views. Can this be done in some programmatic (sp?) way as if you use EXEC sp_rename only the tabe name is changed and scripts in sp/views will not work unless I go through them one by one...a massive piece of work indeed in this case :(
thanks,
m
It's tricky and unsupported but you can replace object names in the code of existing stored procedures and views by modifying system table syscomments.
I've never tried that myself but that's theoretically possible:
UPDATE syscomments
SET text=REPLACE(text,'current
I would make a backup first - it might be risky and you can end up replacing some things you did not intend to replace (for examle when "currentTable" is also part of something else, like "sp_load_currentTable" - that would be replaced as well).
You will also need to run sp_refreshview on all views as their definitions change.
I've never tried that myself but that's theoretically possible:
UPDATE syscomments
SET text=REPLACE(text,'current
I would make a backup first - it might be risky and you can end up replacing some things you did not intend to replace (for examle when "currentTable" is also part of something else, like "sp_load_currentTable" - that would be replaced as well).
You will also need to run sp_refreshview on all views as their definitions change.
miamati,
You're better off dumping the script to a text file and doing proper parsing using a proper programming language - doing simple search and replace is almost bound to go wrong.
You're better off dumping the script to a text file and doing proper parsing using a proper programming language - doing simple search and replace is almost bound to go wrong.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All comments posted gave good feedback but what I missed to explain is that I need to change ALL tables in db and respective scritps in sp/views but in my case the change would only be as follows:
from tbl:
abcEmployees
to tbl:
xyzEmployees
This should make things simpler no as surprise replacements should be avoided?
m
from tbl:
abcEmployees
to tbl:
xyzEmployees
This should make things simpler no as surprise replacements should be avoided?
m
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Agree with some of the above comments--generate scripts, search and replace and run the scripts. Don't edit/change the system tables, you're asking for trouble. Don't rely on the sysdepends information, it doesn't always get update correctly.
ASKER
Managed to solved issue with edit replace in external editor...however all comments proved to be useful as I had to apply them in other instances....will split the points :-)
tks
m
tks
m
You could generate scripts of all your objects, then write a program in vbscript or something else to parse through them all, changing the table names you find, then re run the scripts to update the objects on the database, but that would be a huge amount of work too, probably more than doing it by hand.
There is no automatic way of doing it provided by SQL server.
Cheers!