I think you should have a good look at replication, as many answers have stated, especially in a high-TPS environment or you want this on many tables. However, I will offer some code on how I achieve your stated goals in some of my systems using linked servers, synonyms and check constraints.
I would like to abstract out this common data to a single database but still let the other databases join on these tables, even have keys to enforce constraints, etc
You can setup a view or synonym in your databases to a common table in a linked server (or other local DB). I prefer synonyms if the view would've just been select * from table anyway.
A table synonym will allow you to run DML on the remote item if you have permissions.
At this point, though, you can't have a foreign key to your view or synonym, but we can accomplish something similar with a check constraint.
Let's see some code:
create synonym MyCentralTable for MyLinkedServer.MyCentralDB.dbo.MyCentralTable
go
create function dbo.MyLocalTableFkConstraint (
@PK int
)
returns bit
as begin
declare @retVal bit
select @retVal = case when exists (
select null from MyCentralTable where PK = @PK
) then 1 else 0 end
return @retVal
end
go
create table MyLocalTable (
FK int check (dbo.MyLocalTableFKConstraint(FK) = 1)
)
go
-- Will fail: -1 not in MyLinkedServer.MyRemoteDatabase.dbo.MyCentralTable
insert into MyLocalTable select -1
-- Will succeed: RI on a remote table w/o triggers
insert into MyLocalTable select FK from MyCentralTable
Of course, it's important to note that you won't get an error if you delete a referenced record in your central table.
