Drop all tables from database in SQL Server 2005


Sometimes in our application we create some temporary tables for our use and because of some reason those are not deleted by our application and our database becomes heavy due to large number of tables. So we want to delete all those temporary tables at a single query. Use this query to delete all tables.

Sometimes in our application we create some temporary tables for our use and because of some reason those are not deleted by our application and our database becomes heavy due to large number of tables. So we want to delete all those temporary tables. But it's very difficult to delete manually. So, I am giving the PL/SQL program to do this.

Use below PL/SQL program to delete all tables.


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


I tried this only in Microsoft SQL Server 2005. You can try in other versions of SQL Server.

.

Reference: http://dotnetsquare.com/codesnippets/18-drop-all-tables-from-a-database-in-sql-server


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: