How to drop all the tables from database in SQL Server

Posted on Thursday, 19 April 2012 by SQL Developer

Run the below script in SQL Server. This will delete all the tables in a single shot from the database.


DECLARE
DECLARE @QUERY VARCHAR(500)

SELECT @TABLENAME = (SELECT TOP 1 [name]
FROM sysobjects WHERE [type] = 'U'
AND [name] LIKE '%'
ORDER BY [name])

WHILE @TABLENAME IS NOT NULL
BEGIN
SELECT @QUERY = 'DROP TABLE [dbo].[' + RTRIM(@TABLENAME) +']'
EXEC (@QUERY)
SELECT @TABLENAME = (SELECT TOP 1 [name]
FROM sysobjects WHERE [type] = 'U'
AND [name] LIKE '%'
ORDER BY [name])
END @TABLENAME VARCHAR(50)

This entry was posted in . Bookmark the permalink.

2 Responses to How to drop all the tables from database in SQL Server

  1. Anonymous20 April 2012 at 23:57

    Great post!!
    I was looking for something like this.


    Thanks!!

    ReplyDelete
  2. SQL Developer25 May 2012 at 09:26

    Hi, Thanks lot for your valuable comment!

    ReplyDelete