How to get the TARGET tables from a stored procedure-CodePudding

I am aware of this query that can pull referenced tables from a SQL Server stored procedure.

For example, if I have this stored procedure:

UPDATE tbl1 
SET symbol = tbl2.symbol, symbol2 = tbl2.symbol2
FROM tbl1
JOIN tbl2 ON tbl1.PK = tbl2.PK

This query:

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'

and this one:

SELECT
    o.name
FROM
    sys.sql_modules sm
INNER JOIN sys.objects o ON
    o.object_id = sm.object_id
WHERE
    sm.definition LIKE '%<table name>%'

both return tbl1 & tbl2 as the response.

My question is this: tb11 is ReadWrite as the SQL shows, and tbl2 is ReadOnly.

How do I update my queries to make the distinction?

So my output should be:

Response
========
tbl1, ReadWrite
tbl2, ReadOnly

Thanks!

CodePudding user response：

Try sys.sql_dependencies, eg

select  object_name(d.referenced_major_id) referenced_object, 
        object_name(d.object_id) referenced_by,  
        max(cast(d.is_updated as int)) is_updated
from sys.sql_dependencies d
join sys.objects o
  on d.object_id = o.object_id
where o.type_desc = 'SQL_STORED_PROCEDURE'
group by d.referenced_major_id, d.object_id
order by d.object_id

But beware that procedures with deferred name resolution and stored procedures that access tables with dynamic SQL won't be tracked

CodePudding user response：

The DMV sys.dm_sql_referenced_entities contains this information.

The following query should get you exactly what you need

SELECT
  name,
  referenced_entity_name,
  is_updated
FROM sys.procedures p
CROSS APPLY sys.dm_sql_referenced_entities(SCHEMA_NAME(p.schema_id)   '.'   p.name,'OBJECT') r
WHERE referenced_minor_name IS NULL;

db<>fiddle