Получение всех связанных «родительских» таблиц MSSQL Server 2008 r2

Question

[YURY_PETRANKOV]

Добрый день,

Имеется таблица, на которую по FK ссылаются другие 3 таблицы. На некторые из них ссылается еще по 3 и 2 таблицы соотственно. Необходимо получить список всех таблиц, ссылающихся на первую таблицу. То есть, если у нас есть вот такая структура:


Необходимый результат должен содержать в таблице вида:
CREATE TABLE #tableName (tableName NVARCHAR(max))

Содержимое результирующей таблицы:
Table1
Table2
Table3
Table4
Table5
Table6
Table7
Table8
Table9

Спасибо

Comments

[YURY_PETRANKOV]

Код должен работать и для случая с ссылкой таблицы на «саму себя»

Answer 1

[Sergey Syrovatchenko]

На днях, в нашем dbForge мы как раз решали эту же проблему. Я немного упростил поиск, чтобы он подходил под начальные условия. Надеюсь, что это решит Вашу проблему:

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

[Sergey Syrovatchenko]

Этот скрипт должен помочь:

;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

Comments

[Sergey Syrovatchenko]

Проверял на этих данных:

CREATE TABLE dbo.Table1(
	a NCHAR(10) PRIMARY KEY
)
GO
CREATE TABLE dbo.Table2(
	a NCHAR(10) PRIMARY KEY,
	CONSTRAINT FK_Table2_Table1 FOREIGN KEY (a) REFERENCES dbo.Table1 (a)
)
GO
CREATE TABLE dbo.Table3(
	a NCHAR(10) PRIMARY KEY,
	CONSTRAINT FK_Table3_Table2 FOREIGN KEY (a) REFERENCES dbo.Table2 (a)
)
GO
CREATE TABLE dbo.Table4(
	a NCHAR(10) PRIMARY KEY,
	CONSTRAINT FK_Table4_Table2 FOREIGN KEY (a) REFERENCES dbo.Table2 (a)
)
GO
CREATE TABLE dbo.Table5(
	a NCHAR(10) PRIMARY KEY,
	CONSTRAINT FK_Table5_Table3 FOREIGN KEY (a) REFERENCES dbo.Table3 (a)
)
GO
CREATE TABLE dbo.Table6(
	a NCHAR(10) PRIMARY KEY,
	CONSTRAINT FK_Table6_Table3 FOREIGN KEY (a) REFERENCES dbo.Table3 (a)
)
GO

Answer 3

[YURY_PETRANKOV]

Для первого случая все прекрасно работает, спасибо.
Но при наличии в таблицы ссылки на «саму себя», алгоритм уходит в бесконечную рекурсию. То есть необходимо рассмотреть случай, когда будет возможен обход такого варианта связей:


В SQL Server Management Studio есть функция «View Dependencies» — она правильно строит дерево зависимстей для таблиц. Возможно, есть какая-то системная процедура, которая делает тоже самое, толкьо не учитывает тригеры\процедуры, использующие таблицу, а просто выводит список всех связанных таблиц.

Спасибо.

Comments

[Lazarev-MK]

Полагаю надо как то извернуться и бдить, что-бы идентификаторы foreign key не повторялись в результатирующем наборе.
В sys.foreign_keys это поле object_id.