Some content on this site is available only to logged-in subscribers. Contact Us for information on becoming a subscriber.

InSource.Solutions | InSource Training | InSource Client Portal
InSource Solutions Logo
Log In Sign Up
InSource.Solutions InSource Training InSource Client Portal Log In Sign Up
  • Home
  • Supporting Technologies (i.e. Windows / SQL Server / VMWare / etc.)
  • Supporting Technologies Tech Notes

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

Last updated: February 28th, 2025

Description

  • Author: Ron Tanner
  • Published: February 28th, 2025

Details:

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

sql server remove tables
Give feedback about this article

Recommended articles

How to receive group emails

Read More

TN 1393 Deleting Tags from AVEVA Insight

Read More
Support Icon

CONTACT SUPPORT

How to reach us

10800 Midlothian Turnpike Tpke, Suite 209, Richmond, VA 23235

1.877.INSOURCE

Technical Support - 1.888.691.3858

Contact Us

  • InSource Solutions
  • InSource Training
  • InSource Client Portal
  • Log In
InSource Solutions Logo

© 2025 InSource Solutions. All Rights Reserved.

Knowledge Base Software powered by Helpjuice

Expand