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

   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

Submit to Hacker News

[NOTE] This post has been imported to Hugo from a Wordpress blog. It may be broken, missing images, code snippets or other information.

[NOTE] updated May 25, 2009. It's 3535 days old . This article may have outdated content.