Solved

VB.net - Find SQL Table relationships

Posted on 2013-12-09
3
280 Views
Last Modified: 2013-12-11
Hi

What VB.net code would I use to find the table relationships in a SQL database?
0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39706227
Are you planning on doing this with SQLDMO, or via a straight-forward query?
0
 
LVL 15

Accepted Solution

by:
unknown_routine earned 500 total points
ID: 39706242
You need to run the following query:

Select
	object_name(rkeyid) Parent_Table,
	object_name(fkeyid) Child_Table,
	object_name(constid) FKey_Name,
	a1.name FKey_Col,
	a2.name Ref_KeyCol
From
	sys.sysforeignkeys s
	Inner join sys.syscolumns a1
		on ( s.fkeyid = a1.id And s.fkey = a1.colid )
	Inner join syscolumns a2
		on ( s.rkeyid = a2.id And s.rkey = a2.colid )
Order by Parent_Table,Child_Table

Open in new window



So:

Step 1 : Create an SQL connection and open it

Step 2: Add the above query to a string variabe:

Dim mycommandas string ="object_name(rkeyid) Parent_Table,"
mycommandas =mycommandas +"object_name(fkeyid) Child_Table,"
........
.......
mycommandas =mycommandas +"Order by Parent_Table,Child_Table"


Step 3: Create a dataAdapter object and use it to fill a dataset with the results of the above command.
0
 

Author Closing Comment

by:Murray Brown
ID: 39711081
Thanks very much
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question