|
Hello all, I have a requirement to move a large table in SQL 2000 from one database into a new one. I'm going to be using triggers to maintain referential integrity across databases. Some stored procedures will need to be updated to use the second db name as well. I can forsee that every time I create a new instance of these databases I'd have to edit a bunch of triggers and stored procs to point to the second db. Is there a way I can avoid (or minimize) having to manually edit each trigger and stored proc to use the full name of the databases each time they are deployed? Thanks!
(comments are locked)
|
|
I would use a view to reference the target tables in the other database. That way you only need to hard code the database name in one place, the view itself, rather than in each proc or trigger. In 2005 or 2008 I would use a synonym. I also suggest you think carefully about why you are using separate databases in this way. One reason would be that you want to use separate server machines for the other databases. That means you will be updating across the network. In such a scenario you might think about whether replication or mirroring would be a more robust solution. I would go with a synonym too, personally.
Dec 08 '09 at 10:34 AM
Matt Whitfield ♦♦
(comments are locked)
|
|
dillinzser is completely right and that should answer the direct question. If I can go a little outside the question, why are you separating the one table out of the database? If it is to separate that table onto a different harddrive, then you may be better leaving it in its current database and splitting that database into separate file groups. If it is to provide different different security for that table, then you may want to simply control permissions for that table. Or if you can possibly move up to a newer version of SQL then you can make use of schema-level security measures. There are definitely good reasons to move the table, and if so than dillinzser has the answer, but many reasons people move a table to another database have a better solution. Thanks Timothy - please see comments in my original question as to the reasoning.
Dec 08 '09 at 11:51 AM
Giablo
The comments make complete sense. There are definitely some good reasons to do it out there.
Dec 08 '09 at 04:22 PM
TimothyAWiseman
(comments are locked)
|
|
To get the current database's name you can use db_name() functtion: If you have to use other databases' names then it could be handy to create a table for cunfiguration settings so you have to update (or add, remove) these values only in one place.
(comments are locked)
|
Thank you all for the responses. I need to separate the DB's because of a contractual requirement to exclude sensitive data in the 2nd db from being backed up and stored indefinitely offsite due to privacy concerns. I looked at separating backups via files and filegroups but that could leave the 1st db in a state where it cant be restored if the only backup file recoverable was of the 1st db.
Btw the tables being separated contain large binary data as well so there is also the concern of reducing backup size.