如何生成非常大的数据库模式的图表 (SQL Server)
我有一个非常大的数据库，需要绘制图表。数据库是 x64 上的 SQL Server 2008。它很大，因为有数百个相关表，每个表最多有 2000 个字段（有些是稀疏的），表之间有多种关系（实际上每个表通常有数百个），多个模式......你明白了。
我尝试使用 SQL Server Management Studio 的数据库图表功能，但它因 Win32Exception 崩溃：“没有足够的存储空间来处理此命令...”
我尝试在另一台计算机上使用 Visio 的逆向工程功能进行连接并绘制图表，但这已经持续了几个小时，还没有完成的迹象。
构建这个巨大模式的脚本是由我们为这项工作构建的工具编写的。虽然该工具可以很好地完成其工作，但将其输出可视化却很困难。
我正在寻找一个工具来踢出这个数据库的图表，这样我们就可以做到这一点。有什么建议吗？
编辑： 只是强调一下，该图确实不应该用于实际有用的参考。它是一个客户关系管理设备，用于展示系统的复杂性/规模。
I have a very large database I need to diagram. The database is SQL Server 2008 on x64. It is large in that there are hundreds of related tables, each with up to 2000 fields (some are sparse), multiple relationships between tables (often hundreds per table, in fact), multiple schemas... you get the idea.
I tried to use the Database Diagrams feature of SQL Server Management Studio, but it crashed with a Win32Exception: "Not enough storage is available to process this command..."
I tried to use Visio's reverse engineering feature on a different machine to connect in and diagram it, but that's been going for a few hours with no sign of completion.
The scripts to build this giant schema are being by a tool we built for the job. While the tool is doing its job just fine, it's tricky to visualise its output.
I'm after a tool to kick out a diagram of this database so we can do this. Any suggestions?
EDIT:
Just to emphasize, the diagram is indeed not supposed to be used for actual useful reference. It's a client relationship management device to demonstrate the complexity/scale of the system.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
我工作的地方有几百张桌子（近 1000 张），没有人真正知道系统中发生了什么，公司正在成长并雇用了很多人。一个人的任务是绘制图表，他自动神奇地创建了一张巨大的平铺海报，其中包含每张桌子，并用线连接各个桌子（遍布整个地方）。我不确定他用的是什么，几年前是 Unix 和 Oracle（早于 Linux 和开源）。他的图表中的表格布局没有真正的韵律或理由。他成功地创建了每张桌子的图表。这张“海报”被贴在公共区域的墙上，看了几眼，但没有人真正使用过它，它无法使用，太杂乱，太杂乱。因此，我使用 MS-Word 创建了一个包含 20 个主表的单页图表（当我“发现”新的主表时，它经历了几次迭代），每个外键都有行，每个表都以逻辑方式定位。我显示了列名称、数据类型、可空性、PK 和所有 FK。我把图表挂在墙上显示器旁边。最终每个人都想要我的图表的副本，包括制作“海报”的人。当我离开那份工作时，他们仍在向新员工提供我的图表。
我建议您像探索者一样工作，找到关键表并在您探索时绘制它们，在发现系统时根据需要制作尽可能多的特定图表。试图自动制作一张巨大的“海报”效果并不好。
I worked at a place that had several hundred tables (near 1k) and no one really knew what was going on in the system, company was growing and hiring a lot. A guy was tasked with doing a diagram, and he auto-magically created a gigantic tiled poster that contained every table with lines connecting various tables (going all over the place). I'm not sure what he used, it was Unix and Oracle years ago (way before Linux and open source). There was no real rhyme or reason to the layout of the the tables in his diagram. He had successfully created a diagram of every table. The "poster" was put on a wall in a common area, and got a few looks, but no one ever really used it, it was unusable, too cluttered, too unorganized. As a result, I used MS-Word to create a single page diagram containing the 20 main tables (it went through a few iterations as I "discovered" new main tables) with lines for each foreign key and each table located in a logical manner. I showed the column name, data type, nullability, PK, and all FKs. I put my diagram up on my wall by my monitor. Eventually everyone wanted a copy of my diagram, including the person that made the "poster". When I left that job they were still giving my diagram to new hires.
I recommend that you work like an explorer, find the key tables and map them as you go, making as many specific diagrams as necessary as you discover the system. Trying to make a gigantic "poster" automatically will not work very well.
为这种大小的数据库生成任何类型的图像都只会成为粘在墙上的养眼之物，令人惊叹不已，老实说，除了偶尔扫一眼之外，没有任何真正的目的。为什么不使用像Red Gate 的文档工具这样能够满足实际目的的工具呢？请理解，我并不是以嘲笑的方式这么说，但在尝试绘制一个巨大的数据库之前，我已经沿着这条路走了下去，并且我在某种程度上取得了成功，但从未找到一个有用的好出口。
Generating an image of any kind for a database of that size simply becomes eye candy that is stuck on a wall that draw's gasps, and honestly serves no real purpose except occasional glances. Why not use a tool like Red Gate's Documentation tool that will serve an actual purpose? Please understand I'm not saying this in a mocking way, but I've been down this road before trying to diagram a huge database, and I succeeded to some degree, but never found a good outlet where it was of some use.
使用图形可视化。使用一些 SQL 语句生成二元图，然后通过 dot.exe 运行它以生成 PDF 或 PNG。
我用它在 SQL Server 表中生成数据图。没有理由你也可以将它用于表格。
http://www.graphviz.org/
还有 java、silverlight 和 AJAX 实用程序用于导航额外的内容大图，因为 PDF 仅一页。
Use graphviz. Use some SQL statements to generate the digram, then run it through dot.exe to generate a PDF or PNG.
I've used it to generate digrams of data within SQL Server tables. No reason why you can use it for tables too.
http://www.graphviz.org/
There are also java, silverlight, and AJAX utilities for navigating extra large graphs, as PDF is only for one page.
由于您有多个模式，也许一个好主意是为每个模式生成图表
Since you have multiple schemas maybe a good idea is to generate diagrams per schema instead
我会避免在一张图表中完成所有事情。正如您所提到的，这些工具会崩溃，并且可能无法轻松理解包含数百个表（每个表可能有数千条记录）的图表。您能否生成较小逻辑区域的图表，这些逻辑区域与其他区域有一些重叠
逻辑区域？
或者，您可以尝试使用 graphviz 之类的工具来解析 DDL 语句，然后生成图表。它可能会搅动一段时间，但我记得在大学海报大小的图表中看到过小字体，它们的复杂性可能与您的相同。祝你好运！
I'd avoid doing the whole thing in a single diagram. As you mentioned, the tools crash, and it's probably not possible to easily comprehend a diagram with hundreds of tables with potentially thousands of records per table. Can you generate diagrams of smaller logical areas with some overlap to other
logical areas?
Alternately, you could try using something like graphviz to parse the DDL statements and then produce a graph. It will probably churn for a while, but I remember seeing in a university poster-sized diagrams with tiny print, that were probably of the same complexity as yours. Good luck!
FWIW，假设您确实想继续这样做，我个人发现 Visual Studio 2010 数据库建模器制作了迄今为止我遇到的最好的图表 - 只需导入您的数据库，就好像您要将其用于 Linq2SQL 一样
FWIW, assuming you do want to go ahead with this I've personally found that the visual studio 2010 database modeller does the nicest diagrams I've come across so far - Just import your database as if you were going to use it for Linq2SQL
schemaspy
提供了一个方便的界面来使用 graphviz 作为后端生成跨越多个模式的交互式图表。不过我从来没有在这么大的东西上尝试过。
schemaspy
provides a handy interface to generate interactive diagrams that span multiple schemas using graphviz as a backend. I've never tried it on anything this size though.
IntelliJ（特别是刚刚尝试过的 IDEA，但我相信他们的其他 IDE 提供了此功能 https://www.jetbrains。 com/）有一个内置的数据库客户端工具，从这里您可以连接到数据库并通过突出显示所需的表、“右键单击”并选择“来分析单个表、特定组合或表或所有表”图表'选项。您可以保存以供以后参考，也可以打印。我刚刚在包含 500 多个表的大型数据库上尝试过此操作，它在几秒钟内呈现出来，矢量图可以作为直观地消化数据库结构以及某些表之间的关系和约束的替代方法，但不建议打印。
IntelliJ (specifically IDEA as just tried with this, but I believe their other IDEs offer this feature https://www.jetbrains.com/) has a built in database client facility, from here you can connect to your database and analyse individual tables, specific combination or table or all your tables by highlighting the desired tables, 'right clicking' and selecting the 'diagram' option. You can save for later reference and also print. I have just tried this on a large DB of 500+ tables and it rendered in seconds, the vector diagram serves as an alternative way to digest database structures visually and the relationships and constraints between certain tables but not recommended for printing.
SchemaSpy 是一个很棒的工具，而且很流行，但需要一些努力来安装和设置：
如何设置：
以下软件应用程序需要下载并保存在文件夹中：
• 从 Microsoft 下载 Java jdk：https://learn.microsoft.com/en -gb/java/openjdk/download
（例如：microsoft-jdk-17.0.8.1-windows-aarch64.msi）
双击并安装 msi 文件。
• 下载SchemaSpy JAR 文件：https://github.com/schemaspy/schemaspy/releases< br>
（例如：schemaspy-6.2.4.jar）
• 下载 SQL Server 的 JDBC 驱动程序：https://www.microsoft.com/en-US/download/details.aspx?id=58505
解压缩文件并将其保存在同一文件夹中。该文件夹（例如\sqljdbc_7.4\enu）可能包含不同版本的运行时。将最新的（例如：mssql-jdbc-7.4.1.jre8）复制到根文件夹。
• 下载Graphviz（如果您需要生成png图表）：https://graphviz.org/download/
准备 Windows 批处理文件和 SchemaSpy 属性文件：
• 创建一个具有名称的批处理文件（例如runschemaspy.bat）：添加以下代码并将其另存为同一文件夹中的.bat 文件
• 创建一个属性文件（例如：schemaspy.property）：添加以下代码并将其另存为同一文件夹中的 .property 文件
如何运行应用程序以生成 SchemaSpy 输出
• 使用适当的数据库服务器和数据库名称更改属性文件。默认情况下，输出文件夹将为根文件夹中的“/output”。通过更改参数，可以根据需要将图像更改为 png 或 svg (schemaspy.imageformat=svg)
• 双击运行批处理文件。这将要求输入 SQL 登录的密码。输入密码后，它将在/output文件夹中生成输出。
（sql数据库需要windows登录。）
SchemaSpy is a great tool for this and popular, but needed some efforts to install and setup:
How to setup:
The following software applications need to download and save on a folder:
• Download Java jdk from Microsoft: https://learn.microsoft.com/en-gb/java/openjdk/download
(e.g: microsoft-jdk-17.0.8.1-windows-aarch64.msi)
Double click and install the msi file.
• Download SchemaSpy JAR file: https://github.com/schemaspy/schemaspy/releases
(e.g: schemaspy-6.2.4.jar)
• Download the JDBC driver for SQL Server: https://www.microsoft.com/en-US/download/details.aspx?id=58505
Unzip the file and save it on same folder. The folder (e.g \sqljdbc_7.4\enu) may contain different versions of runtime. Copy the latest one (e.g: mssql-jdbc-7.4.1.jre8) to the root folder.
• Download Graphviz (if you need to generate png diagram) : https://graphviz.org/download/
Prepare windows batch file and SchemaSpy Property file:
• Create a batch file with a name (e.g runschemaspy.bat): add the code below and save it as .bat file on the same folder
• Create a property file (eg: schemaspy.property): add the following and save it as .property file on the same folder
How to run the application to generate SchemaSpy outputs
• Change the property file with appropriate database server and database name. By default, the output folder will be “/output” on root folder. The image can be changed to png or svg as required by changing a parameter (schemaspy.imageformat=svg)
• Double click to run the batch file. This will ask for a password of the SQL Login. Once you entered the password, it will generate output in/output folder.
(The sql database needed a windows login. )