none
Finding columns to join from database that has no relationships between tables designated RRS feed

  • Question

  • Hello -

    I am working with a database that does not have a database diagram.  When I try to create one, it doesn't show any relationships at all.  

    I need to join tables.  Looking for matching columns in tables is like the ol' needle in a haystack.

    Any suggestions will be appreciated!

    junior mint

    Monday, August 19, 2019 3:39 PM

Answers

  • Look for similar column names?  Hope the tables are named accurately and that helps?

    The only diagrams SSMS would ever show are the relationships that someone has set up SQL Server to *enforce*, this is usually far fewer than the logical relationships one might wish to query.  That's the whole point of a relational database, you can do ad-hoc stuff that nobody thought to set up earlier.

    Now, if the database was actually designed by a professional they should have a logical diagram someplace that would be very helpful to you.  But SQL Server has no feature to attach that in a way that helps users to find it.

    Josh

    ps - though you would expect at least a few foreign keys to primary keys would be enforced. BTW, what versions of SSMS and server, are you running?  Or if not SSMS what tool are you using?


    Monday, August 19, 2019 3:51 PM
  • I use sys.dm_exec_query_stats, join it to the plan and parse the plans.  Also I use missing indexes, start sys.dm_db_missing_index_groups, work your way through it.  Logic is you will find the predicates and between the two you will learn a whole lot about how the data model works. 
    • Marked as answer by Juniormint10 Friday, August 23, 2019 12:25 AM
    Monday, August 19, 2019 9:10 PM
  • Thanks, Mighty-O.  I've give it a shot!
    • Marked as answer by Juniormint10 Friday, August 23, 2019 12:25 AM
    Friday, August 23, 2019 12:25 AM

All replies

  • Look for similar column names?  Hope the tables are named accurately and that helps?

    The only diagrams SSMS would ever show are the relationships that someone has set up SQL Server to *enforce*, this is usually far fewer than the logical relationships one might wish to query.  That's the whole point of a relational database, you can do ad-hoc stuff that nobody thought to set up earlier.

    Now, if the database was actually designed by a professional they should have a logical diagram someplace that would be very helpful to you.  But SQL Server has no feature to attach that in a way that helps users to find it.

    Josh

    ps - though you would expect at least a few foreign keys to primary keys would be enforced. BTW, what versions of SSMS and server, are you running?  Or if not SSMS what tool are you using?


    Monday, August 19, 2019 3:51 PM
  • I use sys.dm_exec_query_stats, join it to the plan and parse the plans.  Also I use missing indexes, start sys.dm_db_missing_index_groups, work your way through it.  Logic is you will find the predicates and between the two you will learn a whole lot about how the data model works. 
    • Marked as answer by Juniormint10 Friday, August 23, 2019 12:25 AM
    Monday, August 19, 2019 9:10 PM
  • Hi, Josh -

    Thank you for your response!  I am running SSMS 2012 developer.

    Ha, Ha - I've been in this business for 20+ years and have found most shops do not have diagrams - anywhere!  That includes a lot of ERP systems.  

    Each time Microsoft comes out with a new version, I keep expecting them to have something that shows possible relationships, according to name and datatype at least.  Seems like such a logical tool to include . . .

    Juniormint 


    • Edited by Juniormint10 Friday, August 23, 2019 12:23 AM
    • Marked as answer by Juniormint10 Friday, August 23, 2019 12:25 AM
    • Unmarked as answer by Juniormint10 Friday, August 23, 2019 12:25 AM
    Friday, August 23, 2019 12:22 AM
  • Thanks, Mighty-O.  I've give it a shot!
    • Marked as answer by Juniormint10 Friday, August 23, 2019 12:25 AM
    Friday, August 23, 2019 12:25 AM