Search This Blog & Web

Tuesday, July 6, 2010

Finding dependency using SQL Server 2005

I got a requirement today that a developer needs to return all dependent procedures on a table "ABC" because he has changed a column name and need to change it on all its dependent objects.

I gave him first solution. He can use SSMS feature to find dependent objects.
Step1: to get this you can go to required table --> right click -- > click view dependcy

Step2: On clicking view dependency you can see dependent procedures and tables of mentioned table or procedure.

After some time developer came to me and thanks for this help but he wants to know some SQL for this work.

I have given him following query that retun all dependent procedures that contains its required table name or column name. This query also return those procedures which use required table in a dynamic statement.

Using first query he can get all procedures. While 2nd and 3rd query return required table or column name from mentioned database.


Post a Comment