tag:blogger.com,1999:blog-4743067396800539807.post2592020761788744482..comments2021-02-24T10:48:15.755-05:00Comments on Mark on Databases, etc.: Finding Foreign Keys without IndexesMark Freeman (@m60freeman)http://www.blogger.com/profile/11475488917130342878noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-4743067396800539807.post-4006288481523389892021-02-24T10:48:15.755-05:002021-02-24T10:48:15.755-05:00One useful enhancement I want to make to this scri...One useful enhancement I want to make to this script at some point would be for it to ignore key column order so it would recognize an index on Column2, Column1 as a match for a foreign key on Column1, Column2, while not being fooled by an index on Column2, Column3, Column1. <br /><br />I found that circumstance (with just the two columns in a different order) in a production database recently. The inconsistency was insufficient justification to make a change because everything was functioning as intended. But it bothers me that my script keeps reporting it as a problem.Mark Freeman (@m60freeman)https://www.blogger.com/profile/11475488917130342878noreply@blogger.comtag:blogger.com,1999:blog-4743067396800539807.post-70289964529154029662015-09-02T10:37:22.428-04:002015-09-02T10:37:22.428-04:00Hi Mark
i saved a heck of a time in using your sc...Hi Mark<br /><br />i saved a heck of a time in using your script, your a genius - hope to be like you one day ! <br />all the best and thanks very much.<br /><br />Ali. alihttps://www.blogger.com/profile/09133861568471647653noreply@blogger.comtag:blogger.com,1999:blog-4743067396800539807.post-83169839501844996042011-11-23T01:00:23.298-05:002011-11-23T01:00:23.298-05:00Michael,
I updated the script in this post to add...Michael,<br /><br />I updated the script in this post to address the issue that you raised as well as others I noticed myself. Thanks for your feedback!Mark Freeman (@m60freeman)https://www.blogger.com/profile/11475488917130342878noreply@blogger.comtag:blogger.com,1999:blog-4743067396800539807.post-58126878570542976402011-09-02T14:08:57.853-04:002011-09-02T14:08:57.853-04:00This is brilliant! And I hope to hear more from yo...This is brilliant! And I hope to hear more from you. <br /><br />I don't know if you remember me, I was sitting in Aaron Bertrand's "What's New In Denali" at SQLSat60 and I noticed you were too. I regret not introducing myself then.<br /><br />But any way, I noticed one case that isn't quite covered: The case where a foreign key column is indexed, but not indexed in a way that can be used. For example:<br /><br />use tempdb<br />CREATE TABLE A (a int primary key);<br />CREATE TABLE B (b int not null, a int not null references A(a), primary key (b,a));<br /><br />The column B(a) is part of an index, but not one that's useful when deleting from A. <br /><br />It would be good if your script picked up this case as well! If you can solve that, this script would definitely go into my arsenal.<br /><br />Cheers!Michael J. Swarthttps://www.blogger.com/profile/05408240220683534698noreply@blogger.com