I stumbled on a thread at MSDN titled “How to delete/drop all the tables from SQL Server Database without using Enterprise Manager?” when trying to look for a way to drop all tables from a SQL server database.
Here are some of the nuggets from the thread:
1) How to drop all tables from the database
EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”
2) How to delete all data from all the tables in the database
EXEC sp_MSforeachtable @command1 = “DELETE FROM ?”
3) How to truncate all data from all the tables in the database
EXEC sp_MSforeachtable @command1 = “TRUNCATE TABLE ?”
4) How to select all the rows from all the tables in the database
EXEC sp_MSforeachtable @command1 = “SELECT * FROM ?”
5) How to print the names and owners of all the tables in the database
EXECÂ sp_MSforeachtable @command1 = “PRINTÂ ‘?'” –Just print the tables names with owner(dbo)
Some caveats, like referential integrity rules and foreign keys references will have to be kept in mind when attempting to truncate data. Also, some of the changes can not be rolled back.