Is there quick a way to find what tables are between two or more "unrelated" tables in SQL-CodePudding

I have a large database with 100 tables and I need to run reports between varying tables that have no direct relationship with each other, but have relationships with other tables leading to each other.

For example, if I need to see what Pay Codes an employee is using, tblEmployee does not directly relate to tblPayCode, so I have to also bring in tblEmployeeCode to create the report (see image 1).

This is a simple relationship, but some are more complex and I don't always have the time to look through the PKs or FKs to find what tables I need between two or more tables.

I noticed in SQL Server Management Studio, when you create a database diagram, you can bring in all relationships one table has, but 1) it pull in all relationships for the one table only, and 2) it wouldn't work with more complex relationships where I have to bring in two or more tables between two or more tables (see image 2 for an example; the red outlined tables are the tables I needed to find a relationship with each other).

I have tried creating a diagram with all tables in the database, but with so many tables, it's difficult to navigate and it would be faster for me to use the PK and FK names to find a way to connect the tables. So is there a way I can easily see the "path" of tables between two or more tables quickly?

Image 1: https://i.stack.imgur.com/xuNKi.png

Image 2: https://i.stack.imgur.com/yv3Je.png

EDIT: I would also be fine with a SQL script

CodePudding user response：

You should be able to use a recursive CTE for this.

e.g. As below

WITH R
     AS (
        --Start with root objects that don't reference any others
        SELECT referenced_object_id,
               referenced_path = CAST(QUOTENAME(OBJECT_SCHEMA_NAME(referenced_object_id))   '.'   QUOTENAME(OBJECT_NAME(referenced_object_id)) AS NVARCHAR(4000))
        FROM   sys.foreign_keys
        WHERE  referenced_object_id NOT IN (SELECT fk2.parent_object_id FROM sys.foreign_keys fk2)
        GROUP  BY referenced_object_id

         UNION ALL

         SELECT parent_object_id,
                referenced_path = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))   '.'   QUOTENAME(OBJECT_NAME(parent_object_id))   ' -> '   r.referenced_path
         FROM   sys.foreign_keys fk
                JOIN R
                  ON R.referenced_object_id = fk.referenced_object_id
         --If the path already contains this object stop the path to avoid infinite cycles
         WHERE  0 = CHARINDEX(QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))   '.'   QUOTENAME(OBJECT_NAME(parent_object_id)), r.referenced_path))
SELECT *
FROM   R
--Uncomment below to only return paths containing both objects of interest
/*
WHERE  0 NOT IN ( CHARINDEX('[dbo].[tblEmployee]', referenced_path), CHARINDEX('[dbo].[tblHRPosition]', referenced_path) ) 
*/