了解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.

我希望大家都知道我的数据库中的表是如何相互关联的(I)。因此我在SQL Server中创建了所有表的数据库图。创建的图表不易阅读，必须滚动(水平或有时垂直)才能看到另一端的表。

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

简而言之，当涉及到了解许多表之间的关系时，SQL的db关系图对UI不友好。

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

我的(简单的)问题是:是否有数据库图这样的东西可以做db图所做的事情，但以“良好”的方式?

Thanks!

谢谢!

7 个解决方案

#1

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).

有时，文本表示也可能有所帮助;通过对system catalog视图的这个查询，您可以获得所有FK关系的列表以及如何链接两个表(以及它们操作的列)。

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

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

将它转储到Excel中，您可以基于父表、引用表或其他任何东西对其进行切片和掷骰子。

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

我觉得视觉指南很有帮助——但有时候，文字文档也很好(甚至更好)——只是我的2分……

#2

Or you can look at schemacrawler

或者你可以看看schemacrawler

#3

Microsoft Visio is probably the best I've came across, although as far as I know it won't automatically generate based on your relationships.

微软Visio可能是我见过的最好的，尽管据我所知，它不会根据你的人际关系自动生成。

EDIT: try this in Visio, could give you what you need http://office.microsoft.com/en-us/visio-help/reverse-engineering-an-existing-database-HA001182257.aspx

编辑:在Visio中试试这个，可以得到您需要的http://office.microsoft.com/en-us/visio-help/reverse engineering-an existing-database- ha001182257.aspx

#4

Just another way to retrieve the same data using INFORMATION_SCHEMA

使用INFORMATION_SCHEMA检索相同数据的另一种方法

The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

SQL Server中包含的信息模式视图符合INFORMATION_SCHEMA的ISO标准定义。

sqlauthority way

sqlauthority方式

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')

#5

This stored procedure will provide you with a hierarchical tree of relationship. Based on this article from Technet. It will also optionally provide you a query for reading or deleting all the related data.

这个存储过程将为您提供一种层次关系树。基于Technet的这篇文章。它还可以为您提供一个查询，用于读取或删除所有相关数据。

IF OBJECT_ID('GetForeignKeyRelations','P') IS NOT NULL 
    DROP PROC GetForeignKeyRelations 
GO 

CREATE PROC GetForeignKeyRelations 
@Schemaname Sysname = 'dbo' 
,@Tablename Sysname 
,@WhereClause NVARCHAR(2000) = '' 
,@GenerateDeleteScripts bit  = 0  
,@GenerateSelectScripts bit  = 0 

AS 

SET NOCOUNT ON 

DECLARE @fkeytbl TABLE 
( 
ReferencingObjectid        int NULL 
,ReferencingSchemaname  Sysname NULL 
,ReferencingTablename   Sysname NULL  
,ReferencingColumnname  Sysname NULL 
,PrimarykeyObjectid     int  NULL 
,PrimarykeySchemaname   Sysname NULL 
,PrimarykeyTablename    Sysname NULL 
,PrimarykeyColumnname   Sysname NULL 
,Hierarchy              varchar(max) NULL 
,level                  int NULL 
,rnk                    varchar(max) NULL 
,Processed                bit default 0  NULL 
); 



 WITH fkey (ReferencingObjectid,ReferencingSchemaname,ReferencingTablename,ReferencingColumnname 
            ,PrimarykeyObjectid,PrimarykeySchemaname,PrimarykeyTablename,PrimarykeyColumnname,Hierarchy,level,rnk) 
    AS 
    ( 
        SELECT                   
                               soc.object_id 
                              ,scc.name 
                              ,soc.name 
                              ,convert(sysname,null) 
                              ,convert(int,null) 
                              ,convert(sysname,null) 
                              ,convert(sysname,null) 
                              ,convert(sysname,null) 
                              ,CONVERT(VARCHAR(MAX), scc.name + '.' + soc.name  ) as Hierarchy 
                              ,0 as level 
                              ,rnk=convert(varchar(max),soc.object_id) 
        FROM SYS.objects soc 
        JOIN sys.schemas scc 
          ON soc.schema_id = scc.schema_id 
       WHERE scc.name =@Schemaname 
         AND soc.name =@Tablename 
      UNION ALL 
      SELECT                   sop.object_id 
                              ,scp.name 
                              ,sop.name 
                              ,socp.name 
                              ,soc.object_id 
                              ,scc.name 
                              ,soc.name 
                              ,socc.name 
                              ,CONVERT(VARCHAR(MAX), f.Hierarchy + ' --> ' + scp.name + '.' + sop.name ) as Hierarchy 
                              ,f.level+1 as level 
                              ,rnk=f.rnk + '-' + convert(varchar(max),sop.object_id) 
        FROM SYS.foreign_key_columns sfc 
        JOIN Sys.Objects sop 
          ON sfc.parent_object_id = sop.object_id 
        JOIN SYS.columns socp 
          ON socp.object_id = sop.object_id 
         AND socp.column_id = sfc.parent_column_id 
        JOIN sys.schemas scp 
          ON sop.schema_id = scp.schema_id 
        JOIN SYS.objects soc 
          ON sfc.referenced_object_id = soc.object_id 
        JOIN SYS.columns socc 
          ON socc.object_id = soc.object_id 
         AND socc.column_id = sfc.referenced_column_id 
        JOIN sys.schemas scc 
          ON soc.schema_id = scc.schema_id 
        JOIN fkey f 
          ON f.ReferencingObjectid = sfc.referenced_object_id 
        WHERE ISNULL(f.PrimarykeyObjectid,0) <> f.ReferencingObjectid 
      ) 

     INSERT INTO @fkeytbl 
     (ReferencingObjectid,ReferencingSchemaname,ReferencingTablename,ReferencingColumnname 
            ,PrimarykeyObjectid,PrimarykeySchemaname,PrimarykeyTablename,PrimarykeyColumnname,Hierarchy,level,rnk) 
     SELECT ReferencingObjectid,ReferencingSchemaname,ReferencingTablename,ReferencingColumnname 
            ,PrimarykeyObjectid,PrimarykeySchemaname,PrimarykeyTablename,PrimarykeyColumnname,Hierarchy,level,rnk 
       FROM fkey 

        SELECT F.Relationshiptree 
         FROM 
        ( 
        SELECT DISTINCT Replicate('------',Level) + CASE LEVEL WHEN 0 THEN '' ELSE '>' END +  ReferencingSchemaname + '.' + ReferencingTablename 'Relationshiptree' 
               ,RNK 
          FROM @fkeytbl 
          ) F 
        ORDER BY F.rnk ASC 

------------------------------------------------------------------------------------------------------------------------------- 
-- Generate the Delete / Select script 
------------------------------------------------------------------------------------------------------------------------------- 

    DECLARE @Sql VARCHAR(MAX) 
    DECLARE @RnkSql VARCHAR(MAX) 

    DECLARE @Jointables TABLE 
    ( 
    ID INT IDENTITY 
    ,Object_id int 
    ) 

    DECLARE @ProcessTablename SYSNAME 
    DECLARE @ProcessSchemaName SYSNAME 

    DECLARE @JoinConditionSQL VARCHAR(MAX) 
    DECLARE @Rnk VARCHAR(MAX) 
    DECLARE @OldTablename SYSNAME 

    IF @GenerateDeleteScripts = 1 or @GenerateSelectScripts = 1  
    BEGIN 

          WHILE EXISTS ( SELECT 1 
                           FROM @fkeytbl 
                          WHERE Processed = 0 
                            AND level > 0 ) 
          BEGIN 

            SELECT @ProcessTablename = '' 
            SELECT @Sql                 = '' 
            SELECT @JoinConditionSQL = '' 
            SELECT @OldTablename     = '' 


            SELECT TOP 1 @ProcessTablename = ReferencingTablename 
                  ,@ProcessSchemaName  = ReferencingSchemaname 
                  ,@Rnk = RNK  
              FROM @fkeytbl 
             WHERE Processed = 0 
              AND level > 0  
             ORDER BY level DESC 


            SELECT @RnkSql ='SELECT ' + REPLACE (@rnk,'-',' UNION ALL SELECT ')  

            DELETE FROM @Jointables 

            INSERT INTO @Jointables 
            EXEC(@RnkSql) 

            IF @GenerateDeleteScripts = 1 
                SELECT @Sql = 'DELETE [' + @ProcessSchemaName + '].[' + @ProcessTablename + ']' + CHAR(10) + ' FROM [' + @ProcessSchemaName + '].[' + @ProcessTablename + ']' + CHAR(10) 

            IF @GenerateSelectScripts = 1 
                SELECT @Sql = 'SELECT  [' + @ProcessSchemaName + '].[' + @ProcessTablename + '].*' + CHAR(10) + ' FROM [' + @ProcessSchemaName + '].[' + @ProcessTablename + ']' + CHAR(10) 

            SELECT @JoinConditionSQL = @JoinConditionSQL  
                                           + CASE  
                                             WHEN @OldTablename <> f.PrimarykeyTablename THEN  'JOIN ['  + f.PrimarykeySchemaname  + '].[' + f.PrimarykeyTablename + '] ' + CHAR(10) + ' ON ' 
                                             ELSE ' AND '  
                                             END 
                                           + ' ['  + f.PrimarykeySchemaname  + '].[' + f.PrimarykeyTablename + '].[' + f.PrimarykeyColumnname + '] =  ['  + f.ReferencingSchemaname  + '].[' + f.ReferencingTablename + '].[' + f.ReferencingColumnname + ']' + CHAR(10)  
                     , @OldTablename = CASE  
                                         WHEN @OldTablename <> f.PrimarykeyTablename THEN  f.PrimarykeyTablename 
                                         ELSE @OldTablename 
                                         END 

                  FROM @fkeytbl f 
                  JOIN @Jointables j 
                    ON f.Referencingobjectid  = j.Object_id 
                 WHERE charindex(f.rnk + '-',@Rnk + '-') <> 0 
                   AND F.level > 0 
                 ORDER BY J.ID DESC 

            SELECT @Sql = @Sql +  @JoinConditionSQL 

            IF LTRIM(RTRIM(@WhereClause)) <> ''  
                SELECT @Sql = @Sql + ' WHERE (' + @WhereClause + ')' 

            PRINT @SQL 
            PRINT CHAR(10) 

            UPDATE @fkeytbl 
               SET Processed = 1 
             WHERE ReferencingTablename = @ProcessTablename 
               AND rnk = @Rnk 

          END 

          IF @GenerateDeleteScripts = 1 
            SELECT @Sql = 'DELETE FROM [' + @Schemaname + '].[' + @Tablename + ']' 

          IF @GenerateSelectScripts = 1 
            SELECT @Sql = 'SELECT * FROM [' + @Schemaname + '].[' + @Tablename + ']' 

          IF LTRIM(RTRIM(@WhereClause)) <> ''  
                SELECT @Sql = @Sql  + ' WHERE ' + @WhereClause 

         PRINT @SQL 
     END 

SET NOCOUNT OFF 


go 

#6

My solution is based on @marc_s solution, i just concatenated columns in cases that a constraint is based on more than one column:

我的解决方案基于@marc_s解决方案，我只是在约束基于多个列的情况下连接列:

SELECT
   FK.[name] AS ForeignKeyConstraintName
  ,SCHEMA_NAME(FT.schema_id) + '.' + FT.[name] AS ForeignTable
  ,STUFF(ForeignColumns.ForeignColumns, 1, 2, '') AS ForeignColumns
  ,SCHEMA_NAME(RT.schema_id) + '.' + RT.[name] AS ReferencedTable
  ,STUFF(ReferencedColumns.ReferencedColumns, 1, 2, '') AS ReferencedColumns
FROM
  sys.foreign_keys FK
  INNER JOIN sys.tables FT
  ON FT.object_id = FK.parent_object_id
  INNER JOIN sys.tables RT
  ON RT.object_id = FK.referenced_object_id
  CROSS APPLY
  (
    SELECT
      ', ' + iFC.[name] AS [text()]
    FROM
      sys.foreign_key_columns iFKC
      INNER JOIN sys.columns iFC
      ON iFC.object_id = iFKC.parent_object_id
        AND iFC.column_id = iFKC.parent_column_id
    WHERE
      iFKC.constraint_object_id = FK.object_id
    ORDER BY
      iFC.[name]
    FOR XML PATH('')
  ) ForeignColumns (ForeignColumns)
  CROSS APPLY
  (
    SELECT
      ', ' + iRC.[name]AS [text()]
    FROM
      sys.foreign_key_columns iFKC
      INNER JOIN sys.columns iRC
      ON iRC.object_id = iFKC.referenced_object_id
        AND iRC.column_id = iFKC.referenced_column_id
    WHERE
      iFKC.constraint_object_id = FK.object_id
    ORDER BY
      iRC.[name]
    FOR XML PATH('')
  ) ReferencedColumns (ReferencedColumns)

#7

select * from information_schema.REFERENTIAL_CONSTRAINTS where 
UNIQUE_CONSTRAINT_SCHEMA = 'TABLE_NAME' 

This will list the column with TABLE_NAME and REFERENCED_COLUMN_NAME.

这将列出具有TABLE_NAME和REFERENCED_COLUMN_NAME的列。