I currently have a SQL Server 2008 database in which I am planning to separate out some tables to other databases. I want to be able to replace all references to the separated tables from the original database using views. Is there a better way (other than manually changing all FK and SProc references) to switch all the dependencies to reference the view instead of the table?
+2
A:
Usually the best course is to rename the tables and then name the view what the table used to be named.
HLGEM
2010-05-21 18:31:22
If I rename the table all of the dependency's references automatically get switched to the new table, this is what I'm trying to avoid without having to manually change all FKs and Stored Procedures to point to the view.
jwarzech
2010-05-24 13:22:25
@jwarzech - I'm not sure what tool you're using, since SSMS does not do any such auto reference changing for you. You should be able to do exactly as HLGEM suggests.I wanted to add that you shouldn't trust the dependency list shown by SSMS, I frequently find that it's missing things. If you need to see where the (internal) dependencies are, generating a script to regenerate your whole DB and save that into a text file. Then search the text file for the name that's to be changed.
Chris Wuestefeld
2010-05-25 20:53:14
A:
You can try using sp_rename to change the name of the tables. I don't know if this will change your references also.
norlando02
2010-08-04 19:34:08