May 12, 2014 at 1:07 am
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
May 12, 2014 at 3:19 am
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
🙂
May 12, 2014 at 3:20 am
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
May 12, 2014 at 3:57 am
Yes,
I want to use JOINS, but this relationship is to be generated automatically on selection of the tables by the user.
May 12, 2014 at 4:06 am
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
🙂
May 12, 2014 at 4:16 am
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?
😎
May 12, 2014 at 4:19 am
Hi,
I want to use the existing relationship to be populated in a text box in vb.net
Thanks
Madhukar
May 12, 2014 at 4:34 am
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]
😎
May 12, 2014 at 4:46 am
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
May 12, 2014 at 4:47 am
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
May 12, 2014 at 4:53 am
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.
😎
May 12, 2014 at 5:15 am
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