Tip/Trick

Drop all the tables in a database

Gaushick

Rate me:

4.00/5 (1 vote)

31 Jan 2012CPOL

51K

Drop all the tables in a database(give the database name and table name in the below query).

SQL

--Drop all the table in the database
USE DATABASE
GO
DECLARE @tname VARCHAR(150)
DECLARE @strsql VARCHAR(300)

SELECT @tname = (SELECT TOP 1 [name] FROM sys.objects WHERE [type] = 'U' and [name] like N'TableName%' ORDER BY [name])

WHILE @tname IS NOT NULL
BEGIN
    SELECT @strsql = 'DROP TABLE [dbo].[' + RTRIM(@tname) +']'
    EXEC (@strsql)
    PRINT 'Dropped Table : ' + @tname
    SELECT @tname = (SELECT TOP 1 [name] FROM sys.objects WHERE [type] = 'U' AND [name] like N'TableName%'  AND [name] > @tname ORDER BY [name])
END

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Gaushick

Software Developer

India

.Net Programmer

Comments and Discussions

SQL Server 2008 Tried the following

Timotheos10-Nov-12 1:16

Timotheos

10-Nov-12 1:16

SQL

DECLARE @tname VARCHAR(150)
DECLARE @strsql VARCHAR(300)

SET @tname = (
SELECT TOP 1 [name]
FROM sys.tables
ORDER BY [name])

WHILE @tname IS NOT NULL
BEGIN
    SET @strsql = 'DROP TABLE [dbo].[' + RTRIM(@tname) +']'
    EXEC (@strsql)
    PRINT 'Dropped Table : ' + @tname
    SET @tname = (
    SELECT TOP 1 [name]
    FROM sys.tables
    WHERE [name] > @tname
    ORDER BY [name])
END

First and foremost, thanks for taking the time to share. A f...

Pablo Aliskevicius31-Jan-12 20:01

Pablo Aliskevicius

31-Jan-12 20:01

First and foremost, thanks for taking the time to share.
A few comments:

1. You could use sys.tables instead of sys.objects.
2. Using a cursor may be more efficient than selecting top 1. Did you compare them?
3. I kind of miss the point: why would someone want to drop all tables in the database? It would be nice if you added a scenario. For instance, to set up a database with a known state for automated tests, I'd prefer to restore one.

JM2B,