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 ?"
I’ve executed the command in the ‘Microsoft SQL Server Management Studio’ using a query. Before I’ve executed the command the content of the database was filled with tables as can be seen in the picture below.
On the top left of the management console there’s a button called ‘New Query’. Clicking this would create a new query dialog on the right pane.
Make sure the correct database is selected (in my case ‘DeNamiKLoadGen’). If you select the wrong database, you might end up with the wrong database without tables.
Next execute the command with the ‘! Execute’ button.
The result of the command is displayed in the lower pane with the name ‘Messages’. It should return with the message ‘Command(s) completed successfully.”
The content of the database is now empty, there are no tables left.
Ingmar Verheij
English 
Nederlands