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!


TL;DR

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.’

Usage:
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.

Advertisements

5 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.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s