This next year, I am helping an effort to clean several SQL Server environments.
We have about 10,000 stored procedures and estimate that only about 1000 of them are used on a regular basis, and another 200 or so are used on a rare occasion, meaning we have a lot of work to do.
Since we have multiple departments and teams that can access these databases and procedures, we are not always the ones calling the procedures, meaning that we must determine what procedures are being called. On top of that, we want to determine this over a few months, not in a few days (which eliminates some possibilities).
One approach to this is to use the
SQL Server Profiler and track what procedures are being called and compare them to the list of what procedures we have, while marking whether the procedures are used or not. From then, we could move the procedures to a different schema in case a department comes screaming.
Is using the
Profiler the most effective approach here? And/Or have any of you done something similar and found another way/better way to do this?