TN - 1272 How to drop all tables in a SQL Server database

Description

This article from InSource shows drop all tables in a SQL Server database using T-SQL script.

• Author: Ron Tanner
• Published: 06/27/2022
• Applies to: Microsoft SQL Server (any version)

Details

How to drop all tables in a SQL Server database

Many times, you do not have permission to restore a or create a database when working with remote SQL server farms.  T-SQL scripts can be written to dynamically drop all tables removing constraints and then drop the tables dynamically.

-- Be very careful to use the correct database name.
-- Be sure to make a backup of the database prior to running the T-SQL script.

The following T-SQL script will drop all constraints in the specified database and then drop all tables.

USE [EnterDatabaseName]

GO

-- DROP the table constraints

-- Create varialbe name DropConstraints to hold T-SQL alter statments

DECLARE @DropConstraints NVARCHAR(max) = ''

SELECT @DropConstraints += 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'

                        +  QUOTENAME(OBJECT_NAME(parent_object_id)) + ' ' + 'DROP CONSTRAINT' + QUOTENAME(name)

FROM sys.foreign_keys

EXECUTE sp_executesql @DropConstraints;

GO

-- DROP all tables in the specified database "USE [marine]

-- Create variable named @DropTables to hold T-SQL drop statments

DECLARE @DropTables NVARCHAR(max) = ''

SELECT @DropTables += 'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

FROM INFORMATION_SCHEMA.TABLES

EXECUTE sp_executesql @DropTables;

GO