Delete all tables from the database

Suppose you want to delete all tables from your database, how can you achieve this?

There can be many ways to write a code; In this article I will write two different code to delete all tables from our database.

Please go thought the code below to delete all tables from the database.

Let’s create two tables and we will write a code to delete all tables.

CREATE DATABASE TEACHMESQLSERVER

GO

CREATE TABLE EMP(EMPID INT)

GO

CREATE TABLE DEP(DEPID INT)

Go

Method 1

SELECT * FROM SYS.TABLES /*you can see our database has only two tables which we recently we created. We will write code to delete all tables from this database*/

GO

Use TEACHMESQLSERVER

DECLARE @NAME VARCHAR(128)-- DELETING ALL TABLES FROM A DATBASE

DECLARE @SQL VARCHAR(254)

SELECT @NAME = (SELECT TOP 1 [NAME] FROM SYS.TABLES ORDER BY [NAME])

WHILE @NAME IS NOT NULL

BEGIN

    SELECT @SQL = 'DROP TABLE [DBO].[' + RTRIM(@NAME) +']'

    EXEC (@SQL)

    PRINT 'DROPPED TABLE: ' + @NAME

    SELECT @NAME = (SELECT TOP 1 [NAME] FROM SYS.TABLES ORDER BY [NAME])

END

GO

 
You can check all tables have been deleted from your database.

SELECT * FROM SYS.TABLES

Method 2

CREATE TABLE EMP(EMPID INT)

GO

CREATE TABLE DEP(DEPID INT)

GO

/*LETS DROP ALL TABLES FROM OUR DATABASE */

DECLARE @SQL VARCHAR(MAX),@TABLENAME VARCHAR(MAX)

SET @SQL ='DROP TABLE '

SET @TABLENAME=(SELECT TOP 1 NAME FROM SYS.TABLES)

SELECT @TABLENAME= @TABLENAME+','+NAME FROM SYS.TABLES WHERE name <>@TABLENAME

SET @SQL= @SQL+@TABLENAME

EXEC(@SQL)

PRINT 'Tables '+'('+@TABLENAME+')' +' Has been Dropped'

GO

You can check all tables have been deleted from your database.

SELECT * FROM SYS.TABLES