×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Realtionship finder

Realtionship finder

Realtionship finder

(OP)
Is there any software utility that can help in relationship finding between tables?

RE: Realtionship finder

Any particular DB?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

RE: Realtionship finder

There are many built in views which you should be able to use, for example:

CODE

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

RE: Realtionship finder

RiverGuy,
Are those SQLServer table queries?  I think Harley was asking for clarification due to the fact that you need different queries to get that information from Access or MySQL or Paradox...

Leslie

In an open world there's no need for windows and gates
www.ubuntu.com

RE: Realtionship finder

That's exactly what I meant Leslie.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

RE: Realtionship finder

Leslie,
You may be right.  These all work on SQL Server.  #2 and #3 I don't see a reference to in the MySQL documentation, although I haven't tried it to see if the syntax works.  #1 and #4 are in the MySQL documentation.  Not sure about Access/DB2 etc.  It's hard to keep up with what is in the ANSI specs and what is proprietary vs. which vendors have implemented the full spec.   

My advice would be to try some of the INFORMATION_SCHEMA views in all the database versions you are going to be using and see if you can get a common ground.

RE: Realtionship finder

(OP)
The last 3 commands bring me results from the database!

RE: Realtionship finder

Hi skiabox,

Quote:

The last 3 commands bring me results from the database!

Then I assume you run SQL Server. Does the first query error or does it give you an empty result?

If the result is empty there are no relations defined between the tables, you would need to define them, eg visually in a database diagram within the SQL Server Management Studio.

Bye, Olaf.

RE: Realtionship finder

(OP)
The 1st command does not bring any results.
It does not return an error.
Thanks for helping!

RE: Realtionship finder

Hi skiabox,

You may reverse engineer the database. Depending on the database design references should be made between tables having a field name in common. If that's not the case and there is no other way to automatically decide which references should exist, you will have no other choice as defining relations manually. How much tables does the database have?

Bye, Olaf.

RE: Realtionship finder

This software:
http://fabforce.net/dbdesigner4/

will have a pretty good go at producing a database schema.

Without referential integrity set though (proved through absence of results in the first query) it won't be able to show how the data in the tables relate to each other.

You can often work this out yourself by looking at samples of data and looking at client applications to see how they work, or code for views, stored procedures, SQL code etc that is used.
Takes a bit of time, but once it works you get a better idea of how it all fits together.

John

RE: Realtionship finder

(OP)
The database has around 100 tables!

RE: Realtionship finder

So, it will take time - I've got far bigger DBs than that which were undocumented when I started.

I've used SQL code in queries to start figuring out how the data fits together, run the application with SQL Profiler running so I can see what the front end application is doing at the back end and then re run individual select statements to look at the data and see how it relates to each other - and in the front end application.

Its a long job, but the end result will be worth the effort.

John

RE: Realtionship finder

Hi John,

using the SQL Profiler is a nice idea. this will show you joins so you can see how tables are related. Another helpful thing would be application source code.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close