x

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?

Thanks!

more ▼

asked Dec 07 '09 at 07:32 PM in Default

Giablo gravatar image

Giablo
25 2 2 3

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 '09 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 '09 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 '09 at 10:24 AM

David 1 gravatar image

David 1
1.8k 1 3

I would go with a synonym too, personally.
Dec 08 '09 at 10:34 AM Matt Whitfield ♦♦
(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 '09 at 07:56 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

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)
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 '09 at 07:47 PM

dillinzser gravatar image

dillinzser
91 1 1 3

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x472
x401
x114

asked: Dec 07 '09 at 07:32 PM

Seen: 1725 times

Last Updated: Dec 07 '09 at 07:32 PM