Delete all tables in sql database

Hi all,

Recently I encountered a bizarre request, I had to delete all the tables from an Sql database. The reason is even more bizarre. The client had a system auto creating them without making sure if they exist first. Ao, I had to write some code to first delete all the tables.

I thought maybe other people like me may encounter the same issue, so I wrote some code for it.

The code can either print out the Drop statements for each table or execute it.

Good luck!

Tested on sql server 2005

REXML could not parse this XML/HTML: 
   1: DECLARE @dropSql nvarchar(1000)
   2: DECLARE DropSequence CURSOR LOCAL FAST_FORWARD
   3:
   4: FOR
   5:     SELECT
   6:         N'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME)
   7:     FROM
   8:         INFORMATION_SCHEMA.TABLES
   9:     WHERE
  10:         TABLE_TYPE = 'BASE TABLE'
  11:             AND
  12:         OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
  13:
  14:     OPEN DropSequence
  15:     WHILE 1 = 1
  16:         BEGIN
  17:             FETCH NEXT FROM DropSequence INTO @dropSql
  18:
  19:                 IF @@FETCH_STATUS <> 0 BREAK
  20:                     RAISERROR (@dropSql , 0, 1) WITH NOWAIT
  21:
  22:             --EXEC(@dropSql )
  23:             PRINT @dropSql
  24:         END
  25:
  26: CLOSE DropSequence
  27: DEALLOCATE DropSequence
</pre></p>
REXML could not parse this XML/HTML: 
</div>
REXML could not parse this XML/HTML: 
</div>

Thank you for your interest!

We will contact you as soon as possible.

Send us a message

Oops, something went wrong
Please try again or contact us by email at info@tikalk.com