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.
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
