Walk inside DMV Stacks

Hi dears, we all know well about the power of SQL Server DMVs in so many aspects. That was the reason, we have decided to walk through this series of blogs.

Today we will see about  How do I know which stored procedures are using a particular table?

I always prefer to write something about the problem i have faced and how it was resolved. Recently we have recommended our developer teams to replace a function, which generates a sequence using a scalar-value function will be used to insert into a table,which gave us big troubles while it was accessed concurrently leads to much heavy locks. So we have recommended to use another table to maintain a identity value, to use the ident_current to get the value, hence table already have another identity column.

The solution was good and acceptable, but while implementing such a thing on a existing huge database which was used over many years and by many teams. We need to know what are all the insert stored procedures will hit this table. To safely replace the new solution, we should know clearly what are all the places needs to be touched. On such a cases, the below DMV is much helpful to tell us all the related database objects linked with a specific table.

I did’t wrote much about the DMV, but just execute it by passing a table name on to referenced_entity_name, it will tell you more stories..

SELECT

referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),

referencing_object_name = o.name,

referencing_object_type_desc = o.type_desc,

referenced_schema_name,

referenced_object_name = referenced_entity_name,

referenced_object_type_desc = o1.type_desc,

referenced_server_name, referenced_database_name

--,sed.* -- Uncomment for all the columns

FROM sys.sql_expression_dependencies sed

INNER JOIN sys.objects o ON sed.referencing_id = o.[object_id]

LEFT OUTER JOIN sys.objects o1 ON sed.referenced_id = o1.[object_id]

WHERE referenced_entity_name = 'Customers'