sp depends in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 133 of sequel server
tutorial in this video we'll discuss SP
underscore depends system short
procedure in sequel server there are
several ways to find object dependencies
one of the options is to use view
dependencies feature in sequencer
management studio but discuss this in
part 131 another option is to use sequel
server dynamic management functions such
as DM sequel referencing entities and DM
sequel referenced entities will discuss
this in part 132 the third option is to
use the system stored procedure SP
underscore depends we'll discuss this in
this video so SP underscore depends is a
system stored procedure that returns
object dependencies you can see the
syntax of the stored procedure right
here execute the name of the stored
procedure and this procedure has got one
parameter object name that is the name
of the object for which you want to find
the dependencies so this object name can
be a table name or a view name or a
procedure name etc so for example if you
specify a table name as the argument
value then the views and procedures that
depend on that specified table name are
displayed on the other hand if you
specify a view or a procedure name as
the argument value then the tables and
views on which that specified view of
procedure depends are displayed let's
understand this with an example so for
the examples in this video we'll be
using these two objects this employees
table and this stored procedure so now
if you look at the definition of the
stored procedure we know that this
depends on this employees table now
let's use the system stored procedure SP
underscore depends and the name of the
table is employees now when we execute
this we expect this stored procedure to
be returned because this stored
procedure depends on that table
okay now let's copy the name of the
stored procedure and then use that as
the argument value for SP underscore
depends store procedure now let's
execute this and see what we get
look at that when we specify a stored
procedure name as the argument value
then we get the name of the table on
which that stored procedure depends not
only the name of the table but also the
name of the columns within that table on
which this stored procedure depends so
depending on the argument value that you
specify here you will either get you
know the objects on which that object
depends in this case you know when we
specify a table name we get all the
views and stored procedures you know
that depend on that table if we specify
a view or a procedure name then we will
get all the table and view names on
which that stored procedure depends okay
now one important thing to keep in mind
is that this system stored procedure SP
underscore depends may not report
dependencies correctly always for
example so the baby have created these
objects is first I've created the table
and then I've created the stored
procedure and here we are using the
system stored procedure to find the
dependency so here it's working as
expected now let's do this I'm going to
drop this table so when we drop the
table so let's drop the employee's table
and when we refresh this we should not
have that employee's table now what I'm
going to do is create the table again
but before we do that look at the stored
procedure we still have this system
stored procedure SP and s go get
employees we didn't delete that now I'm
going to create the table once again at
this point if we go ahead and check the
dependencies of employees table look at
that when I execute that look at what it
says object does not reference any
object and no objects reference it
similarly when we execute this you know
SP underscore depends the name of the
stored procedure we get the same result
so in this case SP underscore depends is
not reporting the dependencies correctly
okay so keep that in mind when we use
this SP knows code
stored procedure another important point
to keep in mind is that this system
store procedure is on the deprecation
path meaning this might be removed from
the future versions of sequel server so
if you are planning to use this
procedure within your applications think
twice when a new version of sequel
server is released then probably it
wouldn't be supported in that and you
will may have to change your application
code so that's why sequel server
recommends using the dynamic management
functions that is either DM sequel
referencing entities or DM sequel
reference to entities instead of using
SP underscore depends systems to
procedure thank you for listening and
have a great day