Perform an action on (e.g., "drop") all tables in a SQL Server database

Supposed you want to perform an action on all tables in a SQL Server database.  (E.g., delete or truncate every table.)

You could generate a list of all the tables in the database by (first connecting to the database you want then) executing:

SELECT * FROM sys.Tables Order by name ASC

 Or you could use the hidden Microsoft stored procedure, sp_MSforeachtable.

EXEC sp_MSforeachtable @command1 = "PRINT '?'"
      --print the tables names with owner(dbo)

EXEC sp_MSforeachtable @command1 = "SELECT * FROM ?" 
      -- Selects all the rows form all the table

Or more destructively:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

EXEC sp_MSforeachtable @command1 = "DELETE FROM ?"

EXEC sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"

This was gleaned from this blog post.