I plan to create a copy of a TableA called TableA2. Then I want to rename TableA to TableA_old, then rename TableA2 to TableA.

I wanted to do this so that (1) I have a fresh copy of table A in another dbspace with faster drives and (2) in the process I will be recreating the indexes which should also get some performance benefit. Based on the documentation, I was assuming that any stored procedures that act on TableA will remain unaware of this hocus-pocus, but any views over it would need to be recompiled because they attach to tables via part number and not part name.

Someone has since told me that they believed all my stored procedures will still end up pointing to TableA_old and not the new fresh TableA like I want, but thats not documented in the manuals! Can someone weigh in on this and let me know? If I have to recreate all the stored procedures then it makes this process much more complicated then I first thought.

Hi,

Your plan should work, the things you should be aware of are:
- Constarints (primary/foreign key)
- Views
- Synonyms

All of the above are pointed to with the database internal unique identifier tabid (not the partnum). I think you need to recompile your procedures but you can do that with the update statistics fro procedure command.

Hope this helps,

Rob Prop