sys dm sql referencing entities in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 132 of sequel server
tutorial in this video we'll discuss how
to find object dependencies using these
two dynamic management functions DM
sequel referencing entities and DM
sequel referenced entities will also
discuss the difference between
referencing entity and referenced entity
and the difference between schema bound
dependency and non scheme abounding
pendency this is continuation to part
131 so please watch part 1 3 1 before
proceeding notice the query right here
within this query we are using DM sequel
referencing entities dynamic management
function this function in this example
is going to return us all the objects
that depend on employees table I have
this exact same query already typed here
so when we execute this we should get
all the objects that depend on an
employee's table at the moment we have
got a function and two stored procedures
that depend on employees table now let's
understand the difference between
referencing entity and referenced entity
a dependency is created between two
objects within sequel server when one
object appears by name inside a sequel
statement stood in another object the
object which is appearing inside the
sequel expression is known as referenced
entity and the object that contains that
sequel expression is known as a
referencing entity we have an example
here which tells which is a referencing
entity and which is referenced entity so
here we have a very simple view of view
employees and all this view does is
select all the rows and columns from
employee's table so here this employee's
table appears within the sequel
expression so this employee's table is
the referenced entity and this is the
view that contains the sequel expression
so this view is the referencing entity
to get all the referencing entities we
use this dynamic management function DM
sequel referencing entities and to get
the reference to entities we use this
dynamic management function DM sequel
referenced entities now let's look at an
example of getting all the reference to
entities of a stored procedure so we
have the stored procedure right here as
we get employees and departments and we
are using this dynamic management
function DMC equal reference to entities
so this in this example is going to
return us all the reference to entities
of the stored procedure I have this
exact same query already typed here so
when we execute this we should get all
the reference to entities of the stored
procedure that is the entities on which
this stored procedure depends
we know that the stored procedure
depends on these two tables employees
and departments notice not only the
table name we also get the column names
within those respective tables on which
the stored procedure depends all right
now one important thing to keep in mind
is that for both of these dynamic
management functions to work we need to
specify the 2-part name that is the
schema name and the actual entity name
if we omit the schema name in this case
the schema name is tbo and when we
execute this query look at what's going
to happen we don't get any result so we
will have to specify the 2-part name the
schema name and the actual entity name
now let's understand the difference
between schema by own dependency and non
scheme abounding pendency let's actually
understand this with an example
there is another way of finding
dependencies in sequel server which is
because there's no previous video
session and that's by using sequel
server management studio graphical user
interface so I want to find all the
dependencies of the employees object
right click on that and select view
dependencies from the context menu and
here we can see all the objects that
depend on employee is table
and if you look at the dependency type
between the employees table and this
function the dependency type notice here
it says non schema bound dependency now
if I select this radio button right here
objects on which employees depends so
this employee's table depends on
departments and look at the dependency
type right here it is schema bound
dependency so what's the difference
between schema bound and non schema
bound well schema bound dependency
Previn
referenced objects from being dropped or
modified as long as the referencing
object exists let's understand what this
means so we know that between these two
tables departments and employees there
is a schema bound dependency so the
referencing object here is the
employee's table so this is referencing
departments now what is going to happen
if I try to delete departments since we
have the referencing entity still
present it is still existing if I try to
drop the referenced entity in this case
departments because of the schema bound
relationship between them sequel server
is going to prevent that deletion from
happening so if you really want to
delete the department's table you will
have to first delete the referencing
entity that is the employee's table and
then the referenced entity that is the
department's table whereas the non
schema bound dependency is not going to
prevent such a deletion okay an on
schema bound dependency does not prevent
the referenced object from being dropped
or modified so let's understand this
with an example
so now here we are creating a very
simple view view employees and all this
view is doing is selecting these columns
from employee's table okay by looking at
the definition of this view we can say
this view is dependent on this table
okay so let's create this view first and
let's refresh this folder so we have our
view there so I'm going to right-click
on that and select view dependences and
I'm going to select this radio button
objects on which view employees depends
we know that view is dependent on
employees table and if you look at that
dependency type between those two
objects look at the dependency died it
is non schema bound dependency okay so
this view depends on employees table and
the dependency is non schema bound
dependency since this is a non schema
bounden see if I try to delete the
employee's table sequel server is not
going to prevent the deletion from
happening look at that I am
able to delete that employees table
while this view this object is still
referencing that deleted object okay and
that is because of the non schema bound
relationship between them now let's go
ahead and delete this view first and
let's create this table back and now I'm
going to clear this view once again but
this time I'm going to use this option
with schema binding ok let's create this
view let's refresh the tables folder you
should have our employees table and
let's refresh the views folder we should
have a view as well I'm going to
right-click on that select view
dependencies and I'm going to select
this radio button objects on which view
depends and look at this when I select
in our employees table so the
relationship or the dependency type
between view and employees table is
schema bound the dependency so that
means now if we try to delete this
employee's table look at what's going to
happen we get an error and if you look
at the what the error says it says
cannot drop table DB or dot employees
because it's being referenced by object
view employees so that's the fundamental
difference between schema bound upon C
and non scheme abounding pendency thank
you for listening and have a great day