BWFC (8/27/2015)
Eirikur Eiriksson (8/27/2015)
Thank you for this interesting question Hugo.😎
Slightly taken aback seeing the overall results though
Correct answers:6%Incorrect answers:94%
I suspect that's got something to do with how people have researched this. If you don't specifically ensure you're checking the 2012 documentation it's very easy to come away with the wrong answer. A very cunning question Hugo; it's been a double learning exercise, thank you. I also have to say there are absolutely no tricks here, I got it wrong because I didn't check something I should have.
Actually it's very easy to come up with the wrong answer if you look at 2012 documentation - since the right answer is listed in 2012 BOL as a "Catalog View" (specifically an Object Catalog View) and there is indeed no mechanism for getting the information using anything listed as a Dynamic Management View.
So if we believe BOL the correct answer is Option 1, "None, this information is not exposed through DMVs" and Option 4 is wrong because that system view is NOT a DMV. (The BOL classification of system views can be seen at System Views (Transact-SQL).)
So SQL 2012 BOL clearly says that this can't be done with a DMV. Anyone who tried to look this up in SQL 2012 BOL was guaranteed to get a "wrong" answer. People would be far more likely (1 chance in 8 instead of no chance at all) to pick an answer at random rather than look in 2012 BOL.
But I don't really complain about the question - if I'd known the answer I wouldn't have had to look it up, and if the question had been consistent with BOL I wouldn't have noticed that "DMV" is yet another ill-defined SQL Server term.
edit (2015/09/08): Steve has corrected the question to say "system view" insteaad of "DMV".
Tom