Easy way to manage full database name in stored procedures and triggers?

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?


more ▼

asked Dec 07, 2009 at 07:32 PM in Default

avatar image

25 2 2 5

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.

Dec 08, 2009 at 11:50 AM Giablo

Btw the tables being separated contain large binary data as well so there is also the concern of reducing backup size.

Dec 08, 2009 at 12:48 PM Giablo
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Dec 08, 2009 at 10:24 AM

avatar image

David 1
1.8k 3 5

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 07, 2009 at 07:56 PM

avatar image

15.6k 22 49 38

Thanks Timothy - please see comments in my original question as to the reasoning.

Dec 08, 2009 at 11:51 AM Giablo

The comments make complete sense. There are definitely some good reasons to do it out there.

Dec 08, 2009 at 04:22 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

To get the current database's name you can use db_name() functtion:

SELECT db_name();

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.

more ▼

answered Dec 07, 2009 at 07:47 PM

avatar image

91 1 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 07, 2009 at 07:32 PM

Seen: 2279 times

Last Updated: Dec 07, 2009 at 07:32 PM

Copyright 2016 Redgate Software. Privacy Policy