8

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Vinod
  • 31,933
  • 35
  • 96
  • 119

7 Answers7

14

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'
Community
  • 1
  • 1
ConcernedOfTunbridgeWells
  • 64,444
  • 15
  • 143
  • 197
  • The [MSDN Library](http://msdn.microsoft.com/en-us/library/ms174402.aspx) 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. – ConcernedOfTunbridgeWells 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. – ConcernedOfTunbridgeWells Jul 27 '11 at 16:19
10
select
  so.name,
  sc.text
from
  sysobjects so inner join syscomments sc on so.id = sc.id
where
  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.

Luke Bennett
  • 32,786
  • 3
  • 30
  • 57
  • this is giving me the SP id... can i reach the SP name with this id? – Vinod Sep 23 '08 at 08: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. – Luke Bennett Sep 23 '08 at 08:11
  • My answer here: http://stackoverflow.com/questions/119679/list-of-stored-procedure-from-table#119719 is almost identical (returns name). However, I think Luke is right in that there's no need to use DISTINCT as in my answer – Matt Mitchell Sep 23 '08 at 08:22
  • 1
    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 08:12
2

Use sys.dm_sql_referencing_entities

Note that sp_depends is obsoleted.

MSDN Reference

Gareth D
  • 885
  • 1
  • 9
  • 22
1

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.

Ilya Kochetov
  • 17,988
  • 6
  • 44
  • 60
1

This seems to work:

select   
so.name as [proc],   
so2.name as [table],   
sd.is_updated   
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
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
Corey Trager
  • 22,649
  • 18
  • 83
  • 121
1

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

http://vyaskn.tripod.com/sql_server_search_stored_procedure_code.htm

Michael Prewecki
  • 2,064
  • 4
  • 16
  • 28
0
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND (SC.Text LIKE '%UPDATE%' OR SC.Text LIKE '%INSERT%')
ORDER BY SO.Name

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

Matt Mitchell
  • 40,943
  • 35
  • 118
  • 185