question

briskovich avatar image
briskovich asked

find out what table a stored procedure updates,inserts to, modifies

I want to be able to be able to filter the results of this query to in include only SprocName that has an INSERT or Update or Modify statement in them. Is there a way to do that? I was tasked with finding out what table each stored procedure writes to. Server 2005 btw SELECT DISTINCT QUOTENAME(OBJECT_SCHEMA_NAME (referencing.object_id)) + '.' + QUOTENAME(OBJECT_NAME(referencing.object_id)) AS SprocName ,QUOTENAME(OBJECT_SCHEMA_NAME (referenced.object_id)) + '.' + QUOTENAME(OBJECT_NAME(referenced.object_id)) AS ReferencedObjectName ,referenced.type_desc AS ReferencedObjectType FROM sys.sql_dependencies d INNER JOIN sys.procedures referencing ON referencing.object_id = d.object_id INNER JOIN sys.objects referenced ON referenced.object_id = d.referenced_major_id WHERE referencing.type_desc = 'SQL_STORED_PROCEDURE' AND referenced.type_desc='USER_TABLE' Order BY SprocName,ReferencedObjectName ; Thank you in advance for your help.
sql-server-2005system-databasessystem-viewssystem-stored-proc
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
You could join with sys.sql_modules and check out the definition column to get the whole definition of the stored procedure.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Magnus, Yeah that would be cool but there are 2600 sprocs to go through. I was hoping to set and argument that would filter out those sprocs that don't change any data.
0 Likes 0 ·
Oh, unless that's found in system views which I'm not aware of, I'd go for regular expressions to find INSERT/UPDATE/DELETE/MERGE-commands. You could either create a CLR-function to return a DataTable with ProcedureName/Table-combinations, or expose the RegEx-functions to T-SQL using CLR. I'd go for the first, since what you're looking at is rather complex.
0 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered
FYI that sys.sql_dependencies is on the depreciation list according to MSDN. You should change your code to use sys.sql_expression_dependencies. It appears this view has a good bit more information than the old one does as well: http://technet.microsoft.com/en-us/library/bb677315
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.