I work in a healthcare data department of about 20 analysts and we're finding it difficult to keep track of reports and how they connect to the SQL Server procedures and tables. We pull the data, automating it with stored procedures, and then pull the tables into SSRS to report off of. The problem is linking a report back to the table it creates and then finding that tables stored procedure and job.
We don't use foreign or primary keys when building tables, looking through sp__procedures/tables/objects will give me dependencies but often dependencies are not built in to our tables. When a report breaks, our front end users will receive an error saying "DataSet XXX has an error" and they email us the dataset and report, which then takes every analyst going through their SSRS reports to see if they contain that dataset, finding the table, and then looking through their procedures to find which one creates that table.
This has been going on for some time in our department and the amount of time lost is non-trivial. Is there something out there that can relate a report or dataset back to the SQL server procedure that creates it or table it references?