FK Hierarchy v 2.1

Categories: News, Professional, SSC
Comments: No Comments
Published on: August 9, 2011

Last month I published an update to my Foreign Key Hierarchy script.  Today, I am providing a new update for that script.  A friend (Rémi Grégoire) helped out with some mods to this script.  The change for this month is nothing too intrusive.  The script is now updated for those databases that are Case Sensitive.

[codesyntax lang=”tsql”]


This update should make it more usable for any that may be using it or is interested in using it.  Thanks for Rémi for taking the time to propose this update.

Foreign Key Hierarchy Update

Categories: News, Professional, Scripts, SSC
Comments: 1 Comment
Published on: July 11, 2011

Today I would like to revisit a post of mine that is rather old.  More precisely, the script in that post needs revisiting.  This is one of my more favorite scripts and I still have more ideas to implement with it.  The post/script in question can be found here.

In revisiting this script, I simplified it a bit.  I also had to work on a problem with it that occurs in Hierarchies with circular dependencies.  Quite frankly, that was a huge pain in the butt.  There are some tricks out there to try and help with it – but I was having a hard time getting any of them to work in this scenario.  I also updated the script to better handle self-referencing objects.

When you have circular references, and are trying to recurse the tree via a CTE, an instant blocker comes into play.  You can only reference the anchor of the Recursive CTE once.  Fixing a circular reference would be many times easier if you could reference the anchor twice.

In the end, the biggest hint to getting this to work came from this post.  For it to work, I needed to find which combination of fields would work best.  I finally settled on using the Object_ID to help reduce my pain.  I settled on using the following in the anchor:

[codesyntax lang=”tsql”]


and the following in the recursive definition.

[codesyntax lang=”tsql”]


You can see that I am concatenating into a string for this column.  This seems to work well for the purpose of eliminating those circular references.

Other adjustments to the script are not quite as significant but there is a small performance gain to be seen by these subtle changes.  The most notable is the change to remove the two joins out to sys.columns in order to get the column names of the Parent and Child objects.  In lieu of these joins, I am using the COL_NAME() function.  This little change came to me thanks to a little work done last week on my statistics script that you can read here.

The final notable change comes in the naming of the CTEs in this script.  I decided to rename the CTEs to something a bit more meaningful.  In this case, Hierarchy and Ancestry are much more appropriate.

Without further adieu, here is the next major revision of that script.

[codesyntax lang=”tsql”]


I hope you will play with this script, test it out and make recommendations or even find bugs with it and let me know.

Key Discovery III

Categories: News, Professional, Scripts, SSC
Comments: 2 Comments
Published on: February 1, 2010

In Part I and Part II of the series, I discussed documenting and discovering Primary Keys and Clustered Indexes.  In this article I will show how to document table relationships from a hierarchical standpoint.  The type of documentation I will demonstrate in this article will prove insightful and useful – if for nothing more than pure documentation.

I have had the pleasure of sharing this information in a User Group meeting.  That was the first time I had presented at a User’s Group meeting.  I gave that presentation shortly after hammering out the script to help document a database as a part of a project that is still on-going.  For the project, I first set out to find a script that fulfill the requirements for me.  All I could find were scripts similar to what I had already written.  They would present the Tables and foreign keys for those tables as well as the child tables.  The problem with this was that the list was in no particular order.  I then found another script that presented the data in an hierarchy – however it was not a true hierarchy.  I needed a script that could lay out the hierarchy of the foreign keys in the database when provided with one table from the database as the starting point.  This is how I define the perspective.  Now would be a good time to discuss the requirements I had defined for this script.

  1. Document Foreign Key Relationships
  2. Foreign Key Relationships listed in Hierarchical Format
  3. Create Perspectives (View of Hierarchy Tree from Specified Parent Table)
  4. No Cursors or While Loops

Why are these requirements necessary?  This is to help document a database.  I needed to be able to see the ERD at a reasonable size.  By adding the perspective requirement I could take a large database and reduce the number of objects displayed very quickly.  Furthermore, by using a script, I can create the documentation that displays the table relationships very quickly.  This information also helps in knowing the insert and delete order of data in the respective tables as they relate to one another.

The scripts I could find on the net were quickly eliminated due to the requirements.  Most used a looping mechanism, while others did not create a true hierarchy.  Thus I turned to my own information store to develop a script that could traverse the system views and create the kind of report that I needed.

To meet the requirements, I started looking to a CTE.  And yes a recursive CTE at that.  I know – not 100% set-based due to the recursion – but, it is very efficient for this purpose.  In scenarios such as this, this kind of solution is acceptable.  So, starting with the base query:

[codesyntax lang=”sql”]


This query gets me pretty close to the desired outcome and only needs a few more tweaks.  The above script more or less does the same sort of thing I saw other scripts doing that I found from internet searches.  The tables and foreign key level are displayed, but no linkage is quickly displayed by the query.  That can be resolved by adding a varbinary to the the query and then concatenating the value with each recursion.

[codesyntax lang=”sql”]


Ok, now the query is very close to what I need.  From the requirements, I only need to alter the script just a bit more in order to be able to create a perspective.  The perspective is achieved through variables and a slight change to the CTE.

[codesyntax lang=”sql”]


Through the above script, I am now able to achieve all of the requirements that had been pre-defined.  In testing the query, however, I discovered that the query was not yet quite complete.  There comes a time when there is an additional foreign key defined on the table that does not fit into the above query as a downstream perspective of the origin table.  There may be an occasion where an additional “parent” table needs to be displayed in the query.  Thus I must be able to traverse back upwards now in order to complete the perspective.  This was achieved through the following query.

[codesyntax lang=”sql”]


This query can easily be modified to pull back just the unique table names that are found in the CTE.  Why would that be necesary?  That would provide an easy method to pull back unique tables in the event that an ERD is to be derived from the perspective.  I didn’t delve too much into any of the scripts presented here.  At this point in the series, the only complexity that may need to be explained is the recursive piece of the scripts.  I think there are plenty of articles on that very subject.  The main goal here is to show that the documentation of FKs can be made relatively simple as well as provide plenty of insight into the database.  Test the queries.

I am including the scripts and the execution plan for the final script.  Included in the scripts will be the presentation given on the same subject.  The inclusion of that slide deck is a secondary reason for not getting into great detail in this post.  You can download it here.

The final blog in this particular series will be very short. I will cover the need for indexes on FKs. This is another topic that has been recently discussed. I will explore a couple of different scripts and compare and contrast those scripts performance.

Primary Key Discovery II

Categories: News, Professional
Comments: 3 Comments
Published on: January 12, 2010

In Part I, I discussed a couple of scripts that could be used to help discover some vital information about all of the PKs in the database very quickly.  I also delved into a performance analysis of the two queries.

The scripts presented showed a very basic discovery of Primary Key information.  I decided that I could use more information at my fingertips than the data set provided in that article.  So, in this article, I want to demonstrate a few more scripts to be able to achieve my goals.  These scripts will cover some clustered index information.

In SQL Server, when you create a Primary Key, the default behavior is to create a Clustered Index on the Primary Key.  Though this is the default, it can be overridden.  Thus, one should know what tables have Clustered Indexes created on them.  It is also desirable to know upon which columns Clustered Indexes have been created.

To get to the Clustered Index Information, the Query is not much different than the one I used to retrieve the Primary Key information.  One could make the assertion that this is due to the default behavior of creating a Primary Key and Clustered Index in one fell swoop.  They are certainly closely knit together.

See the difference?



Simple little change to get slightly more detailed information and something a little more intuitive about the database.  What does this tell me?  Well, I can now map these results to my PK results and see what PKs are also Clustered Indexes.  This is simply achieved by uncommenting the is_primary_key line in the script.  This small change will change the results to only show the Primary Keys and their Clustered Indexes.  Even better, the script could be altered as follows to just pull back the Clustered Indexes that are not on Primary Keys.  Simple modification, uncomment the is_primary_key line and change it from an equality to an inequality evaluation.

So, now we know more information about the Primary Keys and Clustered Indexes.  That is, we know that information for any table that has a Clustered Index.  It isn’t unheard of to have tables present in the database that do not have a Clustered Index at all.  Knowing that information is essential as well.  Again, this is all information that can be garnered by tedious investigation into each table.  Or, we could quickly run a little script and get a quick result gaining quicker insight into the data we manage.

If any results are returned, then it would also be useful to know record counts for each of the tables in the result set.  Knowing this might give some information as to usage of the table and impact of queries against the table that is a Heap.  I was able to achieve this with the following:

Armed with this information, it is a little easier to go into a new environment and speak to the databases and data.  The learning curve is shortened and one will be able to spend more time doing other tasks pertinent to maintaining the database environment and learning the database environment.

Download scripts: PKDiscover2_scripts

Primary Key Discovery

Categories: News, Professional, Scripts
Comments: 4 Comments
Published on: January 5, 2010

As a part of my DB documentation process for a large database, I needed a method to quickly show me all of the Primary Keys in the database.  Not only did I want the PKs, but I wanted to know the DataType for each of the Primary Keys.

I began by fiddling with some queries first and then by searching on Google for something that might be fitting.  I found a couple of queries (one which was very similar to what I already had).  The queries used different views and thus sparked a question for me – which method was better?

So let’s start with the base query.

From this query, I can get the Column Name, Constraint Name (PK) and the Table to which it belongs.

Execution Plan for that Query:

With the Following IO Stats:

And here is the alternative base query:

The Execution Plan is much larger, and thus I have added the XML Plan for download via – Alt BaseQuery ExecutionPlan.

And the IO stats:

From both queries, we can see that they are returning the same data, same number of rows, and for this set particular run – both run in under 1 second (109 ms for Query 1 and 156 ms for Query 2).  For this base query both can be deemed acceptable.  However, I want to emphasize that Query 2 took nearly 50% longer to process in this test.

Now that we have a baseline, I need to be able to retrieve the data type for each of the columns in each primary key.  This was a part of the initial self-prescribed requirements.  Adding in the additional components for each query is a little different.  Once added in, I will be doing the final comparison in a single batch to illustrate a little more poignantly the differences in processing between these queries.

Query 1 needs the following additions (lines 3, and 9-13)

Query 2 needs the following changes (Lines 18, and 22-26)

When I run these in tandem, the query plan for Query 1 changes somewhat, and the Plan for Query 2 a little more.  Comparing the two, I see that Query 2 has a cost of 72% for the batch, and Query 1 has the remaining 28% for the batch.

Here is the XML Plan for both for download: Comparison_Plans.

Now the IO Stats:

Notice the one Table that is substantially and surprisingly different between the two sets.  In Query 1, sysiscols has 2119 scans and 4243 logical reads.  Both are substantially higher than Query 2 at 229 Scans and 459 logical reads.  Despite this information, we can find that Query 1 executes (during these tests) better than Query2.  The result of the testing could change depending on information used in testing.  If I were to just consider the IO stats, I may well opt for Query 2.  However, when I consider all factors (used in these tests), my results tell me to use Query1.

Download Scripts For this Article here.

page 1 of 1

June 2020
« May    

Welcome , today is Saturday, June 6, 2020