Another suggestion from me would be to consider creating foreign key / unique / check constraints if they don't already exist. I initially do this in a local copy of the DB, as I find that it can really help when developing an understanding of the database and testing your assumptions about what links to what. You may even find a heap of orphaned data in the process (or that you have misunderstood something - it can at least be a discussion starter with the business). You mention the built in diagramming tools - these are much more useful if you have all the foreign keys in place. It can also assist in locating 'obsolete' objects - although I would agree with everyone advising caution on this.

After you have created a dev/test environment, creating any missing constraints as part of a bug fix release may help to discover bugs that would otherwise be difficult to find.

It would also be a good idea to review indexes / statistics in the database to make sure that they are appropriate (duplicate/ unused/ missing etc). I like to try and rename indexes and constraints to a consistent naming standard - this can be helpful if you get SQL exceptions while testing, and can help to identify duplicate indexes etc. You should obviously check for things like index hints in stored procs and views etc before you do this.

I also try to run the sp_refreshsqlmodule (http://msdn.microsoft.com/en-us/library/bb326754.aspx) on all the stored procs, UDFs, views, triggers etc. in the database. In older versions of SQL server (SQL 2005) this will update the dependency information in sys.sql_dependencies. Even in the current version it will pick up if stored procs refer to columns that don't exist in existing tables (except if in dynamic SQL). It can also make sure that any user defined types used as parameters are referencing the correct types.

Another useful check is for dynamic sql using EXEC - can you modify to avoid SQL injection...