Some time ago, for testing purposes, I needed a fast and simple way to drop all tables from a database. If possible it should not cause any collateral damage and be easy to use. How hard can it be? Right?
Short googling resulted in a StackOverflow page which, among others, had the following query.
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
Easy, simple and working solution. Later I shared it with our DB developer but she pointed out that I used undocumented stored procedure. After that she started using terms I wasn’t aware of. Another problem was that I had to keep an eye on a SSMS connection and make sure that I was querying right database. Also it didn’t work on Azure hosted databases.
SELECT ' Drop table ' + s.NAME + '.' + t.NAME
FROM sys.tables t
JOIN sys.schemas s
ON t.[schema_id] = s.[schema_id]
WHERE t.type = 'U'
A professional DB developer may call above query a nifty piece of code. Unfortunately I still had to keep an eye on SSMS connection. Also people with basic SQL knowledge may find this query too complex for their taste.
Third solution proved to be a bit slower but it reduced the chance of unwanted accidents. You have to navigate to proper server and database and show which tables you want removed.
With SSMS open press F7 or select View and Object Explorer Details. Doing so will bring, well… Object Explorer Details window.
Then navigate to a desired database and open Tables folder which will contain all tables in a given DB. After that select tables you want removed, right click and pick Delete from the context menu.
This will bring up a summary screen. Leave the options as they are and click OK buttons couple of times (depending on a number of FK references).