Drop all tables in an MS SQL Server database

Currently working on a JDO DataNucleus object store for Apache Isis, as part of an app that’s gonna be deployed onto MS SQL Server.

Since I’m using DataNucleus to automatically create the database schema, the build-debug cycle is:

  1. to run the app
  2. inspect the resultant schema
  3. drop all the tables
  4. change the domain object annotations/metadata

and then go round the loop again.

What with foreign-key constraints between tables, step (3) is not exactly trivial. So it seemed like it’d be a good idea to write a little script to simplify step (3) of the above, namely to drop all the tables in my (development!) database. Here’s what I came up with…

DECLARE @table_schema varchar(100)
       ,@table_name varchar(100)
       ,@constraint_schema varchar(100)
       ,@constraint_name varchar(100)
       ,@cmd nvarchar(200)
 
 
--
-- drop all the constraints
--
DECLARE constraint_cursor CURSOR FOR
  select CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   where TABLE_NAME != 'sysdiagrams'
   order by CONSTRAINT_TYPE asc -- FOREIGN KEY, then PRIMARY KEY
      
 
OPEN constraint_cursor
FETCH NEXT FROM constraint_cursor INTO @constraint_schema, @constraint_name, @table_schema, @table_name
 
WHILE @@FETCH_STATUS = 0 
BEGIN
     SELECT @cmd = 'ALTER TABLE [' + @table_schema + '].[' + @table_name + '] DROP CONSTRAINT [' + @constraint_name + ']'
     --select @cmd
     EXEC sp_executesql @cmd
 
     FETCH NEXT FROM constraint_cursor INTO @constraint_schema, @constraint_name, @table_schema, @table_name
END
 
CLOSE constraint_cursor
DEALLOCATE constraint_cursor
 
 
 
--
-- drop all the tables
--
DECLARE table_cursor CURSOR FOR
  select TABLE_SCHEMA, TABLE_NAME
    from INFORMATION_SCHEMA.TABLES
   where TABLE_NAME != 'sysdiagrams'
     and TABLE_TYPE != 'VIEW'
 
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_schema, @table_name
 
WHILE @@FETCH_STATUS = 0 
BEGIN
     SELECT @cmd = 'DROP TABLE [' + @table_schema + '].[' + @table_name + ']'
     --select @cmd
     EXEC sp_executesql @cmd
 
 
     FETCH NEXT FROM table_cursor INTO @table_schema, @table_name
END
 
CLOSE table_cursor 
DEALLOCATE table_cursor

UPDATE: I’ve updated this to escape all schema and table names in square braces; the original script failed when it encountered a table that was a reserved word.

UPDATE: I’ve updated this to ignore any views (in INFORMATION_SCHEMA.TABLES)

About these ads

Posted on July 18, 2012, in Uncategorized and tagged , . Bookmark the permalink. 7 Comments.

  1. was looking for the same desperately , Thanks

  2. I know this is a naive question and I’m going to stick my foot in my mouth, but I can’t resist asking.

    Why?

    Why not just delete the database and redefine it? I’m sure there must be reasons. Fear of getting dis-contiguous space or not all you had before? Other things??

    Thanks,
    Alex Scianna

    • This is a test db, so I just wanted a way to zap the tables. But I wanted to keep my login/user mappings in place, and the system tables/sprocs that the diagramming feature in SQL Mgmt Studio installed). And I wanted to keep a query window open in SQL Mgmt Studio in the same database, without having to do a “use database” etc each time.

      Also, even though this was only a test system, I prefer to operate at database-level (manipulating tables etc) rather than at the server-level (manipulating entire databases). Kinda safer that way.

      And finally, having not used the INFORMATION_SCHEMA views very much (I go back to Sybase days and querying sysobjects etc), I wanted to learn how to do it.

  3. Richard Pawson

    Thanks, that’s useful. Like Alex, I have tended to just delete the database, but I can see your argument for just dropping all the tables instead and will try it. (Though why there isn’t a simple option to do this from the VS Server view I don’t know!)

  4. Great! That’s what I’m looking for. The script works very well. Thank you!

  5. really really useful for getting a UAT environment with old tables with FK contraints referencing tables no longer in the model cleaned up and then recreated and reloaded for user’s testing, without needing to get a DBA involved… in 5 minutes I was able to get the developer back where he needed to be to make progress getting the environment ready. Thanks for sharing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 260 other followers