How to drop all the tables from database in SQL Server

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. Great post!!
    I was looking for something like this.


    Thanks!!

    ReplyDelete
  2. Hi, Thanks lot for your valuable comment!

    ReplyDelete