PK-FK Relations

[Andrzejek]

I have this huge data base (1669 Tables) that I am trying to 'analyze' and one of the 'checks' is: how many PK-FK Relations do I have between the tables?

From this site I've got some good SQL's to list tables with FK and related tables with PK. I have (only) 157 records. Great!

Something like:[tt]
SELECT OBJECT_NAME(fkeys.constraint_object_id) foreign_key_name
,OBJECT_NAME(fkeys.parent_object_id) referencing_table_name
,COL_NAME(fkeys.parent_object_id, fkeys.parent_column_id) referencing_column_name
,OBJECT_NAME (fkeys.referenced_object_id) referenced_table_name
,COL_NAME(fkeys.referenced_object_id, fkeys.referenced_column_id)
referenced_column_name
FROM sys.foreign_key_columns AS fkeys
order by 2[/tt]

So, I stared to drag 2 tables at the time to [blue][tt]Views - New view...[/tt][/blue] in MS Management Studio just to verify the PK-FK relations, easy to see the 'automagic' connection between tables this way. Some of them showed 'connection' between the tables just like I expected. But there are some pairs that even though are listed with PK-FK relation, they are not 'connected' at all. :-(

Creating ER Diagram is out of the question - too many tables, Management Studio crashes.

How can I get the list of tables that do have actual PK-FK relation established?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

To me that raises the question which is more reliable.

I'd say sys.foreign_key_columns. I'D look into the constraints and how they are defined, they may be complex and therefore not recignized as 1:1 or 1:n relation in the view designer?!


Chriss

 

[Andrzejek]

Good point, I may check it out.

Although I don't think I really need to - diving deeper into PK-FK relations in this data base I've discovered: out of 157 pairs of tables, 111 tables with PK field have no records (0 records)!


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

Something generated? Maybe by some .net framework?

I would have said setup a Visual Studio project to reverse engineer the database and get all table/index/constraints, but it seems aside from the fact you can't have a foreign key pointing to an empty table, the constraints could still already point out the structural relationships before there is data.

Chriss

 

[Andrzejek]

All of this may be above my paygrade.
This DB is from an app developed by Consultants. When my co-worker looked at it, he sent us a note: "If you have a weak stomach you should not look." I should just follow his advice.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

Well,

it's something I saw used in systems that are surely enterprise: Databases are created by frameworks, not designed by devlopers. The data persistance framework used works fine with the databases, but they don't have anything in common with what you'd manually design. Empty tables are a bit of bad smell of that. But to give a good judgement of the data structures you'd also need hands on source code.

Chriss

 

[Andrzejek]

hands on source code" - we will never get the source code.
It is a top secret, or how they call it: a 'proprietary information'

Over last few years, based on users' experience of very slow responses of the app, inability to create certain reports, and a whole other 'hints' I am afraid it is not a 'relational' nor 'normalized' DB. But mostly I worry about data integrity. But looks to me I am the only one.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

I was never in the position of a DBA confronted with such databases. I was on the side of "consultants". Well, a small ISV that programmed for a big company, and we did provide sources to them, too. But that can differ, obviously, if it's not custom programmed software but the customization of a larger system like SAP is.

And, well, while I never was in your position I was at the transition from our software to such a system and I know such stories from their DBAs. SAP is a bad example in that as far as I know it has one of the most precise normalized database designs I can think of with initially Informix backends, then for the longest time DB2 and now SAPs own Hana, but does also support other RDBMS as backends, like Oracle. Such systems are often customized for companies by an army of SAP consultants.

Well, no matter if it is something like that or another product your consultants develop, a DBA job is becoming alsmot pointless, as the RDBMS you administer are merely the data persistence layer and things like data integrity are in the business layer outside of the RDBMS, which indeed poses a risk of you becoming disabled to care for the data integrity, to say the least.

Chriss

 

[gmmastros]

May I ask... what is the point of analyzing this database? Is the ultimate goal to improve performance?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

gmmastros,
This is the DB established by Consultants and used by the app written by them for our use.
Improve performance is one of the goals, but also: lack of PK-FK relations (data integrity issue?), having the same data in many, many, many places (if need to update, how do you even find ALL locations of that data?), and whole lot of other problems.
I have been asked to 'analyze' the DB before we will approach Consultants with those issues. Thankfully others (including a DBA) are also involved in this process so I am not alone.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

gmmastros,

generally, you want your database to be normalized in 4NF, at least 3NF. Finding out the database structure lacks a certain structure is like seeing construction defects on a house.

Chriss