LOOP scripts for all the tables - EXEC sp_MSforeachtable

sp_MSforeachtable can be used to loop through all the tables in your databases. Here are some common usages of this useful stored procedure.

Example: Getting Row Count for all the tables

Like wise we can do lot more such as remove contains and space usage, please refer the following link for more usage.
 http://www.sqlservercurry.com/2009/04/8-common-uses-of-undocumented-stored.html

Cheers!