July 24, 2002 at 12:48 pm
All:
I have a problem for one of my SQL Server tables. So I want to re-create this table without lossing any constraints, indexes, triggers and relationship with procedures. the way i did is :
1. copy the data from original table A to new table B
2. re-create all constraints/indexes/trigger on B
3. rename A to C and B to A
4. after all of these, I found the only thing not right is the dependencies. The procedure used to depend on table A now it show to depend on table C, even though when I read its codes, the codes still call table A.
Will this cause problem? If yes, what is the simplest and proper way to achieve what I want?
Thank you in advance.
July 24, 2002 at 1:48 pm
Items like stored procedure work against the object_id for the table, not the name. If recompiled, they will fix the dependencies.
Steve Jones
July 24, 2002 at 2:52 pm
Steve:
How to re-compile a procedure?
July 24, 2002 at 3:21 pm
Steve:
I just used "sp_recompile" to compile the procedure and did not see the update of the table C back to A in my example. Any idea?
July 24, 2002 at 3:35 pm
Just realize in order to make the recompilation happens, I have to execute my procedure. Am I right?
July 29, 2002 at 11:23 am
try to use sp_recompile WITH RECOMPILE option.
this will recompile the Stored procedure every time it is excecuted.this will solw your process but once you get the desired result ,you can revoke back to old stage by using only "sp_recompile TableA"
July 29, 2002 at 2:19 pm
I work more with Sybase, the close cousin of SQL Server. Sybase also bases the dependencies on object_id unless the id is missing. It then uses the object name. So if you delete the previous table, the dependency is reestablished based on name with the next execution. Has SQL Server retained this functionality?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply