We have a few views that go across multiple database. Each database is its own submodel in ERStudio. However, when I try to define a view in one submodel, it can't see any of the tables in the other submodel/database. If I try to edit the view DDL to use multipart identifiers, the view becomes invalid even though it is a valid sql definition. Eg) CREATE VIEW v4.vwCrosswalk ASSELECT codingScheme, eCodeKeyFROM Config WITH (NOLOCK) INNER JOIN [Exam]..ECode ECode WITH (NOLOCK) ON Config.configKey=ECode.configKeyWHERE Config.isDeleted IS NULL AND ECode.isDeleted IS NULLIs there no way to support this kind of view that may start in one DB but reference a table in another DB?
The only way to support a view that references a table in another DB is to copy that external table into your model so the reference can be valid. You can set the table to 'Do Not Generate' so it will never appear in any script generated from the model, nor in your Compare and Merge to the database.
Note that even though your view is currently invalid without that external table, it will still be generated in any DDL scripts.
I actually tried this, but there is still no way to specify the other db name using the multi-part identifier and still have the view DDL 'validated' in ERStudio. I even tried aliasing the table with DBname..Tablename, but that also doesn't work. In my example above, the 'Config' table is in the current db, but the 'ECode' table is in the 'Exam' database. I haven't been able to figure out a way to tell ERStudio that the table exists in this other database/submodel. I would like the view to be seen as 'valid' by ERStudio so that I would be able to tell if some schema changes made would have broken a view (like a column was removed from a table making the view invalid).
I just figured it out. The issue was that ERStudio requires specifying the schema portion 'dbo' instead of '..'. So as long as I specify dbo in the following DDL, it sees everything as 'valid' for the view definition.
CREATE VIEW v4.vwCrosswalk ASSELECT codingScheme, eCodeKeyFROM Config WITH (NOLOCK) INNER JOIN [Exam].dbo.ECode ECode WITH (NOLOCK) ON Config.configKey=ECode.configKeyWHERE Config.isDeleted IS NULL AND ECode.isDeleted IS NULL
Thanks for taking the time to post how you got it working!