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.’
@ViewName = ‘[schema].[view]’,
@OutputFormat = ‘[all|vertical|horizontal]’
Download Latest at GitHub – Update Available: 2016-05-05
- 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.