To CTE or not to CTE, that is the Question

So my post really has nothing to do with the title.  This is not a post to help you determine whether a CTE is appropriate or not.  Or is it?

This month, we have the 18th installment in the TSQLTuesday series.  We are being hosted by Bob Pusateri  (Blog|@SQLBob) this month.  The essence of the topic this month is around CTEs (common table expressions).  There are a great many uses for a CTE in SQL server and this was a nice addition to the product.

One thing I like about CTEs is how much cleaner the code looks to me.  Another benefit for me is the recursion that is available through the use of a CTE.  An observation about CTEs is that a common use would be to use them to replace inline derived tables (which lends to cleaner looking code for some).

Think Think Think

I gave this topic a good long thought.  As I thought about the topic, I came to the conclusion that I had nothing new or unique on the subject.  I did however have some scripts that I posted once upon a time that would work very well for this topic.  Though it is a bit of a cop out, it is an appropriate solution for this month.

In the case of the CTE that I have chosen, there are multiple CTEs being used.  I use the CTE to recurse through data, and then to recurse that same data again – in reverse.  This particular script was created to traverse through system catalogs and create a hierarchy of table relationships.  I use this hierarchy to better understand the structure of the database and the interrelationship of the data between objects.  It is a cheap way of mapping out the objects in an effort to better understand it.

I had thought about using this script once upon a time for a different TSQLTuesday, but thought better of it that time.  Since the original post is more than a year old, it is a good time to bring it up and use it again.  Without further adieu, you can read about that script and CTE here.  I hope you enjoy.