Get the View dependency in SQL Server

We have a view and want to drop that view because as per the business requirement there are no more used on it. Before dropping the view we need to see the dependency of that view in the SP and view. It will be very tides task to open all SP and view to search the dependency.

With the help of below SQL query we will get the dependency of this view.

select sp.name,sqlm.definition from sys.all_objects sp inner join sys.sql_modules sqlm on sp.object_id=sqlm.object_id where sp.type in ('V','P') and sqlm.definition like '%View Name%'

 Type:

V – View

P- Stored Procedure

See the example:

    

We can see the dependency

  

So before dropping the View, we need to do the impact analysis after that we drop any database object.