Find all the SP's where a particular column is not updated

I want to find stored procedures where a particular column is not updated.

As an example

SP1:

BEGIN
      UPDATE YourTable
      SET    Foo = @Foo,
             Bar = @Bar
      WHERE  Id = @Id
  END

SP2:

BEGIN
      UPDATE YourTable
      SET    Foo = @Foo                 
      WHERE  Id = @Id
  END

So I want to get all the SP's where Bar is not updated in above example it should return SP2.

Note: one SP can have multiple update statements. Is there any possibility to get the table name of that update statement ?

Try this:

SELECT DISTINCT OBJECT_NAME([object_id])
FROM sys.sql_dependencies AS d
WHERE referenced_major_id = OBJECT_ID('dbo.YourTable')
AND is_updated = 1
AND NOT EXISTS 
(
  SELECT 1 FROM sys.sql_dependencies AS id
   INNER JOIN sys.columns AS c
   ON id.referenced_major_id = c.[object_id]
   AND id.referenced_minor_id = c.column_id
   WHERE id.object_id = d.object_id
   AND id.referenced_major_id = d.referenced_major_id
   AND c.name = 'bar'
);

Caveats:

• You didn't mention your version of SQL Server. I'm assuming you are on 2005+.

• I'm not sure how reliable the is_updated column is. I've seen major inconsistencies with the is_selected column.

• This does depend on your stored procedure being created after the objects and not being subject to deferred name resolution. You may want to recompile all of your stored procedures before relying on the output.

• For a lot more about how dependencies can't be relied upon, see this post.

Parsing is a real pain in the rear because all of your statements could have slight variations. You may be able to search for patterns like:

SELECT name FROM sys.procedures
  WHERE LOWER(OBJECT_DEFINITION([object_id]))
        LIKE '%update%yourtable%'
  AND LOWER(OBJECT_DEFINITION([object_id]))
      NOT LIKE N'%bar%=%';

But this has all kinds of ways to go astray - some of these patterns could appear in comments, false positives could come because you have an update to yourtable2, you could miss a row because an update references the column bar2, etc. etc.

I wrote a pretty comprehensive search procedure here, but it will only be as reliable as the search pattern you define:

• A handy search procedure

StackExchange Database Administrators Q#39227, answer score: 6