Cross Database Reference - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cross Database Reference

Author

Topic

davinder2406
Starting Member

1 Post

Posted - 2011-08-18 : 09:54:03

I have two Dbs ABC and XYZ
In XYZ DB i wrote 100's of stored procedures and functions in which i am using some queries which refer tables of

ABC DB for eg: select firstname from ABC.dbo.tbl_Details;(this is a SP in XYZ DB)
Now at the time of deployment in production Db or in future, Database name is different like ABC is now DEF. Now the

problem comes:

How can i change DB name in all places, consider sql server performance while answering

I have so many solutions in place but confused which is the best soltuion which will not effect SQL Server

performance and the code should not be hard coded.

Solutions are:
1. We can create Synonyms but synonyms are created at object(table) level so the number of synonyms created will be

high & will be equal to number of tables

Refer
Using Synonyms:
http://msdn.microsoft.com/en-us/library/ms190626(v=SQL.100).aspx
Understanding Synonyms
http://msdn.microsoft.com/en-us/library/ms187552(v=SQL.100).aspx

2.Store the DB name in DB table and select that in variable in stored proc .(In case of DB name is changed, only DB

table need to be updated and no impact on code).

It will effect the sql Server performance.

3. Code needs to be scripted ( SSMS -> Database ->Tasks -> Generate Script) and renamed (Find and replace) in the

script.

i guess this is not a best solution, might be there are some cases where this will not work

4. Create a function like getParamValues ( Paramname) which will return the value for the corresponding Parameter

Name passed.
Call this function in the code.

It will become hardcoded and it would have some performance hit

5. Create Databse project but its a CLR based solution this also not suits with problem
Refer:
http://msdn.microsoft.com/en-us/library/bb386243(v=vs.80).aspx

Please reply asap.

Thanks in advance.

Kristen
Test

22859 Posts

Posted - 2011-08-18 : 10:19:30

I would do (3) because when we release upgrades to our production databases they are all scripted - so we just run the scripts - so it would not be hard to include a Find&Replace step on the scripts. need to make sure that the ABC name is 100% unique and will never be found as part-of some other object's name. If they are always surrounded by [xxx] what would help. We tend to use names like [MyDatbaseSource] in the scripts so that they will be absolutely unique.

Kristen
Test

22859 Posts

Posted - 2011-08-18 : 10:37:12

Alternatively:

Create VIEWs on the current database pointing to tables on the remote database:


CREATE VIEW dbo.ABC_tbl_Details
AS
SELECT *
FROM ABC.dbo.tbl_Details
GO

and then


select firstname from ABC.dbo.tbl_Details

becomes


select firstname from .dbo.ABC_tbl_Details

then when you release to a server with different database names you have to change all the VIEWs to "point" to the right target database.

I suppose this is the same as your Synonyms Point (1) ... so probably no additional merit. Sorry, I'll get back in my box ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources