Generating Auto Relationship

  • Hi Team,

    I would like to create a auto relationship between tables.

    Currently i am using Northwind DB with tables (Orders, OrderDetails, Customers)

    Orders ( OrderId, Customerid)

    OrderDetails(OrderId)

    Customers(CustomerID)

    Now, if the user wants to generate a relationship automatically based on SAME FIELD Names.

    What is the approach? Any kind of query or methodology

    THanks

    Madhukar

  • What do you mean from auto relationship ? Please correct if you want to link all the tables.If so you can do it using join.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • How do you mean the user is going to generate things automatically? Do you mean when writing T-SQL it will automatically figure out the relationships? Nothing does that automatically within SSMS. You can get third party tools that will do it. For example, Red Gate SQL Prompt will provide you with JOIN criteria based on common column names.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Yes,

    I want to use JOINS, but this relationship is to be generated automatically on selection of the tables by the user.

  • madhukars 32911 (5/12/2014)


    Yes,

    I want to use JOINS, but this relationship is to be generated automatically on selection of the tables by the user.

    I don't think in MS SQL this is possible that user can select tables and it will show you the suggested joins.

    May be as advised by Grant some third party tool can help you.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • madhukars 32911 (5/12/2014)


    Yes,

    I want to use JOINS, but this relationship is to be generated automatically on selection of the tables by the user.

    I don't think it is a good idea to use column names for this. If lets say there is a timpestamp column with the same name in the tables, nothing will be returned as those conditions will never be satisfied.

    On the other hand, what about using the existing relationship?

    😎

  • Hi,

    I want to use the existing relationship to be populated in a text box in vb.net

    Thanks

    Madhukar

  • madhukars 32911 (5/12/2014)


    Hi,

    I want to use the existing relationship to be populated in a text box in vb.net

    Thanks

    Madhukar

    Start by looking at these views in the database

    [font="Courier New"]INFORMATION_SCHEMA.TABLE_CONSTRAINTS

    INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS

    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

    INFORMATION_SCHEMA.CHECK_CONSTRAINTS

    INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE[/font]

    😎

  • madhukars 32911 (5/12/2014)


    Yes,

    I want to use JOINS, but this relationship is to be generated automatically on selection of the tables by the user.

    Right. No way to do this automatically through SQL Server Management Studio or Visual Studio. Not without 3rd party help.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • madhukars 32911 (5/12/2014)


    Hi,

    I want to use the existing relationship to be populated in a text box in vb.net

    Thanks

    Madhukar

    That's different. You can look at the foreign key constraints on a table to see the columns that's using and you can display those through your code. The system views shown above are a good solution for that.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (5/12/2014)


    madhukars 32911 (5/12/2014)


    Yes,

    I want to use JOINS, but this relationship is to be generated automatically on selection of the tables by the user.

    Right. No way to do this automatically through SQL Server Management Studio or Visual Studio. Not without 3rd party help.

    Figured there was a slight misunderstanding going on. Don't think that even the smartest of 3rd party tools can offer more than an intelligent suggestion, definitely not generate the relationship with 100% certainty.

    😎

  • Eirikur Eiriksson (5/12/2014)


    Grant Fritchey (5/12/2014)


    madhukars 32911 (5/12/2014)


    Yes,

    I want to use JOINS, but this relationship is to be generated automatically on selection of the tables by the user.

    Right. No way to do this automatically through SQL Server Management Studio or Visual Studio. Not without 3rd party help.

    Figured there was a slight misunderstanding going on. Don't think that even the smartest of 3rd party tools can offer more than an intelligent suggestion, definitely not generate the relationship with 100% certainty.

    😎

    Oh heck no.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 12 posts - 1 through 11 (of 11 total)

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