0

I have 2 databases (db1, db2) - SQL Server. And each database use another in stored procedures, functions

in db1: 

select * from db2.dbo.users

in db2:

select * from db1.dbo.items

The problem is that there is hard coded db names in procedures. Now we want to add databases db1_test, db2_test on this server.

What is the easiest way to resolve this problem without updating all procedures, functions with new database names.

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
inser
  • 1,190
  • 8
  • 17
  • Without updating is not possible in the same server same instance. But you can update very quickly. First create a script of all the stored procedures and functions and views to a new query window. Then find(db1.) and replace(db1_test.) using **Find and Replace** (ctrl+F). Next apply the script to the new database(db1_test). Do the same thing for db2_test as well. – Kaf Mar 15 '12 at 16:04
  • There are no ways to create alias for database. – inser Mar 15 '12 at 16:15

1 Answers1

0

You can have it on a separate instance or server. Test and prod database shouldn't reside on the same box.. let alone same database. In an ideal world that is.

sam yi
  • 4,806
  • 1
  • 29
  • 40
  • Yes, I agree with you. But databases are devs for now. Both. – inser Mar 15 '12 at 16:04
  • "for now"? always try to develop as if it's going into production. will save you headache in the future. especially if it's not too much trouble. – sam yi Mar 15 '12 at 16:25