Drop all the tables in a database

Gaushick

4.00/5 (1 vote)

31 Jan 2012CPOL

51.5K

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)