Getting all related "parent" tables MSSQL Server 2008 r2

Question

SQL

Good afternoon,

There is a table to which on FK other 3 tables refer. Some of them are referenced by 3 and 2 tables, respectively. You need to get a list of all tables that refer to the first table. That is, if we have the following structure:


The required result should be in the table of the form:
CREATE TABLE #tableName (tableName NVARCHAR(max))

The contents of the resulting table:
Table1
Table2
Table3
Table4
Table5
Table6
Table7
Table8
Table9

Thank you

Answer 1

The other day, in our dbForge , we just solved the same problem. I simplified the search a bit so that it fits the initial conditions. Hope this solves your problem:

DECLARE @tables TABLE ([object_id] INT PRIMARY KEY)
INSERT INTO @tables ([object_id])
SELECT OBJECT_ID('dbo.Table1', 'U')

DECLARE @rows INT = 1

WHILE @rows > 0 BEGIN
  
  SET @rows = 0

  INSERT INTO @tables ([object_id])
  SELECT fk.parent_object_id
  FROM @tables t
  JOIN sys.foreign_keys fk WITH(NOLOCK) ON fk.referenced_object_id = t.[object_id]
  WHERE NOT EXISTS(
      SELECT 1
      FROM @tables t2
      WHERE t2.[object_id] = fk.parent_object_id
    )

  SELECT @rows = @rows + @@ROWCOUNT

END

SELECT [object_name] = OBJECT_NAME([object_id])
FROM @tables

Answer 2

This script should help:

;WITH cte AS
(
  SELECT parent_object_id = OBJECT_ID('dbo.Table1', 'U')

  UNION ALL

  SELECT fk.parent_object_id
  FROM cte t
  JOIN sys.foreign_keys fk ON t.parent_object_id = fk.referenced_object_id
)
SELECT OBJECT_NAME(parent_object_id)
FROM cte

Answer 3

For the first case, everything works fine, thanks.
But if there is a reference to "itself" in the table, the algorithm goes into infinite recursion. That is, it is necessary to consider the case when it will be possible to bypass such a variant of relationships:

SQL Server Management Studio has a “View Dependencies” function - it correctly builds a dependency tree for tables. Perhaps there is some system procedure that does the same thing, only it does not take into account the triggers / procedures that use the table, but simply lists all related tables.
Thank you.