Table Hierarchy updated

Recently a need resurfaced to explore the foreign key tree (hierarchy/genealogy) as it related to a specific table within a database.  As I pulled out the script from the repository, I realized there was some unfinished work to be done.  But there was also some polish that needed to be added.  This is an update to the most recent posting of that script.  You can see several revisions in the series at this link or the group here.

Some of the changes involve formatting and and labeling.  I added a new column called “Direction” to help understand the relationship of the key to the table in question.  I also changed up the FKGenealogy (formerly called SortCol) to reflect the source table more accurately in the case when the key comes from an ancestor rather than a descendant.  The Level of the FK was also modified to help understand a little better how far away the ancestor was in relationship to the origin table.

A final adjustment also comes from the Genealogy attribute.  Ancestors were all starting at the wrong point in the lineage.  I adjusted that so the lineage can be seen from the point in the tree that the ancestor is related rather than as a root direct from the origin table.

All in all, this script should make more sense to the user than the previous versions.

4 Comments - Leave a comment
  1. Clayton Groom says:

    Thanks for a great script. It has a fatal flaw that needs to be addressed though. You’ve ignored object schemas, which means you could be matching incorrectly by just using the table name. It is possible to have the same object_name() from different schemas. If you do all the work using just the object_id and column_id values in the common table expressions, you can just return the results in the last select.

    Below is a re-write to address databases with multiple schemas:

    DECLARE @StartParentTable VARCHAR(256) = ‘[app].[Workflow]’;
    DECLARE @StartParentTableID int = object_id(@StartParentTable);

    With Posterity ( ParentTableId
    , ParentColumnId
    , ChildTableId
    , ChildColumnId
    , FKLevel
    , FKGenealogy
    )
    AS (SELECT sfk.referenced_object_id AS ParentTableId
    ,sfkc.referenced_column_id AS ParentColumnId
    ,sfk.Parent_object_id AS ChildTableId
    ,sfkc.parent_column_id AS ChildColumnId
    ,0 AS FKLevel
    ,CAST(‘->’ + CAST(‘[‘+ OBJECT_SCHEMA_NAME(sfk.referenced_object_id) +’].[‘ +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 sfk.referenced_object_id = @StartParentTableId
    UNION All
    SELECT sfk.referenced_object_id AS ParentTableId
    ,sfkc.referenced_column_id AS ParentColumnId
    ,sfk.Parent_object_id AS ChildTableId
    ,sfkc.parent_column_id AS ChildColumnId
    ,FKLevel + 1
    ,STUFF((‘->’ + F.FKGenealogy + CAST(CAST(‘[‘+ OBJECT_SCHEMA_NAME(sfk.referenced_object_id) +’].[‘ +OBJECT_NAME(sfk.referenced_object_id) +’]’ AS VARCHAR(MAX)) + ‘->’ AS VARCHAR(MAX))
    ),1,2,”)
    FROM sys.foreign_keys sfk
    INNER Join Posterity F
    ON sfk.referenced_object_id = F.ChildTableid
    And F.FKGenealogy NOT LIKE ‘%’+CAST(‘[‘+ OBJECT_SCHEMA_NAME(sfk.referenced_object_id) +’].[‘ +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 sfk.referenced_object_id @StartParentTableID
    And sfk.referenced_object_id sfk.parent_object_id
    ), Ancestry (ParentTableId ,ParentColumnId, ChildTableId, ChildColumnId
    , FKLevel, FKGenealogy
    )
    AS (SELECT DISTINCT
    sfk.referenced_object_id AS ParentTableId
    ,sfkc.referenced_column_id AS ParentColumnId
    ,sfk.Parent_object_id AS ChildTableId
    ,sfkc.parent_column_id AS ChildColumnId
    ,(FKLevel * -1)-1 AS FKLevel
    ,F.FKGenealogy + CAST(CAST(‘[‘+ OBJECT_SCHEMA_NAME(sfk.parent_object_id) +’].[‘ +OBJECT_NAME(sfk.parent_object_id) +’]’ AS VARCHAR(MAX))+’->’ AS VARCHAR(MAX))
    FROM Posterity F
    INNER Join sys.foreign_keys sfk
    ON F.ChildTableid = sfk.parent_object_id
    And F.ParentTableid sfk.referenced_object_id
    INNER Join sys.foreign_key_columns sfkc
    ON sfkc.constraint_object_id = sfk.OBJECT_ID
    UNION All
    SELECT sfk.referenced_object_id AS ParentTableId
    ,sfkc.referenced_column_id AS ParentColumnId
    ,sfk.Parent_object_id AS ChildTableId
    ,sfkc.parent_column_id AS ChildColumnId
    ,F.FKLevel -1
    ,STUFF((‘->’ + FKGenealogy + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + ‘->’ AS VARCHAR(MAX))
    ),1,2,”)
    FROM Ancestry F
    INNER Join sys.foreign_keys sfk
    ON F.ParentTableid = sfk.parent_object_id
    INNER Join sys.foreign_key_columns sfkc
    ON sfkc.constraint_object_id = sfk.OBJECT_ID
    WHERE F.ParentTableId not in (SELECT ParentTableId FROM Posterity)
    And sfk.referenced_object_id sfk.parent_object_id
    And F.ChildTableId not IN (SELECT ChildTableId FROM Posterity)
    )

    SELECT ‘[‘+ OBJECT_SCHEMA_NAME(ParentTableId) +’].[‘ +OBJECT_NAME(ParentTableId) +’]’ as ParentTable
    , COL_NAME(ParentTableId, ParentColumnId) as ParentColumn
    , ‘[‘+ OBJECT_SCHEMA_NAME(ChildTableId) +’].[‘ +OBJECT_NAME(ChildTableId) +’]’ as ChildTable
    , COL_NAME(ChildTableId, ChildColumnId) as ChildColumn
    , FKLevel
    , FKGenealogy
    ,Case
    When FKLevel > 0 Then ‘Descendant’
    When FKLevel = 0 Then ‘Root’
    End as Direction
    FROM Posterity
    UNION All
    SELECT ‘[‘+ OBJECT_SCHEMA_NAME(ParentTableId) +’].[‘ +OBJECT_NAME(ParentTableId) +’]’ as ParentTable
    , COL_NAME(ParentTableId, ParentColumnId) as ParentColumn
    , ‘[‘+ OBJECT_SCHEMA_NAME(ChildTableId) +’].[‘ +OBJECT_NAME(ChildTableId) +’]’ as ChildTable
    , COL_NAME(ChildTableId, ChildColumnId) as ChildColumn
    , FKLevel
    , FKGenealogy
    ,’Ancestor’ as Direction
    FROM Ancestry
    ORDER BY FKGenealogy ASC, Direction Desc
    OPTION (maxrecursion 500);
    go

    • Jason Brimhall says:

      Thanks. I agree that is a fatal flaw. I will update.

      • Jefferson Elias says:

        Hi,

        I’ve tried the version provided for schema support, but it never worked in my environment.

        Here is a version that worked.

        DECLARE @StartParentTable VARCHAR(256);

        SELECT @StartParentTable = ‘[SecurityDoc].[Contacts]’;
        DECLARE @StartParentTableID int = object_id(@StartParentTable);

        With Posterity (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, FKGenealogy) AS (
        SELECT OBJECT_SCHEMA_NAME(sfk.referenced_object_id) + ‘.’ + OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
        ,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
        ,OBJECT_SCHEMA_NAME(sfk.Parent_object_id) + ‘.’ + 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_SCHEMA_NAME(sfk.referenced_object_id) + ‘.’ + 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 sfk.referenced_object_id = @StartParentTableID
        UNION All
        SELECT OBJECT_SCHEMA_NAME(sfk.referenced_object_id) + ‘.’ + OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
        ,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
        ,OBJECT_SCHEMA_NAME(sfk.Parent_object_id) + ‘.’ + OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
        ,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
        ,FKLevel + 1
        ,STUFF((‘;’ + F.FKGenealogy + CAST(CAST(OBJECT_SCHEMA_NAME(sfk.referenced_object_id) + ‘.’ + OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + ‘;’ AS VARCHAR(MAX))
        ),1,1,”)
        FROM sys.foreign_keys sfk
        INNER Join Posterity F
        ON OBJECT_SCHEMA_NAME(sfk.referenced_object_id) + ‘.’ + OBJECT_NAME(sfk.referenced_object_id) = F.ChildTable
        And F.FKGenealogy NOT LIKE ‘%’+CAST(OBJECT_SCHEMA_NAME(sfk.referenced_object_id) + ‘.’ + 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 sfk.referenced_object_id @StartParentTableID
        And sfk.referenced_object_id sfk.parent_object_id
        ), Ancestry (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, FKGenealogy) AS (
        SELECT DISTINCT OBJECT_SCHEMA_NAME(sfk.referenced_object_id) + ‘.’ + OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
        ,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
        ,OBJECT_SCHEMA_NAME(sfk.Parent_object_id) + ‘.’ + OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
        ,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
        ,(FKLevel * -1)-1 AS FKLevel
        ,F.FKGenealogy + CAST(CAST(OBJECT_NAME(sfk.Parent_object_id) AS VARCHAR(MAX))+’;’ AS VARCHAR(MAX))
        FROM Posterity F
        INNER Join sys.foreign_keys sfk
        ON F.ChildTable = OBJECT_SCHEMA_NAME(sfk.parent_object_id) + ‘.’ + OBJECT_NAME(sfk.parent_object_id)
        And F.ParentTable OBJECT_SCHEMA_NAME(sfk.referenced_object_id) + ‘.’ + 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_SCHEMA_NAME(sfk.referenced_object_id) + ‘.’ + OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
        ,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
        ,OBJECT_SCHEMA_NAME(sfk.Parent_object_id) + ‘.’ + OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
        ,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
        ,F.FKLevel -1
        ,STUFF((‘;’ + FKGenealogy + CAST(CAST(OBJECT_SCHEMA_NAME(sfk.referenced_object_id) + ‘.’ + 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_SCHEMA_NAME(sfk.parent_object_id) + ‘.’ + 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 Posterity)
        And sfk.referenced_object_id sfk.parent_object_id
        And F.ChildTable not IN (SELECT ChildTable FROM Posterity)
        )

        SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, FKGenealogy
        ,Case
        When FKLevel > 0 Then ‘Descendant’
        When FKLevel = 0 Then ‘Root’
        End as Direction
        FROM Posterity
        UNION All
        SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, FKGenealogy
        ,’Ancestor’ as Direction
        FROM Ancestry
        ORDER BY FKGenealogy ASC, Direction Desc
        OPTION (maxrecursion 500);

Leave a comment

Your email address will not be published. Required fields are marked *










Calendar
January 2014
M T W T F S S
« Dec   Feb »
 12345
6789101112
13141516171819
20212223242526
2728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Thursday, June 29, 2017