Знать отношения между всеми таблицами базы данных в SQL Server

Я хочу, чтобы все знали, как таблицы в моей базе данных связаны друг с другом (например, PK/FK/UK), и поэтому я создал диаграмму базы данных всех моих таблиц в SQL Server. Созданная диаграмма не была легко читаема и должна была прокручиваться (горизонтально, а иногда и вертикально), чтобы увидеть таблицу на другом конце.

Короче говоря, диаграмма SQL db не является дружественной к пользовательскому интерфейсу, когда дело доходит до понимания отношений между многими таблицами.

Мой (простой) вопрос: Есть ли что-то вроде диаграммы базы данных, которая может делать то, что сделала db-диаграмма, но "хорошим" способом?

Спасибо!

Ответ 1

Иногда текстовое представление также может помочь; с этим запросом в представлениях системного каталога вы можете получить список всех отношений 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

Сбросьте это в Excel, и вы можете нарезать кубиками и нарезать их на основе родительской таблицы, таблицы с привязкой или всего остального.

Я нахожу визуальные руководства полезными - но иногда текстовая документация так же хороша (или даже лучше) - только мои 2 цента.....

Ответ 2

Или вы можете посмотреть schemacrawler

Ответ 3

Microsoft Visio, вероятно, лучший из всех, с которыми я столкнулся, хотя, насколько я знаю, он не будет автоматически генерироваться на основе ваших отношений.

EDIT: попробуйте это в Visio, можете дать вам то, что вам нужно http://office.microsoft.com/en-us/visio-help/reverse-engineering-an-existing-database-HA001182257.aspx

Ответ 4

Еще один способ получить одни и те же данные с помощью INFORMATION_SCHEMA

Представления информационной схемы, включенные в SQL Server, соответствуют стандартным определениям ISO для INFORMATION_SCHEMA.

способ 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

Эта хранимая процедура предоставит вам иерархическое дерево отношений. Основываясь на этой статье статьи от 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 [email protected] 
         AND soc.name [email protected] 
      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

Мое решение основано на решении @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'

Здесь будет указан столбец с TABLE_NAME и REFERENCED_COLUMN_NAME.