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

The below query will return the object name and as well as the object type.

SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, '[db]') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE '%code/SQL command/any text to search for%'
ORDER BY Location, ObjectName

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%'