I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now have a list of recommendations for indexes that need creating.
Edit: The Indexes described above take information from the DMV's that tell you what the database engine would use for indexes if they were available and the scripts take the Top x recommendations (by seeks, user impact etc.) and put these in a table.
(Edit above partially taken from Larry Coleman's answer below in order to clarify what the scripts are doing)
As I am new to database admin, and having had a quick search around the net, I am reluctant to take the plunge and blindly add the recommended indexes. However, not being experienced in  the field, I am looking for some advice on how to determine whether the recommendations are necessary or not.
Do I need to run the SQL Profiler, or is it better to examine the code that queries the tables? And do you have any other advice?

I use Jason Strate's index analysis scripts. They tell you how much your existing indexes are used as well as how much missing indexes would have been used. I typically don't add indexes unless they make up more than 5 or 10% of the queries on a table.
Most importantly, though, it's about making sure the application responds fast enough for the users.
Jason Strate's index analysis blog articles)
These days, I use sp_BlitzIndex® when performing index analysis.
