- For the unused stored procedures you can make use of dmv's especially sys.dm_exec_procedure_stat.
To find unused stored procs, please see the answer as explained by Aaron Bertrand.
Also, there is an good read to identify as explained here in the article
How to find unused Stored Procedures in SQL Server 2005/2008
- To tack down unused views:
a) Generate a List of non-recently-used code (by querying the plan cache).
b) Script the Views/Sprocs/etc that aren’t in cache (for whatever reason) using ALTER scripts. (i.e. the SSMS ‘Modify’ option when right-clicking on an object).
c) Execute the ALTER script generated by SSMS in production. Since the object wasn’t in cache, there’s no harm and no foul in worrying about recompilation overhead. And since the script is an ALTER (not a DROP/CREATE), permissions are retained intact.
d) But, if the object references invalid objects, it’ll throw an error and I can definitively know that it’s not only NOT used, but one of the culprits that’s causing me grief during replication snapshot procedures.
Also read as mentioned by Jonnathan@ here
Unfortunately if you want to be 100% accurate, the plan cache isn't
going to cut it, because there are all types of scenarios in SQL
Server where a plan may not get cached at all. For example,
OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and
single use plan stubs, etc. Since you want a method that works across
2000-2012 your only real option to guarantee you catch the usage is to
use a server-side trace with the Audit Database Object Access event: