Tuesday, March 3, 2009

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 this to find out View, Trigger etc..
V: View
TR: Trigger

See the out put…

No comments: