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.

[codesyntax lang="tsql"]


No Comments - Leave a comment

Leave a comment

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

January 2014
« Dec   Feb »


  • @SQLTaiob: @pshore73 It all depends if you can sustain index not being there. If I have any doubt I do online=on and use higher maxdop. #sqlhelp
  • @pshore73: @SQLTaiob #SQLHelp the plan is to do them one at a time during off hours, it is a DB for vCenter so it is never really offline
  • @SQLTaiob: @pshore73 If you are ok with dropping the index and users not being impacted yes drop/create, otherwise I will do online=on. #sqlhelp
  • @pshore73: #SQLHelp 500+MB indexes, 90+% fragmentation, in terms of time, t-log use & proc use am I better off doing a rebuild or drop/create
  • @regbac: @NikoNeugebauer yes there is... UPDATE Subscriptions SET ExtensionSettings = ... FROM ReportServer.dbo.Subscriptions. #sqlhelp #ssrs
  • @NikoNeugebauer: Is there a way to modify SharePoint Reporting Services subscription via t-sql ? (Native & SharePoint) #sqlhelp #ssrs #ssrshelp
  • @SQLSoldier: @SQLDBA @SQLYard If not connected to the internet, will need to download manually and specify an alternate path for it. #sqlhelp
  • @SQLDBA: @SQLYard That's .NET framework 3.5. Enable through Windows features. #sqlhelp
  • @SQLYard: #sqlhelp, Issue installing SQL on a VM 2012 Please try enabling Windows feature :NetFx3. I have tried most of the fixes on the web.Thanks
  • @sqlconcepts: Dear #sqlhelp, does anyone have a bit of code that will help me estimate the space requirement for Change Tracking on a table?

Welcome , today is Tuesday, September 30, 2014