Linking tables between databases

Question

Hi,

I’m after a bit of advice on the best way to go about this is SQL server 2008R2 express. I have a number of applications that are in separate databases on the same server. They are all “plugins” that use a central staff/structure list that will be in a separate database. The application is in the process of being migrated from JET.

What I’m looking for is the best way of all the “plugin” databases being able to see the central database and use those tables in standard queries and views etc.

As I’m using express that rules out any replication solution and so far the only option I can think of is to use triggers or a stored procedure to “push” out all the changes to the plugins. The information needs to be populated on a near enough real time basis however the number of changes will be very small maybe up to 100 a day and the biggest table only has about 1000 rows at the moment (the staff names table).

Hopefully that will cover all everything but if anyone needs any more details then just ask

Thanks

Answer 1

Apologies if I've misunderstood, but from your description it sounds like all these databases are hosted on the same instance of SQL Server - it's your mention of replication that makes me uncertain.

Assuming that's the case, you should be able to replace any copies of tables from the central database which are held in the "plugin" databases with views or synonyms which reference the central tables directly, since SQL server allows you to make references between databases on the same server using three-part naming (database_name.schema_name.object_name)

For example, if each plugin db has a table StaffNames, you could replace this with a view by dropping the table, then creating a view:

drop table StaffNames
go

create view StaffNames
as
select * from <centraldbname>.<schema - probably dbo>.StaffNames
go

and your code should continue to work seamlessly, as long as permissions are set up.

Alternatively, you could replace all the references to the shared tables in the plugin databases with three-part name references to the central database, but the view method requires less work.