Trouble with Finding Dependencies between Tables using Keys

  • YSLGuru

    SSC-Insane

    Points: 21517

    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!
  • John Mitchell-245523

    SSC Guru

    Points: 148745

    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

  • ScottPletcher

    SSC Guru

    Points: 98427

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • YSLGuru

    SSC-Insane

    Points: 21517

    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.

    • This reply was modified 1 month, 4 weeks ago by  YSLGuru.

    Kindest Regards,

    Just say No to Facebook!
  • YSLGuru

    SSC-Insane

    Points: 21517

    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!
  • ScottPletcher

    SSC Guru

    Points: 98427

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • YSLGuru

    SSC-Insane

    Points: 21517

    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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply