February 27, 2011 at 7:55 am
Hi suppose i have the following 3 tables
table A:
RollNo(PK) | Cell | Address
table B:
RollNo | Subject_Code (P.K) | Marks
table C:
Subject_Code|Level|Teacher Name
Now table A and Tabe C are realted to each other via Table C (the Pk of table b acts as a fk on table C)
Now is there any way by which programatically I could find out that table A is related to tablec via table B
inshore A->B->C ??/
February 27, 2011 at 1:00 pm
Yes, there are a couple of ways although none of them are guaranteed to be correct because of the "human element" during table design.
You could look for duplicate column names in sys.columns and make the terrible assumption that identically named columns mean the same thing. To keep from picking up common columns like "LastModifiedDate" as joined columns, you could only include columns that were a part of a PK or AK in some table somewhere. Of course, that all goes to hell in a handbasket if every PK column is called just "ID". 😉
If FK's are present, you could simply interpret those to give you the relationships. That would be more accurate than the first method because the presence of an FK implies certain rules that humans must follow in order to be able to create the FK's in the first place. After all, FK's are meant to enforce such relationships.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply