Get the Foreign Key Hierarchy

• Mike Pearson

  Default port

  Points: 1481

  More actions

  October 13, 2007 at 11:42 pm

  #118482

  Comments posted to this topic are about the item Get the Foreign Key Hierarchy

• don.schaeffer

  Ten Centuries

  Points: 1245

  More actions

  May 27, 2009 at 2:53 pm

  #1001036

  Table with self references seem to cause an infinite loop in this script. I added "where S1.name SO.Name" to the table population query to avoid this.

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 1, 2010 at 11:29 pm

  #1112094

  I think this is a nice foundation script.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• MarkR-251611

  Right there with Babe

  Points: 731

  More actions

  June 19, 2013 at 3:23 pm

  #1625668

  How would you start with a specific table and get everything down from that?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  June 19, 2013 at 3:28 pm

  #1625670

  for a single table, i have this saved from Jason's website(the guy who posted before you

  --http://jasonbrimhall.info/2011/07/11/foreign-key-hierarchy-update/

  --SET XACT_ABORT ON

  --BEGIN TRAN

  DECLARE@StartParentTableVARCHAR(256)

  SELECT @StartParentTable = 'GMACT'

  ;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)

  --ROLLBACK TRAN

  --COMMIT TRAN

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  June 19, 2013 at 3:34 pm

  #1625673

  Lowell (6/19/2013)

  ---

  for a single table, i have this saved from Jason's website(the guy who posted before you

  --http://jasonbrimhall.info/2011/07/11/foreign-key-hierarchy-update/

  --SET XACT_ABORT ON

  --BEGIN TRAN

  DECLARE@StartParentTableVARCHAR(256)

  SELECT @StartParentTable = 'GMACT'

  ;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)

  --ROLLBACK TRAN

  --COMMIT TRAN

  Thanks for that follow-up Lowell.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• MarkR-251611

  Right there with Babe

  Points: 731

  More actions

  June 20, 2013 at 10:47 am

  #1626037

  Thanks, that worked great. Now I need to find only columns with a foreign key back to the original column. This gives me a good start.

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  June 20, 2013 at 11:22 am

  #1626051

  This reminds me, I think I have an update for that script.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events