11/11/2011
SP_Depends Wont Work
I had observed that sp_depends wont work properly in many scenarios. So i had wrote this code to fetch the dependent objects on a table.
/*********************************************************************** * Author : V.U.M.Sastry Sagi * Date : 11/11/2011 * Purpose: Returns all Objects Dependent on a table ***********************************************************************/ CREATE PROCEDURE spDepends ( @TableName VARCHAR(100) ) AS BEGIN DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT DISTINCT o.name AS [Object Name], o.xtype AS [Object Type]' SET @query += ' FROM syscomments c ' SET @query += ' INNER JOIN sysobjects o ON c.id=o.id ' SET @query += ' WHERE c.TEXT LIKE ''%' + @tableName + '%''' EXECUTE(@query) END