Good day. I hope everyone's doing well. I have another annoying question. A colleague of mine wants to know if there's a script / way that he could use to establish which stored procedures access certain tables. Is there any way this can be found out?
You may use system sp like sp_depends or sp_msdependencies or you may refer this blog post thanks Madhu
If you're looking for all stored procedures that reference the object, then maybe this will help. It does the same thing as sp_depends, but can be filtered to a specific column. Declare @tableName varchar(128) , @columnName varchar(128); Select @tableName = 'myTableName' , @columnName = Null; --'myColumnName', optional Select o.name As 'dependency' , Max(Cast(d.is_selected As tinyint)) As 'is_selected' , Max(Cast(d.is_updated As tinyint)) As 'is_updated' From sys.columns As c With (NoLock) Join sys.tables As t With (NoLock) On c.object_id = t.object_id Join sys.sql_dependencies d With (NoLock) On t.object_id = d.referenced_major_id And d.class = 0 And d.referenced_minor_id = c.column_id Join sys.objects o With (NoLock) On d.object_id = o.object_id Where t.[name] = @tableName And c.[name] = IsNull(@columnName, c.name) Group By o.name Order By o.name;