I see scripts all the time on how to reindex all the tables in a db but they are usually too generic and may cause issues. For instance they typically cause the log file to grow very large and may even fill the hard drive. This is intended to be run off hours when db activity is low or nonexistant. I usually discourage accessing system tables directly but seeing as this is a maint script it should not pose a problem. The user may choose to do a full backup before and or after if the db is not already in simple recovery mode.
Indexes, Indexes, Indexes
This article has a description of an index utilization tool that Ron Johnson uses to analyze the effectiveness and use of indexes in a system.
2012-07-04 (first published: 2011-07-05)