In case you didn’t know, there are a couple of very cool SQL stored process that allow you to execute a T-SQL statement for each table and for each database. They are sp_MSforeachdb and sp_MSforeachtable.
For example, executing
sp_msforeachtable ‘sp_spaceused “?”‘
will result in the dump information on space occupied by each table.
EXEC sp_MSforeachdb @command1=”print ‘?’ DBCC CHECKDB (‘?’)”
Will check for allocation and consistency errors
For more information on these two stored procs, and on more advanced examples, including pre- and post-execution commands, check out http://www.databasejournal.com/features/mssql/article.php/3441031.