Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Change Table Names in MSSQL 2000 and NOT break stored procedures and views!

Posted on 2004-10-08
10
Medium Priority
?
715 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:miamati
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 6

Expert Comment

by:boblah
ID: 12257470
Hi miamati,

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!
0
 
LVL 6

Expert Comment

by:graf0
ID: 12257512
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,'currentTable','newTable')

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.
0
 
LVL 6

Expert Comment

by:boblah
ID: 12257528
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Assisted Solution

by:BillAn1
BillAn1 earned 300 total points
ID: 12257531
Any automatic scripts are as likely to cause more problems than they solve. e.g. a script can be written to run thorugh all sp's etc to do a search and replace of 'currentTable' to 'NewTable', but then you run in to the difficulty of what about if you happen to also have a reference to a table called 'CurrentTableDetail' - this will get replaced with 'NewTableDetail' if you're not careful. Impossible to track down automatically is any dynamicSQL that may generate table names dynamically etc.  

A possible alternative approach if you have a lot of references to the table is to create a view onto the table with the new name, and then alter the permissions so that users can only see the view, and not the table.
0
 

Author Comment

by:miamati
ID: 12257578
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
0
 
LVL 6

Assisted Solution

by:graf0
graf0 earned 600 total points
ID: 12257632
In this case simple replacement should work fine. Just make sure that substring "abcEmployees" is not part of any other object name and try my code above (just make a backup first - just in case).
You can test for the object with substring "abcEmployees" in the name by running:
SELECT name FROM sysobjects
WHERE name LIKE '%abcEmployees%'
You can also test what will be replaced by running:
SELECT * FROM syscomments
WHERE text LIKE '%abcEmployees%'

HTH
0
 
LVL 6

Assisted Solution

by:OlegP
OlegP earned 300 total points
ID: 12259323
befor renaming check where are used your table
and change after renaming by hand

select
      c.[name],c.type,count(*)
FROM      
      sysdepends a
      INNER JOIN
      sysobjects b
       ON a.[depid]=b.[id]
      INNER JOIN
      sysobjects c
      ON a.[id]=c.[id]
where   b.[name]='MyTableName'
group by c.[name],c.type
0
 
LVL 2

Accepted Solution

by:
praveen_ms earned 300 total points
ID: 12260378
hi miamatI,

If you dont like GENERATING SCRIPTS AND FIND AND REPLACE and only want to do it programatically then you should write a DTS task to read the content of any depended objects using the above query and serach and replace using Replace function.

If wear your shoes I won't bother doing this I just do search and replace on one file.

To DO THIS:

GOTO SQL enterprise manage go to the database where your table and highlight any object and choose all taks-> Generate scripts and in the tab sheet select all View
0
 
LVL 34

Expert Comment

by:arbert
ID: 12265051
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.
0
 

Author Comment

by:miamati
ID: 12275070
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
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question