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?
asked Dec 07 '09 at 07:32 PM in Default
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.
answered Dec 08 '09 at 10:24 AM
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.
answered Dec 07 '09 at 07:56 PM
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.
answered Dec 07 '09 at 07:47 PM