During the setup of a LoadTest with the DeNamiK LoadGen I created loads of tables in a SQL database. The content of the tables was irrelevant, pure for testing purposes, and needed to be cleaned.
Deleting them all by hand, one by one, would be tremendous job. As an IT guy I dislike repeating jobs, so I searched for an automated solution.
Since I have limited permissions I don’t want to delete the database and recreate it. This would require more permissions than available, requires me to reconfigure the permissions (etc) and would raise questions by the DBA’s.
There is an undocumented stored procedure called ‘sp_MSforeachtable’. This stored procedure executes a command for each table in the database, for instance ‘DROP TABLE’.
The following command will delete all tables in the active database (use with caution!)
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"