We are migrating a fair number of our organization's reports out of a depreciated reporting tool and re-creating them in SSRS and some of the rest of the Microsoft BI stack.
I am looking for a good way to track SQL objects that are created for the purpose of being re-used by report devs (views, INTVFs, things that return active employees, or revenues by department, by year, etc, etc...).
Ideally, this tool would list all those objects and then contain information about how they are sourced/pulling data from as well as comments that are useful.
Then if a dev was working on a report they could search this metadata to see if what they need was already created and they could just consume/join against that.
Also, it would be useful to do a "reverse" check and look at an OLTP field to see if any of these reporting objects had already been created against that specific field.
I was thinking about ERwin for a while, as it seemed to do things similar to this.
However, an SQL dependency tracker or documentation tool might work just as well, provided all our SQL reporting objects are usefully documented using the description fields, etc...
Has anyone else already done this?
Any suggestions, wisdom, lessons learned, etc?