Know relationships between all the tables of database in SQL Server

Question

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

User · Accepted Answer

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   Dump this into Excel  and you can slice and dice - based on the parent table  the referenced table or anything else   I find visual guides helpful - but sometimes  textual documentation is just as good  or even better  - just my 2 cents

User · Answer

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

User · Answer

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    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     -- gt      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   lt  gt  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   gt   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  gt  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  gt  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  lt  gt  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  lt  gt  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    -    lt  gt  0                     AND F level  gt  0                   ORDER BY J ID DESC               SELECT  Sql    Sql     JoinConditionSQL               IF LTRIM RTRIM  WhereClause    lt  gt                       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    lt  gt                       SELECT  Sql    Sql      WHERE      WhereClause            PRINT  SQL       END   SET NOCOUNT OFF    go

User · Answer

My solution is based on  marc s solution  i just concatenated columns in cases that a constraint is based on more than one column   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

User · Answer

Just another way to retrieve the same data using INFORMATION SCHEMA      The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION SCHEMA    sqlauthority way  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

User · Answer

Or you can look at schemacrawler

User · Answer

select   from information schema REFERENTIAL CONSTRAINTS where  UNIQUE CONSTRAINT SCHEMA    TABLE NAME     This will list the column with TABLE NAME and REFERENCED COLUMN NAME

[sql] Know relationships between all the tables of database in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to tsql