Question: 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 ?

Answer: 
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: 

3. You didn't mention your version of SQL Server. I'm assuming you are on 2005+.
4. I'm not sure how reliable the `is_updated` column is. I've seen major inconsistencies with the `is_selected` column.
5. 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.
6. For a lot more about how dependencies can't be relied upon, see [this post](https://sqlblog.org/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008).

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](https://sqlblog.org/2011/10/06/a-handy-search-procedure)
You can find the code for your stored procedures in `sys.sql_modules`. You can write a query on them like this:

```
SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id) 
FROM sys.sql_modules 
WHERE [definition] LIKE '%UPDATE YourTable%'
  AND [definition] NOT LIKE '%Bar = @Bar%'

```

Be warned that this assumes that your code looks EXACTLY like this. So for example if your code looks like this:

```
UPDATE
    YourTable

```

It's not going to find it because it doesn't exactly fit the pattern. You can play with the patterns though to get as close as possible to the results you want.

If you are interested I talked about these views in more detail [here](http://sqlstudies.com/2012/10/26/how-do-i-find-all-stored-procedures-or-views-that-use-a-specific-table/).