Sql-server – One table used in two databases

sql-server-2008-r2

I have a common product table in db1 and db2. I want to make it one table because it has one inventory (stock of products). What is the best solution to share product table among two different database? I have few thoughts:

  1. Writing trigger on update/delete/insert one table and update another same time.
  2. Creating view on product table and used in second db (I don't know how)
  3. replication (I don't know how)

Can you guide me on the best way?

Best Answer

I would just make a synonym in db2 (assuming the table exists in db1). This will let you point directly to the source table without obfuscating behind a view, and will prevent you from needing to maintain a view if the schema of the table changes:

CREATE SYNONYM dbo.CommonTableName FOR db1.dbo.CommonTableName