Dropping All Tables in SQL Server

In SQL Server, there may be situations where you need to drop all tables in a database. This can be useful when you want to recreate a database from scratch or when you want to remove all data and schema from a database. Here's how to do it.

Using the `DROP TABLE` Statement

The simplest way to drop all tables in a database is to use the `DROP TABLE` statement. However, this method requires you to specify the name of each table individually. Here's an example:

DROP TABLE table1; DROP TABLE table2; DROP TABLE table3; ... 

This method can be tedious if you have a large number of tables in your database.

Using a Script to Drop All Tables

A more efficient way to drop all tables in a database is to use a script that generates the `DROP TABLE` statements for you. Here's an example:

DECLARE @sql NVARCHAR(MAX) = ''; SELECT @sql += 'DROP TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '; ' FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id; EXEC sp_executesql @sql; 

This script uses the `sys.tables` and `sys.schemas` system views to generate a list of all tables in the database, and then executes the `DROP TABLE` statements using the `sp_executesql` stored procedure.

Using the `sp_MSforeachtable` Stored Procedure

Another way to drop all tables in a database is to use the `sp_MSforeachtable` stored procedure. Here's an example:

EXEC sp_MSforeachtable 'DROP TABLE ?'; 

This stored procedure executes the `DROP TABLE` statement for each table in the database.

Important Note

Before dropping all tables in a database, make sure you have backed up your data and schema, as this operation will permanently delete all data and schema from the database.

Example Output

Here's an example output of the script that drops all tables in a database:

DROP TABLE dbo.table1; DROP TABLE dbo.table2; DROP TABLE dbo.table3; ... 

This output shows the `DROP TABLE` statements that are executed to drop all tables in the database.