How to find relations between different tables



  • I am working with AdventureWorks2019 Full database in SSMS and the problems I am facing are as follows:

    1. I cannot find any any apparent connection between different tables of different schemas. For example - I cannot find how to connect Customer table of Sales schema and Address table of Person schema.

    2. I cannot understand which columns are linked with one another. As there are so many tables, it is confusing to understand and know which columns are connected to one another (for joining).

    Please tell me how to resolve these problems. Happy to provide extra details.



  • You can use this query to find a list of foreign keys:

    ;WITH ColumnCount
    AS (
        SELECT s.name AS SchemaName
            ,t.name AS TableName
            ,c.name AS ColumnName
            ,ct.[name] AS DataType
            ,c.max_length
            ,c.precision
            ,c.scale
            ,COUNT(c.column_id) OVER (
                PARTITION BY c.[name]
                ,ct.[name]
                ,c.max_length
                ,c.precision
                ,c.scale
                ) AS Duplicates
        FROM sys.schemas s
        JOIN sys.tables t ON s.schema_id = t.schema_id
        JOIN sys.columns c ON t.object_id = c.object_id
        JOIN sys.types ct ON c.user_type_id = ct.user_type_id
        )
    SELECT cc.TableName + '.' + cc.ColumnName AS ForeignTableName
        ,cd.TableName + '.' + cd.ColumnName AS PrimaryTableName
        ,cd.TableName AS TargetTable
        ,cd.ColumnName AS TargetColumn
        ,1 Relationship --,cc.ColumnName, cc.DataType, cc.Duplicates 
    FROM ColumnCount CC
    

    /*pk only joins - take ths out if you want all joins between fields, not just pk to fk */
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col ON col.COLUMN_NAME = cc.ColumnName
    AND col.TABLE_NAME = cc.TableName
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab ON Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY'
    /*end of pk only joins */

    LEFT JOIN ColumnCount Cd ON cc.ColumnName = cd.ColumnName
    AND cc.DataType = cd.DataType
    AND cc.TableName != cd.TableName
    WHERE CC.Duplicates > 1
    ORDER BY CC.ColumnName
    ,CC.SchemaName
    ,CC.TableName;

    I wrote an article on my blog about https://www.jeeja.biz/2021/11/10/how-to-blind-reverse-engineer-sql-server-database-to-erd/ .




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2