Dan,
It sounds like you are asking this:
How can I find out if a particular primary key value
exists in the foreign key column of any dependent table?
Or for example, does customer number #324989 have any
entries in the Orders table, the BadChecks table, or the
ReturnedMerchandise table?
The dependencies displayed in Enterprise Manager are
dependencies of database objects (tables, columns,
views, etc.), not of single rows of data, so I don't think
sp_depends or Enterprise Manager is going to have anything.
And I don't think there is simple function to do this,
but there are solutions to the problem. This
is something like a garbage collection problem for
pointers in C - how do we know we've deleted the last pointer
to allocated data?
In the context of PK/FK relationships, you could
keep a master count of referring rows. Keep an integer,
in the main table - not just a bit, and that count will
be at zero when there are no references. It would have to
be updated by triggers on the dependent tables.
Let us know if this is what you are asking about.
-- Steve Kass
-- Drew University
-- Ref: 5D4089BF-0B8A-4D45-B820-923A2D6886EE
Dan Hartshorn wrote:
Anybody know if there is a system function that can be used from a
stored procedure that determines if a given primary key has existing
dependencies? I want to make a check for this and if there are none, I
will delete the record. If there are, I will change a field called
bitStatus from 1 to 0. Enterprise Mgr. does something like this under
All Tasks, Display Dependencies. The normal way I do it is to manually
check for the existance of the primary key in every dependent table.
SQL 2000 server