Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Drop all Tables in an MS SQL Server Database

DZone's Guide to

Drop all Tables in an MS SQL Server Database

· Java Zone
Free Resource

Download Microservices for Java Developers: A hands-on introduction to frameworks and containers. Brought to you in partnership with Red Hat.

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
     EXEC sp_executesql @cmd
     --select @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'

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
     EXEC sp_executesql @cmd
     --select @cmd


     FETCH NEXT FROM table_cursor INTO @table_schema, @table_name
END

CLOSE table_cursor 
DEALLOCATE table_cursor

 

Download Building Reactive Microservices in Java: Asynchronous and Event-Based Application Design. Brought to you in partnership with Red Hat

Topics:

Published at DZone with permission of Dan Haywood, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}