Debuting sp_helpExpandView

Who here hates nested views? I sure do. And whenever I find them used in code, untangling them can be a behemoth of a task.

Having faced them one too many times, I finally became fed up. I thought to myself, that there has to be a better way than opening up each object, checking each table reference, and crawling further and further down the rabbit hole. SQL Server must have functionality buried within it that would allow me to programmatically dissect and untangle a nested view.

So I started writing a script against system views. But then I ran into some roadblocks. As I researched those, I discovered the DMF sys.dm_sql_referenced_entities(). And thanks to that wonderful DMF all of those problems went away.

The result is a script tool that I am calling sp_helpExpandView!


sp_helpExpandView will take a view & return a list of all objects referenced underneath. V1 supports references to child views, tables, inline functions, table valued functions, and 1 level of synonyms.

Output comes in two formats – vertical and horizontal.  Default is ‘all.’

EXEC sp_helpExpandView
@ViewName = ‘[schema].[view]’,
@OutputFormat = ‘[all|vertical|horizontal]’

Download Latest at GitHub – Update Available: 2016-05-05

Known Issues:

  • Synonyms against objects not fully qualified with 3 part names will not be correctly processed.
  • Does not process 3 part names correctly. ex: ([db].[schema].[object])
  • If no schema specified, does not default dbo. schema.
  • Error in output if 3 part name used in underlying code & no schema specified. ex: ([db]..[object])

I hope people find this helpful.  I would love to hear any suggestions or feedback you may have.

Special thanks to Ben Thul (b|t), who mentioned PARSENAME() to me on Twitter. I’d forgotten that function existed & it helped clean some things up.

9 thoughts on “Debuting sp_helpExpandView

  1. SAinCA says:

    First time use: error 446, Sev 16, State 11, Cannot resolve collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in CASE operator for DISTINCT operation.

    • Hi Stephen. Thanks for giving my script a whirl. I see how your system is using a different collation, which is causing the above issue. I’ll add it to my backlog of issues to address. Thanks!!

  2. Greg R says:

    I have question about installation. Is it intended you have create proc in each database you want to run it in? Or should it just be created in Master? I created as-is (in master) and it couldn’t find any of views in my user database, b/c it’s looking for object_id in master. So I then created proc in my user database (by changing out Use Master with Use mydatabase in script) and it works. But wasn’t sure if that was intended behavior? Feel like I’m missing something. Thanks.

    • Hi Greg, the procedure is intended to be created in master & is set to become a system procedure. That’s the very last command in the script. Thereafter, you must switch to the database you’re interested in & can run it there.

  3. Hi Andy,
    I tried out your sp_helpExpandView procedure today, after finding it in the comment you left in Brent Ozar’s blog (

    I also tried out EXEC sp_helpExpandView For All Views.sql from your GitHub site. I just happened to try it out on AdventureWorks as my first test. It bombs with the message:

    EXEC sp_helpExpandView @ViewName = ‘Person.vAdditionalContactInfo’, @OutputFormat = ‘Vertical’
    Msg 213, Level 16, State 7, Procedure sp_helpExpandView, Line 450 [Batch Start Line 0]
    Column name or number of supplied values does not match table definition.

    I’ve done some minimal troubleshooting, and it appears as if the code bombs on views where one gets the following message, in SSMS, when right-clicking a view and selecting Design:

    “The following errors were encountered while parsing the contents of the SQL Pane:

    SQL text cannot be represented in the grid pane and diagram pane.”

    I realized this is a somewhat contrived example, using AdventureWorks, but just wondering if you have any type of workaround?


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.