了解 SQL Server 中所有数据库表之间的关系 [英] Know relationships between all the tables of database in SQL Server

问题描述

我想知道我的数据库中的表是如何相互关联的(即 PK/FK/UK)，因此我在 SQL Server 中创建了我所有表的数据库图表.创建的图表不易阅读，必须滚动(水平滚动，有时垂直滚动)才能看到另一端的表格.

I wish to all know how the tables in my database are related to each other (i.e PK/FK/UK) and hence i created a database diagram of all my tables in SQL Server. The diagram that was created was not easily readable and had to scroll (horizontally and sometimes vertically) to see the table on the other end.

简而言之，当涉及到了解许多表之间的关系时，SQL 的数据库图不是 UI 友好的.

In short SQL's db diagram are not UI friendly when it comes to knowing relationships between many tables.

我的(简单)问题:有没有像数据库图这样的东西可以做数据库图所做的事情，但是好"的方式?

My (simple) Question: Is there something like database diagram which can do what db diagram did but in "good" way?

推荐答案

有时，文本表示也可能有所帮助；使用系统目录视图上的此查询，您可以获得所有 FK 关系的列表以及如何链接两个表(以及它们对哪些列进行操作).

Sometimes, a textual representation might also help; with this query on the system catalog views, you can get a list of all FK relationships and how the link two tables (and what columns they operate on).

SELECT
    fk.name 'FK Name',
    tp.name 'Parent table',
    cp.name, cp.column_id,
    tr.name 'Refrenced table',
    cr.name, cr.column_id
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
    tp.name, cp.column_id

将其转储到 Excel 中，您可以根据父表、引用表或其他任何内容进行切片和切块.

Dump this into Excel, and you can slice and dice - based on the parent table, the referenced table or anything else.

我发现视觉指南很有帮助 - 但有时，文本文档也一样好(甚至更好) - 只需我的 2 美分......

I find visual guides helpful - but sometimes, textual documentation is just as good (or even better) - just my 2 cents.....

这篇关于了解 SQL Server 中所有数据库表之间的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！