有没有一种方法可以在数据库图中直观地显示数据库视图? [英] Is there a way to visually display a database view in a database diagram?

问题描述

类似于

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

推荐答案

是的，尽管未使用SSMS的数据库图.

Yes, though not using SSMS's Database Diagrams.

为此，我的解决方案是Microsoft BI的数据源视图(DSV).这要求您安装了Microsoft的BIDS(商业智能开发工作室)，它是SQL Server Standard(或更高版本)的组件.

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.

从2005年到2016年的BIDS应该都可以工作并提供相同的基本功能.

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

或者，如果您想访问该工具集的免费版本，则可以下载并安装带有Advanced Services的SQL Server Express(基本上是添加了Reporting Services的SQL Express)，尽管我尚未使用此版本进行验证./p>

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. 安装BIDS后，启动(如果安装了更完整的版本，则启动Visual Studio)
2. 转到文件>新建项目
3. 在新建项目"对话框中的模板"下，扩展商业智能并选择一个项目.(我相信任何BI项目类型都可以.如果您没有看到任何BI项目，那么您最多可能没有安装BIDS，或者至少没有安装该版本的Visual Studio.
4. 为该项目提供一个可以在您的计算机上运行的位置.
5. 单击确定" 以创建Visual Studio和BI项目.
6. 在项目资源管理器"窗格中，右键单击数据源"文件夹，然后选择新建数据源" .按照数据源向导"创建与所需数据源的连接.在BIDS的2014版(及其他版本)中，存在一个有关您希望Analysis Services用于连接到数据源的安全凭据的问题.如果您只想创建仅用于绘制图表的DSV，则在此处选择什么都没关系.
7. 右键单击数据源视图"文件夹，然后选择新建数据源视图" .将启动数据源视图"向导. 使用向导，使用向左和向右箭头，将要在数据源视图中看到的表和视图添加到包含的对象"窗格中.
8. 单击下一步" 完成表/视图的导入并为您的DSV命名.
9. 单击完成以启动导入并查看您的初始图表. 现在，您有了一个包含表和视图的数据库图.

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.

这是Microsoft AdventureWorks2008 OLTP数据库的DSV .此图显示了一个示例，该示例说明了如何使用DSV中的SQL视图.在其中，我用数据库中存在的vEmployee视图(给它一个友好的名称"Employee")替换了Employee表，并添加了逻辑主键和外键关系，这些关系反映了基础物理表的PK/FK.

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.

请注意，在DSV中所做的所有更改都是合乎逻辑的，因此与DSV文件本身是隔离的，不会直接影响数据库.

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.

这篇关于有没有一种方法可以在数据库图中直观地显示数据库视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！