Home > Functions and DMVs, SQL Queries, SQL Server, T-SQL > Get the list of dependent objects for any given object using query.

Get the list of dependent objects for any given object using query.

At times , we may have to find the objects on which a particular object is dependent on. The views , functions, SPs are dependent on one or more objects. Dropping objects from a database on which there are dependencies can be catastrophic.  Although there are properties like “SCHEMABINDING“, which we can use along with objects while creating it , Most of us don’t make a good use of it. It may become very tedious opening each view or object in a database to find the dependent objects, it becomes even more difficult to find the objects refering to a particular object or the object is encrypted. Given below are two queries which can help you achieve these tasks in a fraction of a second

use the below query, to get the list of objects on which a particular object is dependent on

SELECT DISTINCT OBJECT_NAME(DEPID) DEPENDENT_ON_OBJECT, OBJECT_NAME (ID) OBJECTNAME
FROM SYS.SYSDEPENDS
WHERE ID = OBJECT_ID(‘INDEX_STATS_VW’)

o/p will look like

OBJECTNAME DEPENDENT_ON_OBJECT
INDEX_STATS_VW INDEX_DETAIL
INDEX_STATS_VW INDEX_OPERATIONAL_STATS
INDEX_STATS_VW INDEX_PHYSICAL_STATS
INDEX_STATS_VW INDEX_USAGE_STATS

As you can see the view”INDEX_STATS_VW” is dependent on four other objects.

Use the below one, to fetch the objects which has dependency on a particular object

SELECT DISTINCT  OBJECT_NAME(DEPID) OBJECT_NAME, OBJECT_NAME (ID) USED_IN_OBJECT
FROM SYS.SYSDEPENDS
WHERE DEPID = OBJECT_ID(‘INDEX_DETAIL’)

OBJECT_NAME USED_IN_OBJECT
INDEX_DETAIL COLLECTIONSUMMARY
INDEX_DETAIL INDEX_STATS_VW

You can notice that the table “INDEX_DETAIL” is used in two other objects.

The above queries are faster than using properties/ opening each object to find dependencies. However, it is always advisable to use  “SCHEMABINDING”, so that accidental dropping of objects can be avoided.

OBJECTNAME DEPENDENT_ON_OBJECT
INDEX_STATS_VW INDEX_DETAIL
INDEX_STATS_VW INDEX_OPERATIONAL_STATS
INDEX_STATS_VW INDEX_PHYSICAL_STATS
INDEX_STATS_VW INDEX_USAGE_STATS
Advertisements
  1. March 7, 2010 at 10:00 am

    xanE6I Excellent article, I will take note. Many thanks for the story!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: