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

Verify, standardize, and correct the Big 4 + more– name, email, phone and global addresses – try our Data Quality APIs now at Melissa Developer Portal!

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

 

Developers! Quickly and easily gain access to the tools and information you need! Explore, test and combine our data quality APIs at Melissa Developer Portal – home to tools that save time and boost revenue. Our APIs verify, standardize, and correct the Big 4 + more – name, email, phone and global addresses – to ensure accurate delivery, prevent blacklisting and identify risks in real-time.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}