SQL SERVER - Find Stored Procedure Related to Table in Database - Search in All Stored procedure

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

   ----Option 1
SELECT DISTINCT so.name
    FROM syscomments sc
            INNER JOIN sysobjects so ON sc.id=so.id
    WHERE sc.TEXT LIKE '%tablename%'
    ----Option 2
SELECT DISTINCT o.name ,o.xtype
    FROM syscomments c
            INNER JOIN sysobjects o ON c.id=o.id
    WHERE c.TEXT LIKE '%tablename%'