CheckDB
We all know that corruption within a SQL Server database can happen (although, in my experience usually becuase of an issue with the underlying disk system), and the built in CheckDB database console command is usually very good at interrogating the database to find it.
I know that some people schedule this to run at regular intervals, probbably through a maintenance plan, however my personal preference is to run it on demand where I can keep control of what is happenening – especially if it is to be run across multilple databases. This process is resource intensive, so is obviously best run when your databases are at their least active.
It is possible to run the command through a cursor or the undocumented sp_MSforeachdb command, however I like to generate the commands and then run them in batches. I appreciate that this is a manual process, and may not be possible if your only window of opportunity is at 02:30am, however as stated before this is my personal preference.
To generate the commands, I use the following script. Note, that I use the NO_INFOMSGS option so that no output is generated unless an issue is found.
select 'DBCC Checkdb ([' + name + ']) WITH NO_INFOMSGS' from master..sysdatabases where name <> 'tempdb'
