I have a huge database with 100's of tables and stored procedures. Using SQL Server 2005, how can I get a list of stored procedures that are doing an insert or update operation on a given table.


sys.sql_dependencies has a list of entities with dependencies, including tables and columns that a sproc includes in queries. See this post for an example of a query that gets out dependencies. The code snippet below will get a list of table/column dependencies by stored procedure

select sp.name       as sproc_name
      ,t.name        as table_name
      ,c.name        as column_name
 from sys.sql_dependencies d
 join sys.objects t
   on t.object_id = d.referenced_major_id
 join sys.objects sp
   on sp.object_id = d.object_id
 join sys.columns c
   on c.object_id = t.object_id
  and c.column_id = d.referenced_minor_id
where sp.type = 'P'
  • The MSDN Library says that this is now deprecated and should be replaced with sys.sql_expression_dependencies. Unfortunately I can't get the equivalent to work. In the query shown in the answer d.object_id no longer exists. I tried d.referencing_id but that didn't work either. Any idea how to use sql_expression_dependencies?
    – Drew
    Jul 26 '11 at 14:08
  • 1
    On sys.sql_expression_dependencies, referencing_id is the item that references the target, and referenced_id is the dependency. The table also has schema and entity names for the referenced entities in the table. Minor IDs refer to column numbers, with 0 indicating no particular column reference. Jul 26 '11 at 14:45
  • I think I have all that down. I think the issue is that all of my sql_expression_dependencies rows have a 0 for referenced_minor_id. The analogous query of sql_dependencies returned many non-zero rows and I'm surprised the results are as different as they are. It seems to effectively makes sql_expression_dependencies a lot less useful because I often want to see which sprocs are referencing a specific column. For reference, I did a select count(*) from both where referenced_minor_id <> 0. There are 38119 such rows in sql_dependencies, and 112 in sql_expression_dependencies.
    – Drew
    Jul 27 '11 at 12:59
  • 1
    @Drew - Yes, it looks like the sproc references don't list individual columns in sys.sql_expression_dependencies. There are some functions in SQL Server 2008+ - sys.dm_sql_referencing_entities, sys.dm_sql_referenced_entities - that will do it for a specific entity and will return a record set with column level dependencies. Jul 27 '11 at 16:19
  sysobjects so inner join syscomments sc on so.id = sc.id
  sc.text like '%INSERT INTO xyz%'
  or sc.text like '%UPDATE xyz%'

This will give you a list of all stored procedure contents with INSERT or UPDATE in them for a particular table (you can obviously tweak the query to suit). Also longer procedures will be broken across multiple rows in the returned recordset so you may need to do a bit of manual sifting through the results.

Edit: Tweaked query to return SP name as well. Also, note the above query will return any UDFs as well as SPs.

  • this is giving me the SP id... can i reach the SP name with this id?
    – Vinod
    Sep 23 '08 at 8:06
  • Yep, I've tweaked the query to include that. I see there's now a couple of other answers been posted that will get you it as well. Sep 23 '08 at 8:11
  • My answer here: stackoverflow.com/questions/119679/… is almost identical (returns name). However, I think Luke is right in that there's no need to use DISTINCT as in my answer Sep 23 '08 at 8:22
  • It works, but not for every case. For example if somebody uses more spaces or tab characters between the SQL keywords and the table name.
    – splattne
    Nov 12 '08 at 8:12

Use sys.dm_sql_referencing_entities

Note that sp_depends is obsoleted.

MSDN Reference


You could try exporting all of your stored procedures into a text file and then use a simple search.

A more advanced technique would be to use a regexp search to find all SELECT FROM and INSERT FROM entries.


If you download sp_search_code from Vyaskn's website it will allow you to find any text within your database objects.



This seems to work:

so.name as [proc],   
so2.name as [table],   
from sysobjects so   
inner join sys.sql_dependencies sd on so.id = sd.object_id  
inner join sysobjects so2 on sd.referenced_major_id = so2.id  
where so.xtype = 'p' -- procedure  
and is_updated = 1 -- proc updates table, or at least, I think that's what this means
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'

This link was used as a resource for the SP search.

Your Answer

