question

This tool should do the job: http://sqlcodeguard.com/ ![alt text][1] [1]: /storage/temp/1699-untitled.jpg

how about SELECT o.name, m.[definition] FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.[definition] LIKE '%UPDATE%' AND o.type_desc = 'SQL_STORED_PROCEDURE'

I agree with SQLShark - I don't think that there is an easy way to do this. If you are able to, the tools route looks like your best option. But, I like a puzzle :) This should get you somewhere close. It might not be comprehensive though - there are a lot of alternatives for laying out scripts. One that springs to mind immediately is table aliases. This script expects *TableName alias*. You might have *TableName as alias*, or even *TableName 'alias'* (heaven forbid). But it's tweakable to handle different circumstances. You'd just need to play about with the replace inputs. Note that I've laid this out in a step by step way to make it a bit simpler to understand. It would be entirely possible to rewrite this using fewer steps. Here's the script: Note: edited in response to comment from OP. ------------------- -- set up test data ------------------- create table dbo.MyUpdatedTable (Id int) go create procedure dbo.DirectUpdateTest as begin update dbo.MyUpdatedTable set Id = 1 end go create procedure dbo.AliasUpdateTest as begin update mut set Id = 1 from dbo.MyUpdatedTable mut end go create procedure dbo.AliasUpdateTestNoBlock as update mut set Id = 1 from dbo.MyUpdatedTable mut go -- find updates declare @TableName varchar(200) set @TableName = 'dbo.MyUpdatedTable' ------------------------------------- -- find direct updates - the easy bit ------------------------------------- -- edit: need to use vertical bars here too, to avoid returning MyUpdatedTable1, MyUpdatedTable2 etc -- eliminate whitespace characters and square brackets to avoid problems with alternative layouts -- using vertical bars allows us to break the input to words ; with cte as ( select object_id , SubstitutedDefinition = replace(replace(replace(replace(replace(sm.definition,' ','|'),char(13),'|'),char(10),'|'),'[',''),']','') from sys.sql_modules sm ) -- reduce multiple vertical bar instances to singles -- assume maximum of 5 consecutive bars to substitute , cte2 as ( select object_id , SingleBarredDefinition = replace(replace(replace(replace(SubstitutedDefinition,'|||||','|'),'||||','|'),'|||','|'),'||','|') from cte ) select SchemaName = s.Name , ProcedureName = p.Name from cte2 sm join sys.procedures p on sm.object_id = p.object_id join sys.schemas s on p.schema_id = s.schema_id where charindex('update|' + @TableName + '|', SingleBarredDefinition) 0 go ----------------------------------------- -- find aliased versions - a bit trickier ----------------------------------------- -- again, use substitution of whitespace characters -- we then look for the pattern || and use this to derive the alias -- edit added trailing vertical bar to allow for procedure defintions without begin / end block declare @TableName varchar(200) set @TableName = 'dbo.MyUpdatedTable' ; with cte as ( select object_id , SubstitutedDefinition = replace(replace(replace(replace(replace(sm.definition,' ','|'),char(13),'|'),char(10),'|'),'[',''),']','') from sys.sql_modules sm ) -- reduce multiple vertical bar instances to singles -- assume maximum of 5 consecutive bars to substitute , cte2 as ( select object_id , SingleBarredDefinition = replace(replace(replace(replace(SubstitutedDefinition,'|||||','|'),'||||','|'),'|||','|'),'||','|') + '|' from cte ) -- isolate the possible matches and calculate start position of alias , cte3 as ( select * , AliasStart = charindex('from|' + @TableName + '|',SingleBarredDefinition) + len(@TableName) + 6 from cte2 where charindex('from|' + @TableName + '|',SingleBarredDefinition) 0 ) -- calculate end position of alias , cte4 as ( select * , AliasEnd = charindex('|',SingleBarredDefinition,AliasStart) - 1 from cte3 ) -- extract the actual alias value , cte5 as ( select * , AliasName = substring(SingleBarredDefinition,AliasStart,AliasEnd - AliasStart + 1) from cte4 ) -- return the result select SchemaName = s.Name , ProcedureName = p.Name from cte5 c join sys.procedures p on c.object_id = p.object_id join sys.schemas s on p.schema_id = s.schema_id where charindex('update|' + AliasName + '|',SingleBarredDefinition) 0 ---------- -- tidy up ---------- drop procedure dbo.DirectUpdateTest drop procedure dbo.AliasUpdateTest drop procedure dbo.AliasUpdateTestNoBlock drop table dbo.MyUpdatedTable