{"id":2826,"date":"2011-06-10T10:09:50","date_gmt":"2011-06-10T08:09:50","guid":{"rendered":"https:\/\/ingmarverheij.com\/2011\/06\/remove-all-tables-from-database\/"},"modified":"2011-06-10T10:09:50","modified_gmt":"2011-06-10T08:09:50","slug":"remove-all-tables-from-database","status":"publish","type":"post","link":"https:\/\/ingmarverheij.com\/en\/remove-all-tables-from-database\/","title":{"rendered":"Remove all tables from database"},"content":{"rendered":"

\"SQL<\/p>\n

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.<\/p>\n

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.<\/p>\n

Since I have limited permissions I don\u2019t 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\u2019s. <\/p>\n

There is an undocumented stored procedure called \u2018sp_MSforeachtable\u2019. This stored procedure executes a command for each table in the database, for instance \u2018DROP TABLE\u2019.<\/p>\n

The following command will delete all tables in the active<\/em> database (use with caution<\/strong>!)<\/p>\n

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"<\/pre>\n
<\/p>\n
I\u2019ve executed the command in the \u2018Microsoft SQL Server Management Studio\u2019 using a query. Before I\u2019ve executed the command the content of the database was filled with tables as can be seen in the picture below.<\/p>\n
\"Database<\/a><\/p>\n
On the top left of the management console there\u2019s a button called \u2018New Query\u2019. Clicking this would create a new query dialog on the right pane.<\/p>\n
Make sure the correct database is selected (in my case \u2018DeNamiKLoadGen\u2019). If you select the wrong database, you might end up with the wrong database without tables.<\/p>\n
\"Microsoft<\/a><\/p>\n
Next execute the command with the \u2018! Execute\u2019 button.<\/p>\n
The result of the command is displayed in the lower pane with the name \u2018Messages\u2019. It should return with the message \u2018Command(s) completed successfully.\u201d<\/p>\n
\"Message<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
The content of the database is now empty, there are no tables left.<\/p>\n
\"Database<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Ingmar Verheij<\/p>","protected":false},"excerpt":{"rendered":"
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, […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[307],"tags":[339,173,340],"class_list":["post-2826","post","type-post","status-publish","format-standard","hentry","category-microsoft-sql","tag-sp_msforeachtable","tag-sql","tag-undocumented"],"_links":{"self":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts\/2826","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/comments?post=2826"}],"version-history":[{"count":1,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts\/2826\/revisions"}],"predecessor-version":[{"id":4673,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts\/2826\/revisions\/4673"}],"wp:attachment":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/media?parent=2826"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/categories?post=2826"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/tags?post=2826"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}