In September 2009 I wrote one of my first blog posts on SQLBlog entitled Deriving a list of tables in dependency order. In it I provided a SQL statement that would return a list of all tables in a database in what I called dependency order i.e. by adhering to declarative referential integrity (foreign keys to you and me). Here is a portion of one such result after executing it against [AdventureWorks] sample database:
Very useful methinks and it got a fairly good reception in the comments. One particular comment (from telcogod) really caught my attention though, he/she said:
can this be used to create a wipeout script?
on a much bigger scale, but similar to delete all orderdetails and orders where customerid = 1
That really got me thinking about what could be achieved from this simple premise of deriving a table dependency order and what could then be done with it. I realised that what would be really useful, rather than discovering all the tables dependent on a particular table, would be discovering all the data in the database dependent on a particular row in that table. The idea germinated and sp_CascadingDataViewer was born!
Eleven months later (yes, really) and I have finally gotten round to putting something out in the wild that does what I just described. In the rest of this blog post I will go into detail about what sp_CascadingDataViewer is, what it does and how it does it but before that you may want to take a look at this short video that explains exactly what sp_CascadingDataViewer is for (if the video is not showing up for whatever reason then go ahead and watch it at http://vimeo.com/14072554).