One of my pet peeves is when somebody will read a question and either they don't understand it, or they don't know the answer -- does that stop them? Nope - they just reply to a different question that they do know the answer to. Human nature, I guess. End of rant.
If you re-read Leo's original article, he said:
"In my company the task becomes a little bit easier by knowing the fact that any database can be accessed only via call to a stored procedure. E.g. I have to split the task to the 2 sequential subtasks: ..."
Lucky Leo. In his case, he could very easily add some kind of logging feature that records when an sp is run, and then (over the course of time) use a process of elimination to find the sp's that never get called.
Considering that SQL Server is notorious for omitting relevant object info such as "last modified" and "last accessed" dates (which Windows itself has had for ages) we're just a little bit stuck. Even perusing the SQL-DMO library does not reveal anything that I can find relating to the topic at hand. It really gets tiring when you have to drop-and-create procedures to track when changes happen.
The issue becomes even more complex when you consider that an external application can be allowed to touch objects directly, so even the internal SQL tracking mechanisms such as dependencies don't help a bit. I can think of one customer's web app that uses SQL Server, however there is also an Access database that connects in there, along with Excel gurus who use the Data tools to run their own specialized analyses. And let's not forget an e-commerce link to both their physical and online stores.