ReReplaced - Saturday, December 30, 2017 6:26 AM
While you're going through the views, procedures, functions, etc. to find all the joins, do yourself a favor and take note of the data types of the columns involved in each predicate. If it's as bad as you make it sound, then you'll likely have columns of different data types being joined together. This implicit cast is devastating to performance, so you might as well make plans to fix the data types while you're identifying the joins.
This sounds like it's going to be a pretty serious release script. I've had to undertake efforts like this and they aren't trivial. There are very few absolutes in SQL Server, but my experience is that what Gail said is absolutely true.
HappyGeek - Saturday, December 30, 2017 9:23 AM
If you do have nonclustered indexes present (use sys.indexes), they may be of some help, but don't assume the person who created them knew what they were doing. Some may be unused, which you can determine from sys.dm_db_index_usage_stats, but it's only good since the last instance restart. You can also use sys.indexes to find heaps by using WHERE type = 0.