Friday, July 6, 2007

Table Dependency Issue

SQL Server's inbuilt feature of finding the dependency for a Table seems to have an issue. If a SP has just an "Insert into Table1" then this SP (say SP1) will not be shown as a dependent to Table1!! If I just add "Select * from Table1" in the above SP then SP1 is getting showed as dependent to Table1!!!!!!!!!!So if we are going to use the "View Dependency" feature then we are gonna have some trouble. The best way to get around this problem is to use the following "Text" search query
SELECT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%Table1%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)

No comments: