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

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: