Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Realtionship finder
- Thread starter skiabox
- Start date
HarleyQuinn
Programmer
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 before posting.
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 before posting.
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_CONSTRAINTSRiverGuy,
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
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
HarleyQuinn
Programmer
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 before posting.
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 before posting.
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.
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.
- Thread starter
- #7
Olaf Doschke
Programmer
Hi skiabox,
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.
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.
- Thread starter
- #9
Olaf Doschke
Programmer
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.
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.
This software:
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
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
- Thread starter
- #12
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
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
Olaf Doschke
Programmer
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.
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.
- Status