I am going to explain here is how we can list all the dependent object of a specific object. In one of my staging database we need to change the name of a column, because this column name breaking the naming convention rule.But the problem is if we change the column name we have to change all dependent object also, I mean wherever we are using this column it need to change otherwise database object will not work.
We have following solution for this problem.
1) We can use sp_Depends 'Object_name ‘ to list all the object .
But with this SP we can’t get the list if the object is using dynamic query. I mean if object is used inside dynamic part then this SP will not help, we can see this in following example.
/*Creating procedure and using HumanResources.Department */
Create
procedure vtest
as
begin
declare @sql varchar(max)
set @sql = 'select * from HumanResources.Department where name like ''eng%'''
exec(@sql)
end
GO
exec vtest
GO
sp_Depends 'HumanResources.Department'
GO
Here we didn’t get the name ‘vtest’ Sp .
2) Using syscomments we can list the all dependent objects it will give the list of dynamic query also. We can see on following example :
/*Read data from syscomments table */
SELECT
distinct so.name
FROM
syscomments sc
INNER
JOIN sysobjects so ON sc.id = so.id
WHERE
charindex('Department', text) > 0
3) Using INFORMATION_SCHEMA.ROUTINES we can know the dependency of any of the table even if SPs of Function has dynamic query..
select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like '%tbalename$%'
3) Using Sql Server Management Studio.
Summary
As you can see from these different methods each gives you a different part of the answer. So to be safe none of these approaches is full proof. To get a complete listing you really need to employ a few different methods to ensure you are not missing anything.
To take this a step further let's take a quick look at creating some objects to see what these different methods return. For this next example we will create two stored procedures that rely on each other. This is probably not something you would do, but this helps illustrate the issue.
