T-SQL Challenge - Identifying Related Tables

The challenge at hand involves writing a recursive query that returns all tables related through foreign keys to a table whose name is given as input, directly or indirectly. For sample data use the following code:

USE testfk;

CREATE TABLE dbo.D(d INT PRIMARY KEY);

CREATE TABLE dbo.E(e INT PRIMARY KEY, d INT REFERENCES dbo.D);

CREATE TABLE dbo.A(a INT PRIMARY KEY, e INT REFERENCES dbo.E, aa INT REFERENCES dbo.A);

CREATE TABLE dbo.C(c INT PRIMARY KEY);

CREATE TABLE dbo.B(b INT PRIMARY KEY, c INT REFERENCES dbo.C);

CREATE TABLE dbo.AB(a INT REFERENCES dbo.A, b INT REFERENCES dbo.B, PRIMARY KEY(a, b));

CREATE TABLE dbo.G(g INT PRIMARY KEY);

CREATE TABLE dbo.F(f INT PRIMARY KEY, g INT REFERENCES dbo.G);

Here’s a graphical depiction of the relationships between the tables:

Given a table name as input, e.g.,

DECLARE @table AS NVARCHAR(261) = N'dbo.E';

Your challenge is to write a recursive query returning all related tables. You’re supposed to return the names of tables related to the input one directly or indirectly, including tables related through a connection table. So, for example, for the given input table name E, your code should return the following output:

obj_schema_name  obj_name

---------------- ---------

dbo              E

dbo              D

dbo              A

dbo              AB

dbo              B

dbo              C

Coming up with a solution that works is not that hard, for example using loops, like so:

DECLARE @T AS TABLE ( id INT NOT NULL PRIMARY KEY );

INSERT INTO @T(id)

  SELECT referenced_object_id AS id

  FROM sys.foreign_keys

  WHERE parent_object_id = OBJECT_ID(@table)

  UNION

  SELECT parent_object_id

  FROM sys.foreign_keys

  WHERE referenced_object_id = OBJECT_ID(@table);

WHILE @@ROWCOUNT > 0

  INSERT INTO @T(id)

    SELECT referenced_object_id AS id

    FROM sys.foreign_keys AS FK

      JOIN @T

        ON FK.parent_object_id = [@T].id

    UNION

    SELECT parent_object_id

    FROM sys.foreign_keys AS FK

      JOIN @T

        ON FK.referenced_object_id = [@T].id

    EXCEPT

    SELECT id FROM @T;

SELECT OBJECT_SCHEMA_NAME(id) AS obj_schema_name, OBJECT_NAME(id) AS obj_name

FROM @T;

However, coming up with a solution using a recursive query is not that trivial due to all kinds of restrictions that SQL Server imposes on recursive queries. For example, among other restrictions, you’re not allowed to refer to the CTE name more than once from the recursive query. So with this constraint in place—using a recursive query—can you find a solution?

Please post your solutions as comments to this blog entry or if you prefer directly to me.

Good luck!

BG