遍历数据库中的所有外键并生成路径 [英] Traverse through all foreign keys in database and generate a path

问题描述

我写了一个查询来找出主键-数据库中所有表之间的外键关系。仅当物理上存在外键时，此方法才有效。

I wrote a query to find out primary key - foreign key relationship between all the tables in a database. This works only if we have foreign keys present physically.

请运行此查询以清楚地了解我的问题。

WITH cte
AS
(
    SELECT 
        fk.create_date
        , fk.modify_date
        , fkc.constraint_object_id AS ConstraintId
        , OBJECT_NAME(fkc.constraint_object_id) AS ConstraintName
        --, fkc.referenced_object_id AS PrimaryKeyTableId
        , OBJECT_NAME(fkc.referenced_object_id) AS PrimaryKeyTableName
        --, fkc.referenced_column_id AS PrimaryKeyColumnId
        , rc.name AS PrimaryKeyColumnName
        --, fk.parent_object_id AS ForeignKeyTableId
        , OBJECT_NAME(fk.parent_object_id) AS ForeignKeyTableName
        --, fkc.parent_column_id AS ForeignKeyColumnId
        , lc.name AS ForeignKeyColumnName
    FROM sys.foreign_key_columns fkc
    INNER JOIN sys.columns rc 
        ON  rc.OBJECT_ID = fkc.referenced_object_id 
        AND fkc.referenced_column_id = rc.column_id
    INNER JOIN sys.foreign_keys fk 
        ON  fk.OBJECT_ID = fkc.constraint_object_id
    INNER JOIN sys.columns lc 
        ON  lc.OBJECT_ID = fk.parent_object_id
        AND fkc.parent_column_id = lc.column_id
)
, cte2(create_date, modify_date, ConstraintName
        , PrimaryKeyTableName, PrimaryKeyColumnName
        , ForeignKeyTableName, ForeignKeyColumnName
        , Hops, path ) AS 
    (
        SELECT
            create_date, modify_date, ConstraintName
            , PrimaryKeyTableName, PrimaryKeyColumnName
            , ForeignKeyTableName, ForeignKeyColumnName 
            , 1 , CAST(QUOTENAME(PrimaryKeyTableName + '.' + PrimaryKeyColumnName) AS VARCHAR(4000))
        FROM cte
    UNION ALL
        SELECT 
            cte.create_date, cte.modify_date, cte.ConstraintName
            , cte.PrimaryKeyTableName, cte.PrimaryKeyColumnName
            , cte.ForeignKeyTableName, cte.ForeignKeyColumnName
            , cte2.Hops +1, CAST(cte2.path + '-> ' +QUOTENAME(cte.PrimaryKeyTableName+ '.' + cte.PrimaryKeyColumnName) AS VARCHAR(4000))
        FROM cte2 INNER JOIN cte ON cte2.ForeignKeyTableName = cte.PrimaryKeyTableName
        AND cte2.ForeignKeyColumnName != cte.PrimaryKeyColumnName
    )
SELECT 
ConstraintName
        , PrimaryKeyTableName, PrimaryKeyColumnName
        , ForeignKeyTableName, ForeignKeyColumnName
        , Hops, path + '-> ' + QUOTENAME(ForeignKeyTableName + '.' + ForeignKeyColumnName) AS Path
FROM cte2

除非我们在数据库中存在复合主键，否则查询工作会顺利进行。

Above query works smoothly unless we have composite primary key present in database.

比方说，我有一个表

1. Method（MethodId，....）


2. Parameter（ParameterId，...）


3. ParameterMethodMap（ParameterId，MethodId） ）-复合主键


4. Test（TestId，ParameterId，MethodId ....）-复合主键用作外键


5. Sample（SampleId，TestId ....）

1. Method(MethodId, ....)
2. Parameter(ParameterId, ...)
3. ParameterMethodMap(ParameterId, MethodId) --Composite primary key
4. Test(TestId, ParameterId, MethodId....) --Composite primary key being used as foreign key
5. Sample(SampleId, TestId....)

因此，当前查询不会考虑复合主键方案而生成路径。

So current query do not generate path considering Composite Primary key scenario.

我想生成类似的路径。

[Method.MethodId]-> [ParameterMethodMap.MethodId，ParameterMethodMap。 ParameterId] -> [Test.TestId]-> [Sample.SampleId]

[Method.MethodId] -> [ParameterMethodMap.MethodId, ParameterMethodMap.ParameterId] -> [Test.TestId] -> [Sample.SampleId]

这是我想以某种方式合并复合主键的方法。我该怎么做？

This is somehow I am thinking to merge composite primary key. How can I do this?

推荐答案

好吧，通常，因为不能在CTE的递归部分中使用聚合函数，所以应该移动列串联部分连接到另一个CTE。您将拥有：

Well, in general because you cannot use aggregate functions in recursive part of CTE, you should move column concatenation part to another CTE. You will have:

    ;

WITH CTE_FKCols
AS (
    SELECT FK.NAME
        ,'[' + STUFF((
                SELECT ','
                    ,object_name(Col.object_id) + '.' + col.NAME
                FROM sys.foreign_key_columns C
                INNER JOIN sys.columns Col ON Col.object_id = c.referenced_object_id
                    AND col.column_id = c.referenced_column_id
                WHERE C.constraint_object_id = FK.object_id
                FOR XML PATH('')
                ), 1, 1, '') + ']' Cols
    FROM sys.foreign_keys FK
    )
    ,CTE
AS (
    SELECT fk.create_date
        ,fk.modify_date
        ,fkc.constraint_object_id AS ConstraintId
        ,OBJECT_NAME(fkc.constraint_object_id) AS ConstraintName
        --, fkc.referenced_object_id AS PrimaryKeyTableId
        ,OBJECT_NAME(fkc.referenced_object_id) AS PrimaryKeyTableName
        --, fkc.referenced_column_id AS PrimaryKeyColumnId
        ,OBJECT_NAME(fk.parent_object_id) AS ForeignKeyTableName
    FROM sys.foreign_key_columns fkc
    INNER JOIN sys.foreign_keys fk ON fk.OBJECT_ID = fkc.constraint_object_id
    )
    ,cte2 (
    create_date
    ,modify_date
    ,ConstraintName
    ,PrimaryKeyTableName
    ,ForeignKeyTableName
    ,Hops
    ,path
    )
AS (
    SELECT create_date
        ,modify_date
        ,ConstraintName
        ,PrimaryKeyTableName
        ,ForeignKeyTableName
        ,1
        ,CAST((
                SELECT F.Cols
                FROM CTE_FKCols F
                WHERE F.NAME = cte.ConstraintName
                ) AS NVARCHAR(4000))
    FROM cte

    UNION ALL

    SELECT cte.create_date
        ,cte.modify_date
        ,cte.ConstraintName
        ,cte.PrimaryKeyTableName
        ,cte.ForeignKeyTableName
        ,cte2.Hops + 1
        ,CAST((
                cte2.path + CAST('-> ' AS NVARCHAR(4000)) + (
                    SELECT F.Cols
                    FROM CTE_FKCols F
                    WHERE F.NAME = cte.ConstraintName
                    )
                ) AS NVARCHAR(4000))
    FROM cte2
    INNER JOIN cte ON cte2.ForeignKeyTableName = cte.PrimaryKeyTableName
        AND cte2.PrimaryKeyTableName != cte.PrimaryKeyTableName --Remove self-reference
    )
SELECT *
FROM cte2

这篇关于遍历数据库中的所有外键并生成路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！