I have 4 databases on a SQL Server. They have views that access tables across database boundaries. When I reverse engineer I get invalid views due to the situation. How do I implement views in ER Studio to support cross database joins such as these?
Thanks in advance,
The way we handle this is by creating a copy of the table from the second database in the same model as the first database. We flag these as "stub tables" using a table-level attachment. The problem with doing this is that you need to keep the tables replicated across models in sync. For our purposes, we're only concerned with referential integrity, so we only keep the primary key column(s) in the "stub tables" (which change less frequently).
I assume you change the DDL for the view to join to the local stub by removing the database reference leaving only a schema and table reference. I need all columns referenced by the view so PK only will probably not apply to my situation. My biggest concern is that the result can no longer be compared to the physical SQL database(s) due to the stub tables and code changes made to accommodate ER Studio.
We're actually using a different database platform (not SQL Server) which allows for the database name to be put in the Owner field in the table editor. I put together a test SQL Server model with two tables and a view. In the view DDL I included tables with DBName.dbo.TableName. The view validation seemed to ignore the DBName and validated correctly.