x

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.
more ▼

asked May 11 '12 at 05:40 PM in Default

briskovich gravatar image

briskovich
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered May 12 '12 at 01:03 PM

Shawn_Melton gravatar image

Shawn_Melton
5.3k 17 21 29

(comments are locked)
10|1200 characters needed characters left
You could join with sys.sql_modules and check out the definition column to get the whole definition of the stored procedure.
more ▼

answered May 11 '12 at 08:00 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

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.

May 11 '12 at 09:12 PM briskovich
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.
May 12 '12 at 10:43 PM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x18
x13
x2

asked: May 11 '12 at 05:40 PM

Seen: 1299 times

Last Updated: May 12 '12 at 10:43 PM