January 23, 2018 at 9:29 am
Hi SSC,
Unfortunately I think I probably know the answer, but figured I'd ask anyway. I've been writing a proc to identify the proc(s) which update a given table. I can do this by using sys.dm_sql_referenced_entities where [is_updated] = 1. However if the update is mediated by a view, this doesn't seem to work. Consider this test setup
use Test
go
if object_id('dbo.TestTable', 'U') is not null drop table dbo.TestTable
create table dbo.TestTable (RID int)
go
if object_id('dbo.TestView', 'V') is not null drop view dbo.TestView
go
create view dbo.TestView as select RID from dbo.TestTable
go
insert into dbo.TestTable values(1)
go
if object_id('dbo.UpdateTestTableViaTestView', 'P') is not null drop proc dbo.UpdateTestTableViaTestView
go
create proc dbo.UpdateTestTableViaTestView
as
update dbo.TestView
set RID += 1
go
For the following function calls, the first returns no results, which isn't surprising (the proc doesn't directly reference the table). The second call (references to the view) does return data, but the is_updated flag is 0; it would seem because the view is mediating the update on the table.
select top 1000 *
from sys.dm_sql_referenced_entities('dbo.TestTable', 'OBJECT')
select top 1000 *
from sys.dm_sql_referenced_entities('dbo.TestView', 'OBJECT')
--where is_updated = 1
Is there any way to find view-mediated updates determinstically? or do I have to fall back to wildcard searches or naming convention things?
January 23, 2018 at 12:04 pm
January 23, 2018 at 4:51 pm
Xedni - Tuesday, January 23, 2018 12:04 PMHold that thought; I may have actually just written my statement wrong.
If you solved it, post it. Others may have the same problem.
January 23, 2018 at 7:25 pm
Lynn Pettis - Tuesday, January 23, 2018 4:51 PMXedni - Tuesday, January 23, 2018 12:04 PMHold that thought; I may have actually just written my statement wrong.If you solved it, post it. Others may have the same problem.
Hi Lynn,
I certainly will. TL;DR version is it's completely doable, but it depends on a couple of factors how it's done. My OP was just not taking those factors into account. What are those factors? Well I'm still wrapping my head fully around them, but I'll post them here (and probably a completely separate article too) once I'm confident in what I'm saying.
A little background; I'm building a proc which will tell you what proc(s) populate a given table, and I'm using sys.sql_expression_dependencies and sys.dm_sql_referenced_entities() to do so. Things are pretty trivial when you only have to worry about one database. They're slightly more complicated when the insert/update is mediated by a view (the subject of my OP), and considerably more complex when It can be mediated by one or more views (or the base table) via one or more procs. Additionally, walking those dependencies across databases is where things really get tricky. Think lots of sp_msforeachdb-like statements. Plus, [is_updated] is only reliably sourced from the DMF, and then, only when referencing the correct object on the correct database.
My actual situation involves
Anyway, long story short, my original issue CAN be solved, but depending on several factors, you have to do something slightly different. Once I can succinctly describe what those rules are, I'll let y'all know.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply