September 8, 2002 at 1:42 am
I need to replace a tale (says A) completely by save the old one to a new name (e.g. A_org) and re-create a new table with the original name (e.g. A). But how to handle the dependency adjustment? what is the best/easiest way to move the all the table dependencies (including constraints,triggers and stored procedures, etc.) from the old table (A_org) to the new one (A)? Or when renaming the old table, is there any way to rename the dependent constraints/triggers accordingly? I found that use the "Rename" does not handle all these things properly.
September 8, 2002 at 5:34 am
No easy way. Is the structure going to be the same? If so I'd recommend doing a select into to make a copy followed by a delete (or truncate if appropriate).
Andy
September 8, 2002 at 9:53 am
Actually, I believe if you right click the table in EM you will get a script of the table and all it' s related items except triggers that you can paste into QA or Notepad or other text field of an app. Then you script the triggers and do a rename of the table (may need to drop any foreign keys but I have never tried with those in place). Then you run your table script and all should be fine. Just read the script to make sure covers all you need.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 9, 2002 at 3:37 pm
The table structure is going to be changed. That is the main reason I need to go over all these mess.
I can manually to generate scripts from the original table and carefully manipulate it to restore all FK constraints and triggers from the original table to the new one. But one thing I don't know how to restore is the dependency of stored procedures. In the QA, if I rename the original table to be A_org, it automatically changed the dependency of procedures to A_org even though in the real execution, it looks for A instead.
What can I do to reflect the dependency change in QA so that we won't be confused by the display in QA? (I use "sp_recompile, does not change the dependency display in QA).
September 9, 2002 at 6:55 pm
EM should handle it for you. The trick is to avoid sp_rename I think, it is the one that is changing the dependencies. If you change a table structure in EM and save script instead of applying the changes directly, that will generate code that should come pretty close to doing everything you need to do. I'm sure you've thought of this, but I'd recommend making a copy of the db (with empty tables maybe) and keep testing your script until it runs exactly as you want it.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply