SQL Server script to find table dependencies
-
If you are fanatic about normalization, I am sure you know the pains associated with it. Deletion of a row at a top level table can become a project very quickly due the child tables dependencies, which can run several levels deep. I have a script to do that. Please feel free to download and share.
-
If you are fanatic about normalization, I am sure you know the pains associated with it. Deletion of a row at a top level table can become a project very quickly due the child tables dependencies, which can run several levels deep. I have a script to do that. Please feel free to download and share.
If it's so good and useful, please write an article. And if you run into that problem often I recommend using referential integrity.
-
If you are fanatic about normalization, I am sure you know the pains associated with it. Deletion of a row at a top level table can become a project very quickly due the child tables dependencies, which can run several levels deep. I have a script to do that. Please feel free to download and share.
-
If it's so good and useful, please write an article. And if you run into that problem often I recommend using referential integrity.
I was going to curse you out but then saw that you are a legend at The Code Project, so I won't. > Write an article: Yes the link is kinda article and the script itself. > I recommend using referential integrity I hope you are not confusing "on delete cascade" feature in the context of referential integrity. Referential Integrity: NICE On Delete Cascade: BAD (VERY BAD actually)
-
I was going to curse you out but then saw that you are a legend at The Code Project, so I won't. > Write an article: Yes the link is kinda article and the script itself. > I recommend using referential integrity I hope you are not confusing "on delete cascade" feature in the context of referential integrity. Referential Integrity: NICE On Delete Cascade: BAD (VERY BAD actually)
jujiro wrote:
"on delete cascade"
No, I use that very seldom and only when the data is transient anyway.
-
jujiro wrote:
Deletion of a row at a top level table can become a project very quickly due the child tables dependencies, which can run several levels deep.
"Cascading delete". Most modern databases do.
Bastard Programmer from Hell :suss:
Yeah and most experienced devs DON'T use it. I do not recall ever using cascade delete. I do recall chastisiing a junior dev for trashing the database using cascading delete. Oops just does not cut it!
Never underestimate the power of human stupidity RAH
-
If you are fanatic about normalization, I am sure you know the pains associated with it. Deletion of a row at a top level table can become a project very quickly due the child tables dependencies, which can run several levels deep. I have a script to do that. Please feel free to download and share.
If an article is too much then a tip/trick requires less commitment. If you can't keep track of your data structure during development then there is something lacking in your methods. Referential integrity via FKs usually meets most of the requirements!
Never underestimate the power of human stupidity RAH
-
Yeah and most experienced devs DON'T use it. I do not recall ever using cascade delete. I do recall chastisiing a junior dev for trashing the database using cascading delete. Oops just does not cut it!
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
Yeah and most experienced devs DON'T use it.
They rather write queries to check whether there are child-rows, and delete them manually. I understand that people don't use what bites them - about the same brilliant idea as keeping every field a
VARCHAR. It's standard functionality of the database, and it's less error-prone than coding it manually. I bet you're glad that I'm not allowed near your systems? :-DBastard Programmer from Hell :suss:
-
If an article is too much then a tip/trick requires less commitment. If you can't keep track of your data structure during development then there is something lacking in your methods. Referential integrity via FKs usually meets most of the requirements!
Never underestimate the power of human stupidity RAH
>> If an article is too much then a tip/trick requires less commitment. I don't understand what you mean by that. Anyway, the reason why I developed the script is to see how deep the rabbit hole is. Yes, I can use any reverse engineering ERD tool to see that (Visio is my favorite.) If you have been around as long as I have and have worked on projects where no one really knew the entire system/database, you have to rely on quick and dirty tools like my script to find your way. Visio may or may not be there at your disposal. I really do not understand why people are making smart ass comments about how they do things. Col. Jessep (A few good men) said, "I have neither the time nor the inclination to explain myself to a man who rises and sleeps under the blanket of the very freedom I provide, then questions the manner in which I provide it! I'd rather you just said thank you and went on your way." :)
-
I was going to curse you out but then saw that you are a legend at The Code Project, so I won't. > Write an article: Yes the link is kinda article and the script itself. > I recommend using referential integrity I hope you are not confusing "on delete cascade" feature in the context of referential integrity. Referential Integrity: NICE On Delete Cascade: BAD (VERY BAD actually)
jujiro wrote:
On Delete Cascade: BAD (VERY BAD actually)
I use DELETE and DELETE CASCADE where the model requires me to do so. I find it good practice to limit the number of RI keys I'll define on a table so that I don't get into messes where all for the sake of normalization, someone has defined a row with a couple of dozen RI keys in it. To me that's just asking for a coding nightmare. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
>> If an article is too much then a tip/trick requires less commitment. I don't understand what you mean by that. Anyway, the reason why I developed the script is to see how deep the rabbit hole is. Yes, I can use any reverse engineering ERD tool to see that (Visio is my favorite.) If you have been around as long as I have and have worked on projects where no one really knew the entire system/database, you have to rely on quick and dirty tools like my script to find your way. Visio may or may not be there at your disposal. I really do not understand why people are making smart ass comments about how they do things. Col. Jessep (A few good men) said, "I have neither the time nor the inclination to explain myself to a man who rises and sleeps under the blanket of the very freedom I provide, then questions the manner in which I provide it! I'd rather you just said thank you and went on your way." :)
jujiro wrote:
you have to rely on quick and dirty tools like my script to find your way.
This shows there is something wrong with the environment, if you are going in having to build cascading deletes with no reference to the data structure then it is wrong. Not that we have not all had to do it but in a reasonable application you should not have to. I did not even look at your script/solution, my point was that the way you are approaching it is wrong, this thread will dissapear into the pits fairly rapidly, writing an article will make your contribution more useful, but even a tip/trick will have a more lasting usefulness than a link in a forum post.
Never underestimate the power of human stupidity RAH
-
Mycroft Holmes wrote:
Yeah and most experienced devs DON'T use it.
They rather write queries to check whether there are child-rows, and delete them manually. I understand that people don't use what bites them - about the same brilliant idea as keeping every field a
VARCHAR. It's standard functionality of the database, and it's less error-prone than coding it manually. I bet you're glad that I'm not allowed near your systems? :-DBastard Programmer from Hell :suss:
Eddy Vluggen wrote:
I understand that people don't use what bites them
You may well be right there, the idea of cascading deletes just makes my toes curl.
Eddy Vluggen wrote:
I bet you're glad that I'm not allowed near your systems
You are most certainly right there, I could see endless discussions where each tries to justify their OPOV - fruitlessly.
Never underestimate the power of human stupidity RAH
-
jujiro wrote:
you have to rely on quick and dirty tools like my script to find your way.
This shows there is something wrong with the environment, if you are going in having to build cascading deletes with no reference to the data structure then it is wrong. Not that we have not all had to do it but in a reasonable application you should not have to. I did not even look at your script/solution, my point was that the way you are approaching it is wrong, this thread will dissapear into the pits fairly rapidly, writing an article will make your contribution more useful, but even a tip/trick will have a more lasting usefulness than a link in a forum post.
Never underestimate the power of human stupidity RAH
My point in the message to you was that people started writing comments without even clicking on the link. You click on the link you get some more information, may be not a whole lot of explanation, but information it is. Now, about my approach: There could be myriad of reasons why you would want to know the relational linkages. May be you, you were doing a data cleanup, which meant deleting the parent rows, and keeping the child rows by making the foreign key column null, or may even delete cascading, if that is what was needed as a part of the cleanup process. So, unless you know the context, you can say an emphatic "wrongful approach." Do you get my point?
-
My point in the message to you was that people started writing comments without even clicking on the link. You click on the link you get some more information, may be not a whole lot of explanation, but information it is. Now, about my approach: There could be myriad of reasons why you would want to know the relational linkages. May be you, you were doing a data cleanup, which meant deleting the parent rows, and keeping the child rows by making the foreign key column null, or may even delete cascading, if that is what was needed as a part of the cleanup process. So, unless you know the context, you can say an emphatic "wrongful approach." Do you get my point?
jujiro wrote:
So, unless you know the context, you can say an emphatic "wrongful approach."
No no no not the approach you took with the code, I never even looked at it, the wrong way to PRESENT it here. There are always requirements for hacks and workarounds, I was not dumping on your code, just the way you presented it!
Never underestimate the power of human stupidity RAH