Сегодня я хотел бы пересмотреть сценарий, который я написал некоторое время назад. Этот сценарий был одним из моих любимых, и я постоянно думал о способах его улучшения. Вы можете найти оригинальный пост и сценарий здесь.

При пересмотре этого сценария я сделал некоторые упрощения и решил проблему, которая возникает в иерархиях с циклическими зависимостями. Работа с циклическими ссылками может быть довольно сложной, и я пробовал различные трюки, чтобы преодолеть эту проблему. Однако ни один из них не сработал в этом конкретном сценарии.

После некоторых экспериментов я нашел решение, которое включает использование Object_ID для более эффективной обработки циклических ссылок. В якорной части рекурсивного CTE я объединяю Object_ID с другими полями, чтобы создать уникальный идентификатор. Это помогает устранить циклические ссылки и позволяет сценарию работать по назначению.

Еще одно улучшение, которое я внес в сценарий, – это удаление ненужных соединений с sys.columns. Вместо этого я использую функцию COL_NAME() для получения имен столбцов родительского и дочернего объектов. Это изменение не только упрощает сценарий, но и обеспечивает небольшой прирост производительности.

Наконец, я решил переименовать CTE в сценарии, чтобы они были более понятными. Новые имена, “Hierarchy” и “Ancestry”, лучше отражают их назначение и делают сценарий более понятным.

Без лишних слов, вот пересмотренная версия сценария:

DECLARE @StartParentTable VARCHAR(256)
SELECT @StartParentTable = 'yourtable'

;WITH Hierarchy (ParentTable, ParentColumn, ChildTable, ChildColumn, FKLevel, SortCol) AS (
    SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
        ,COL_NAME(sfkc.referenced_object_id, sfkc.referenced_column_id) AS ParentColumn
        ,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
        ,COL_NAME(sfkc.parent_object_id, sfkc.parent_column_id) AS ChildColumn
        ,0 AS FKLevel
        ,CAST('.' + CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
    FROM sys.foreign_key_columns sfkc
    INNER JOIN sys.foreign_keys sfk ON sfkc.constraint_object_id = sfk.object_id
    WHERE OBJECT_NAME(sfk.referenced_object_id) = @StartParentTable

    UNION ALL

    SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
        ,COL_NAME(sfkc.referenced_object_id, sfkc.referenced_column_id) AS ParentColumn
        ,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
        ,COL_NAME(sfkc.parent_object_id, sfkc.parent_column_id) AS ChildColumn
        ,FKLevel + 1
        ,STUFF(('.' + F.SortCol + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))), 1, 1, '')
    FROM sys.foreign_keys sfk
    INNER JOIN Hierarchy F ON OBJECT_NAME(sfk.referenced_object_id) = f.ChildTable
        AND F.SortCol NOT LIKE '%' + CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '%'
    INNER JOIN sys.foreign_key_columns sfkc ON sfkc.constraint_object_id = sfk.object_id
    WHERE OBJECT_NAME(sfk.referenced_object_id) <> @StartParentTable
        AND sfk.referenced_object_id <> sfk.parent_object_id
), Ancestry (ParentTable, ParentColumn, ChildTable, ChildColumn, FKLevel, SortCol) AS (
    SELECT DISTINCT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
        ,COL_NAME(sfkc.referenced_object_id, sfkc.referenced_column_id) AS ParentColumn
        ,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
        ,COL_NAME(sfkc.parent_object_id, sfkc.parent_column_id) AS ChildColumn
        ,-1 AS FKLevel
        ,CAST('.' + CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
    FROM Hierarchy F
    INNER JOIN sys.foreign_keys sfk ON f.ChildTable = OBJECT_NAME(sfk.parent_object_id)
        AND F.ParentTable <> OBJECT_NAME(sfk.referenced_object_id)
    INNER JOIN sys.foreign_key_columns sfkc ON sfkc.constraint_object_id = sfk.object_id

    UNION ALL

    SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
        ,COL_NAME(sfkc.referenced_object_id, sfkc.referenced_column_id) AS ParentColumn
        ,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
        ,COL_NAME(sfkc.parent_object_id, sfkc.parent_column_id) AS ChildColumn
        ,f.FKLevel - 1
        ,STUFF(('.' + SortCol + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))), 1, 1, '')
    FROM Ancestry F
    INNER JOIN sys.foreign_keys sfk ON f.parentTable = OBJECT_NAME(sfk.parent_object_id)
    INNER JOIN sys.foreign_key_columns sfkc ON sfkc.constraint_object_id = sfk.object_id
    WHERE f.parentTable NOT IN (SELECT ParentTable FROM Hierarchy)
        AND sfk.referenced_object_id <> sfk.parent_object_id
        AND f.ChildTable NOT IN (SELECT ChildTable FROM Hierarchy)
)
SELECT ParentTable, ParentColumn, ChildTable, ChildColumn, FKLevel, SortCol
FROM Hierarchy

UNION ALL

SELECT ParentTable, ParentColumn, ChildTable, ChildColumn, FKLevel, SortCol
FROM Ancestry
ORDER BY SortCol ASC
OPTION (MAXRECURSION 500)

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

Спасибо за чтение!