SQL RNNR

1. Clayton Groom says:

   August 17, 2016 at 11:00

   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 ‘{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170}’+CAST(‘[‘+ OBJECT_SCHEMA_NAME(sfk.referenced_object_id) +’].[‘ +OBJECT_NAME(sfk.referenced_object_id) +’]’ AS VARCHAR(MAX))+'{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170}’
   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

   Reply
   • Jason Brimhall says:

     August 17, 2016 at 12:09

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

     Reply
     • Jefferson Elias says:

       December 19, 2016 at 01:27

       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 ‘{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170}’+CAST(OBJECT_SCHEMA_NAME(sfk.referenced_object_id) + ‘.’ + OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170}’
       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);

       Reply
       • Jason Brimhall says:

         December 19, 2016 at 19:19

         Thanks for the feedback.

         Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.