Is there a way to visually display a database view in a database diagram?

Question

Similar to this question, is there a way to visually display a database view in a database diagram?

Answer 1

Yes, though not using SSMS's Database Diagrams.

My solution for this is Microsoft BI's Data Source View (DSV). This requires that you have Microsoft's BIDS (Business Intelligence Development Studio), a component of SQL Server Standard (or higher) installed.

BIDS 2005 through 2016 should all work and provide the same basic functionality.

Alternatively, if you want access to a free version of the toolset, you can download and install SQL Server Express with Advanced Services ( basically SQL Express with Reporting Services added), though I have not worked with this version to verify.

1. With BIDS installed, launch it (or Visual Studio, if you have a more full version installed)
2. Go to File > New Project
3. In the New Project dialog, under Templates, expand Business Intelligence and choose a project. (I believe any BI project type will do. If you don't see any BI Projects, then you most likely do not have BIDS installed or at least installed with that version of Visual Studio).
4. Provide a location for this project to live on your machine.
5. Click OK to create the Visual Studio and BI Project.
6. In the Project Explorer pane, right-click the Data Sources folder and select New Data Source. Follow the Data Source "wizard" to create a connection to your desired data source. In the 2014 (and other?) version of BIDS, there is a question about the security credentials you'd like Analysis Services to use to connect to the data source. If you are just wanting to create a DSV for diagramming only, it doesn't matter what you select here.
7. Right-click the Data Source Views folder and choose New Data Source View. A Data Source View wizard launches. Using the wizard, add the tables and views you would like to see in your data source view to the Included Objects pane using the left and right arrows.
8. Click Next to complete the table/view import and to give your DSV a name.
9. Click Finish to launch the import and see your initial diagram. You now have a database diagram that includes both tables and views.

Here's a DSV of Microsoft's AdventureWorks2008 OLTP database. This image shows an example of what one can do with SQL views in DSVs. In it, I replaced the Employee table with the vEmployee View (gave it a friendly name of 'Employee') that exists in the database, and added logical primary key and foreign key relationships that mirror the PK/FKs of the underlying physical table.

Note that all changes made in a DSV are logical and therefore isolated to the DSV file itself and do not impact the database directly.