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.

No Comments - Leave a comment

Leave a comment

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

January 2014
« Dec   Feb »


  • @SQLTrooper: @gordoncoulter @arvisam I personally use WHILE (1=1) inf loops w/ WAITFOR DELAYS, in ~3K batches, etc. Adjust accordingly. #sqlhelp
  • @arvisam: @gordoncoulter you could also do a select into a new table and swap the two tables... could be much faster that way! #sqlhelp
  • @SQLSoldier: @pappyd I would at least schedule it to be done during an upcoming maintenance window after the migration. #sqlhelp
  • @SQLSoldier: @pappyd run updateusage is to make sure they are correct to start off with. 2/2 #sqlhelp
  • @SQLSoldier: @pappyd In earlier versions, there were sometimes inaccuracies in the usage stats. These issues have been fixed and the rec to 1/2 #sqlhelp
  • @pappyd: I am planning to run DBCC CHECKDB & sp_updatestats, but would like to not run UPDATEUSAGE to save some time #sqlhelp
  • @NedOtter: and then renames to valid extension when copy is complete. Thx. #sqlhelp 3/3
  • @NedOtter: it cannot get exclusive access. Wondering if my test is invalid, perhaps because MS copies with a different extension, #sqlhelp 2/3
  • @NedOtter: Anyone know log shipping internals? On 2ndary, if I use posh to exclusively lock tlog backup, restore fails because #sqlhelp 1/3
  • @rottengeek: what is MEMORYBROKER_FOR_XTP? super-happy it was able to give up memory when my prod server ran out, but i have SQL 2012 #sqlhelp

Welcome , today is Tuesday, June 28, 2016