First a bit of context (venting). I'm working with a new vendor product which is really nothing more than a SQL Server database with a .NET interface.
First few weeks with this client and users are complaining about performance constantly with even the most bread and butter reporting functionality. I finally get DB access and discover the worst set of nested views (Is nested view a good database design?) I've seen in my life. We're talking a tree of views that is 5 nodes deep and each node has 3-5 leaves. So a single query has 20+ views behind it. It's mind bogglingly bad. The explain plan looks like a fractal after ten million iterations. I can't even read it.
I need to untangle at least this one monster view. But I have no ideas on how. I found this post (http://www.midnightdba.com/Jen/2010/06/detangling-nested-views/) which has a handy SQL script that lists the referred objects but that information doesn't get me terribly far on getting the actual table references for each field in the parent view. If I were to do it by hand, it would probably take 2 weeks. If I got it down to inline form, are there any tools that could remove redundancies and unused fields and optimize the query?