Home  »  Code Snippets  »  SQL Server  »

Drop all tables from a database in sql server

 Posted By : Manoranjan SahooPosted Date : 18/05/2011 09:53:18 AM Category : SQL Server Points : 4 (Rs 1.00)

Hello,

Yesterday, i faced one big problem that in my database there are lots of temporary tables present. I want to delete all those tables. But it's very difficult to delete manually. I tried to create a PL/SQL program for this and finally did. So i am posting that program for your needs.

Download Full Source Code Here

USE [DataBaseName]
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

To get complete source code of the query click on the below link and download the full source code :

Download Link :  http://www.filesonic.com/file/1025388964/Delete_All_Table_in_SQL.zip

 

Thank You.

Responses
No responses found for this thread. Be the first to respond this thread.
Post Reply
Login to post Response.