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'
- 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
- 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 http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx
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
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: