Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB.net - Find SQL Table relationships

Posted on 2013-12-09
3
Medium Priority
?
290 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
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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

581 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