| Author |
Topic |
|
Jake Shelton
Yak Posting Veteran
74 Posts |
 Posted - 2012-03-02 : 10:30:27
|
| Hi all, I've inherited a database to do some reporting on, so I've been inventorising via what data is in what tables/columns, starting with the tables with greatest rowcounts, but the only copy I have has lost its FK relationships, so I have about half a dozen orphaned junction tables. There's no chance of getting another copy, nor any kind of access to the front end.Is there any way for me to discover what the FK relationships were?(Edit: this is an import from a former Access DB) |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-03-02 : 11:44:12
|
| is there any kind of naming convention to the keys?If the foreign keys and table names were all things like<FOREIGNTABLENAMEID>Then you could make an educated guess using pattern matching.example .. the sales table has a [EmployeeID] column. If there is an employee table then it's probably the fk.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Jake Shelton
Yak Posting Veteran
74 Posts |
Posted - 2012-03-02 : 15:00:50
|
quote:
Originally posted by Transact Charlie
is there any kind of naming convention to the keys?If the foreign keys and table names were all things like<FOREIGNTABLENAMEID>Then you could make an educated guess using pattern matching.example .. the sales table has a [EmployeeID] column. If there is an employee table then it's probably the fk.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
No such luck, the naming convention is about as useful a condom machine in a Nunnery. I was thinking more of doing a join between columns to see if there's a 99% match or so. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-02 : 17:07:13
|
| but that would leave you with whole lot of combinations isnt it? how many tables are involved in this?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Jake Shelton
Yak Posting Veteran
74 Posts |
Posted - 2012-03-02 : 18:35:46
|
| I owe Charlie an apology, re-examining his suggestion has narrowed this down quite a lot, of the 7 junction tables, 6 of them:AsignedUsersRead;AsignedUsersRead27;AsignedUsersRead29;AsignedUsersRead30;AsignedUsersRead31;AsignedUsersRead32. seem pretty useless, as they have only 2 columns, ID and UserID, UserID is simply '30', for each table, and the ID column merely increments by 1 every row. Doesn't look promising.However, the 7th junction table (AsignedUsers) features two columns, AsignedUsersSchedule_Id and AsignedUsersUser_Id, and I noticed a ScheduleID column in the RecSchedules table, as well as a UserID + LogID columns in the Logs table, which are the top 2 tables in Rowcount.Now, I still don't know what use I can make out of the above info, even if there was an FK relationship between these three tables. i think the next step is to do some kind of join to see if the data matches, any idea how I should write it? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-03-02 : 18:38:56
|
| you can maybe use data from the system viewsINFORMATION_SCHEMA.COLUMNSand INFORMATION_SCHEMA.TABLESCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Jake Shelton
Yak Posting Veteran
74 Posts |
Posted - 2012-03-02 : 19:26:46
|
| Thanks Charlie,Slight mystery though...If I run this statement, Select Count (*) From Logs LI get the table's rowcount, 122,622.But this... Select Count (*) From Logs L Inner join AssignedUsers A on A.AssignedUsers_UserID = L.UserID returns a count of 6847224 ! I find it hard to believe it's doing a Cartesian join...anyway it's too late in the evening to worry about it. Time for a quick drop of the good stuff. :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-02 : 19:58:50
|
| you have to see how two tables are related. it may be many to one which might be causing this!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Jake Shelton
Yak Posting Veteran
74 Posts |
Posted - 2012-03-03 : 05:48:19
|
quote:
Originally posted by visakh16
you have to see how two tables are related. it may be many to one which might be causing this!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
But as per my opening post, there are no FK relationships. As far as I know, an inner join only returns those rows that contain data common to both tables, and should not return more rows than the either of the two tables. I may have overlooked something. I've changed the join to both left and right, with the same effect (6.8million rows). The Asignedusers table only has 3000 rows, so I know it's not performing a Cartesian join. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-04 : 15:05:21
|
quote:
Originally posted by Jake Shelton
quote:
Originally posted by visakh16
you have to see how two tables are related. it may be many to one which might be causing this!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
But as per my opening post, there are no FK relationships. As far as I know, an inner join only returns those rows that contain data common to both tables, and should not return more rows than the either of the two tables. I may have overlooked something. I've changed the join to both left and right, with the same effect (6.8million rows). The Asignedusers table only has 3000 rows, so I know it's not performing a Cartesian join.
you're missing my pointeven if inner join brings only records containing common data between tables, if one table has more that one record for data you're trying to matching against, it will cause join to return resultset which will have multiple records per single value combination of first table. In that case the count will be more------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Making sense of a DB with missing FK's