0

For integration purposes I want to move a table to a different database B ( on the same server ) and then create a synonym of the table on the original database A so that all the objects referring to the table can still work as before the move. The problem I'm having is that to create the synonym I first have to delete the local table which It's not easy as it has several objects depending on it. Is there an easier way other than recreating ALL the depending objects to achieve this? In other words is there a way to "turn off the dependency check", drop the table, create the synonym and ... reactivate the dependencies which now find the synonym?

Hope it makes sense, thanks!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Paolo Aimetti
  • 1
  • This following has the info on copying tables from one db to another in `SQL-Server` - https://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server/187852#187852. Then I believe you can create a `VIEW` from the table you wish to reference, such as `CREATE VIEW NAME_OF_VIEW AS SELECT * FROM table1` – etch_45 Nov 18 '20 at 01:53

1 Answers1

0

I'm afraid I have to drop this idea. Probably "converting" the table into a synonym could be done by first dropping all the foreign keys, deleting the table and then creating the synonym; after this views referring to the table would work fine reading data from the other database through the synonym BUT I could not recreate the foreign keys as I now learn that you can not create foreign keys on a synonym ( similarly Cross database foreign keys are not supported ). due to this my whole idea is not gonna work and I will have to look into merging the two databases.

For sake of details: the table contains user accounts which need to be shared between two systems using two different databases; both systems must be able to add, update and delete user accounts and both systems have several tables which are referring to user records and which need a way to check referral integrity, foreign keys are required.

thanks for the attention.

Paolo Aimetti
  • 1
  • You do know you can have stored procedures on a server query tables in other databases by merely adding the database name in a prefix, right? Only a user account setting would prevent Database B from being able to interact with Database A in the same server; you can SELECT, UPDATE, and run stored procedures between them pretty easily. If they’re on different instances or servers, you’ll need to make a linked server, but then it’s also just as easy. ```SELECT * FROM DatabaseA.dbo.Users u INNER JOIN DatabaseB.dbo.Sales s on u.userid = s.userid ``` – CoffeeNeedCoffee Nov 18 '20 at 03:43
  • yes, thanks I'm very well aware about the full name referencing to access any object stored in a different database; my question was not about that and neither about how to copy the table to the other database (@etch_45 ); I was instead planning to use a synonym ( which would have referred to the remote table using the same syntax you suggested db.schema.object ) and my question on how creating the synonym without having to drop/recreate all the relations of the table to be moved,but then I got stuck at the stage of creating foreign keys between tables in two databases which is not supported. – Paolo Aimetti Nov 19 '20 at 04:10