List all the Stored Procedures/Triggers/Views Associated with a specific Table
Below query will help to list all the Stored Procedures associated with a specific Table.
DECLARE @TABLENAME VARCHAR(50)
SET @TABLENAME='<table name>' --Pass the Table Name here
SELECT @TableName as "Table Name",A.Name AS "SP Name",B.Text as "SP Text" from SYSOBJECTS A
INNER JOIN SYSCOMMENTS B
ON A.ID=B.ID
WHERE B.TEXT LIKE '%'+@TableName+'%'
and A.TYPE='P'
Same way we can use the above query to find out View, Trigger. If you wanted to list views and Triggers change the value of "Type" as below,
TYPE='V' --For View
TYPE='TR' -- Trigger
Attachments
Follow