Google+ Followers

Friday, July 25, 2014

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

No comments: