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.
Every Byte Counts 
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!!
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.
Hi Andy,
I tried out your sp_helpExpandView procedure today, after finding it in the comment you left in Brent Ozar’s blog (https://www.brentozar.com/archive/2022/08/lets-make-september-our-free-community-tools-awareness-month/).
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?
Thanks!
Huh, that’s a new one on me. I’ll try to make some time to repro and investigate it further. Appreciate you sharing this.
Hi, AndyA client of mine has a lot of X-database views, it is really messy, and performance suffers.I’ve created my own scripts, but yours is better :-)They have databases with a total of FOUR different collations. This made the SP fail when populating the worktables, but I think I’ve fixed that by COLLATE-ing all strings to Default Collation.
I just tried to run the SP on ALL databases, and for some of them, I experience the error message that tomwickerath reported.
In my case, the error is due to VIEWS referring to non-existent tables or views. By adapting the script by Avarkx from StackExchange: https://dba.stackexchange.com/questions/82948/sql-tsql-to-find-invalid-views , I managed to easily DROP all the garbage in one operation.Thank you so much for your contribution!
Can we pass sql select statement considering mutiple joins as a parameter and extract all views/tables from it instead of converting it into view and then running sp_helpExpandView , my ask is sp_helpExpandView ‘select column1,2…. from A inner join B ….’ ?
Hi – thanks for the question. No you cannot pass in a T-SQL statement and have the procedure “parse and extract” all objects referenced in that statement. sp_helpExpandView relies on DMVs that have dependencies already defined between existing objects. This is why it must be executed against an object that already exists; the DMV information would not exist for an ad-hoc T-SQL statement.