Database name as variable

[davejam]

Hi all,

I have hit a rather annoying problem in view of cross referencing databases.

I am currently trying to deliver an in house solution on the back of third party software.

My gui uses stored procedures from my reporting database, these stored procedures reference the third party database tables in the format of thirdPartyDB.DBO.TABLE1. this works very nicely and has fantastic speed in return.

My problem is that when setting up this third party databases on live and test servers they named them differently.

on live server they named it ThirdPartyLIVE and on test ThirdPartyTEST..... this makes migrating my development to live a REAL issue of having to go through every statement in every procedure to change this for live, then when further development happens the process will have to be completed again... and again!

It would seem easy to create a new database on the test server called ThirdPartyLIVE (which i have done) but the software is setup with its own test front end, pda connectivity and also other third party software connections for transferring data. This meaning it isn't going to be easy to recreate this for my new ThirdPartyLIVE database (infact I wouldn't know where to start, i just wrote my bit!!!)

So my question is.... is there a way to name a database as a variable and call this in a function so all i have to do is modify the database in one place, maybe even a table... then is will refer to the database without needing any further mod other than setting it up in the first place??

Any help or advice whether it be good or bad news is greatly appreciated.

Oh, database is sql server2005.

Cheers

daveJam

even my shrink says its all your f#@/ing fault

 

[gmmastros]

If you are using SQL2005 (or greater), you could create a synonym for your database.

http://technet.microsoft.com/en-us/library/ms177544.aspx

Then, change your code to use the synonym. If you want to change the underlying database later, all you need to do is recreate the synonym(s). This is a ton better than changing all the code.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Just to be clear. You can't create a synonym for a database, but you create synonyms for the tables you need to access. I assume there is a relatively small list of tables, so you would only need to modify a relatively small list of synonyms when you want to change the database you are using.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davejam]

Thankyou

i was looking into the possibility of synonyms after stumpling across them.

Spent half the day finding which tables i need (through 97 sprocs!!!), creates sproc to drop and create my synonyms for each database name corresponding to a passed variable (for ease)

The problem is i want to call them thirdpartyTable1 kind of convention rather than just table1..... this has since caused issue within procedures which use the table name to match rather than an alias.... more issues.

Am going to look into synonyms for the long term as i believe the databases are not going to change but they will still need updating and developing....

in the interim I was wondering if there was an easy find and replace across all sprocs within a single database within sql server.

I need a test system for monday and have already lost half a day working through this so need a 'QUICK FIX' in the interim!!!

Any Help??

daveJam

it works on my machine, so technically i win!

 

[gmmastros]

You can try this:

Basically, this is a query that searches through the text of your stored procedure and does some search & replace. The output of this query will be to the results part of the query window. You can take a look through the code it creates and copy/paste the output window to a new query window to run it.

This... "sniff test" is a safety precaution, just in case things don't work properly. I also encourage you to make a backup of your current database before doing this.

Before running the following code, you should set the results to text. Right click in the query window, click "results to", click "results to text".

Select  Replace(ProcedureText, 'TextToFind', 'TextToReplaceWith')
From    (
        Select Replace(Object_Definition(Id), 'Create Procedure', 'Alter Procedure') + Char(13) + Char(10) + 'GO' + Char(13) + Char(10) As ProcedureText
        From   sysobjects 
        Where  XType = 'P'
               And ObjectProperty(ID, N'IsMSShipped') = 0
        ) As X
Where   ProcedureText Like '%TextToFind%'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom