April 1, 2020 at 5:24 pm
In the past I could rely on the View Dependencies feature in SQL Server Mgt Studio to show me all objects that an Object depends on or that the object is dependent on. I don't know when this changed but it no longer works reliably. The View Dependencies works for some parent/Child tables but not all.
After some searching I found a number of recommendations on how to find what child tables are referencing a parent table, where a Foreign key in the child points to a Primary key in the parent table. For some reason these are not working reliably either and I am at a loss to understand why. I verified that if I try to delete a row from the parent table which has a row in the child table that references it I am unable to do so which makes sense. What I can't figure out is why none of the methods I've found will show this relationship, this dependency.
So with that understood, what is a reliable method on SQL Sever 2014 to get a list of all child tables that are referencing a parent table via the primary key = foreign key?
Thank you
Kindest Regards,
Just say No to Facebook!April 1, 2020 at 5:57 pm
There are a number of catalog views that you can query to find dependencies. I don't have SQL Server in front of me at the moment, so I'm not sure what their exact names are, but there'll be one that has foreign keys, one that shows what columns make up the foreign keys, and you'll need to join possibly to sys.objects and sys.columns to get the table and column names. Alternatively, there may be some sp_ stored procedures that will give you dependency information, although you may have to run them once per table.
John
April 1, 2020 at 6:13 pm
Agreed.
Views sys.foreign_keys and sys.foreign_key_columns should give you FK details.
Also look at sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.
For just any reference to another object, not for a defined relationship, try sys.sql_expression_dependencies. For example, if a stored proc SELECTs from a given table, that should be recorded in that view.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 1, 2020 at 6:22 pm
I will query on the system catalogs but know that so far everything Iv'e found either works only for non-table objects (i,.e. Views, Stored Procedures, UDF's) or does not work for all Parent/Child relationships which has me dumb founded.
For example I've tried both sys.sql_expression_dependencies and sql_expression_dependencies and while both show dependencies they are only for non-table objects such as views and Stored Procedures which is find weird because the description for these says ALL dependencies. Perhaps tables are not dependencies even when one is a child table.
I've also Tried sp_fkeys to get a list of all FK's for the specified table and while it shows most of the Foreign keys it does not list them all. All objects in my DB are of the same default schema DBO. Just mentioning that in case it matters.
NOTE: I can get the View Dependencies in SSMS to work for most parent/child tables but not all even though all are using Primary/Foreign keys. Needless to say this has left me very confused.
Kindest Regards,
Just say No to Facebook!April 1, 2020 at 6:34 pm
Those work but not for tables just other objects like Stored Procs.
If I have a table named MYPARENT and there are 5 other tables that each have a foreign Key that references the Primary Key column in MYPARENT how do I construct T-sql that when MYPARENT is specified will enumerate the 5 tables that have FK's that point to MYPARENT?
Perhaps I'm using the wrong terminology when i say dependencies and that's why I'm having trouble with this.
Kindest Regards,
Just say No to Facebook!April 1, 2020 at 6:38 pm
As I noted above, views sys.foreign_keys and sys.foreign_key_columns should give you FK details. I can't imagine how your SQL doesn't have those views, since they've been around a long time.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 1, 2020 at 7:00 pm
UPDATE: I think I found the problem and the error is on my side. I made some assumptions about the DB I'm working with right now (not our regular DB) that I should not have.
Thank you all for taking the time to help. The input you have provided will still prove useful in the future.
Kindest Regards,
Just say No to Facebook!Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply