Skip to main content
InSource Solutions

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.

clipboard_edf4a5e2ed5bf8902ccf9493d095e3b9a.png

clipboard_ec1b238056af84e9eee04c6555d825785.png

clipboard_e9f6d83f29208b536fd12443c453681bf.png

 

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

  • Was this article helpful?