Tools and techniques for untangling monster views (nested views) 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?
You can enumerate nested views through this query:
SELECT DISTINCT
    sd.referenced_major_id,
    OBJECT_NAME(sd.referenced_major_id) as This_view,
    sd.object_id,
    OBJECT_NAME(sd.object_id) as need_this_view_to_work

FROM
    sys.sql_dependencies sd
WHERE 
    EXISTS (SELECT 1 FROM sys.views v WHERE sd.object_id = v.object_id)
    AND EXISTS (SELECT 1 FROM sys.views v WHERE sd.referenced_major_id = v.object_id);