SQL Tips by Namwar Rizvi

June 7, 2007

Finding object dependencies in SQL Server 2005

Filed under: Information,New Features,Query,SQL Server 2005,tips,TSQL — namwar @ 9:42 PM

If you want to find all the objects affected by the given stored procedure then you can use sp_depends stored procedure in SQL Server 2005. This stored procedure returns a very good set of information which can be used to determine the type of activity like select, update performed by the given stored procedure. Following example will give you the clear idea:

Use Adventureworks
Go

exec sp_depends ‘HumanResources.uspUpdateEmployeeHireInfo’
Go


name Name of the object
type
Type of object (table, view, stored procedure)
updated
mentioned column is updated
selected
mentioned column is selected
column
Name of the affected column

Similarly if you want to find which object is dependent on the given table or view then you can use the following query:

Use Adventureworks
Go

exec sp_depends ‘HumanResources.Employee’
Go

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: