Rob Allred

Developer, Outdoor Enthusiast & Purveyor of Odd Things. AngularJS, Javascript, ASP_NET, OrchardCMS.

  • Twitter

Delete all the tables from your database

Posted on March 16, 2017


I really like using the sql tools in visual studio that let you compare two databases schemas or data. I will often bring a copy of data from a production server and run code locally so I can test my work against real data. Often this is something I need to do over and over again. So I have found that at time it is nice to just delete all the tables in the database and use an SSIS package to just copy the data all over again. If you have more than a few tables being able to run a quick script that will delete the tables is very nice. Warning: this will delete all the tables in your database, so I suggest you be cautious about how and where you run this code. Anyhow, I use this from time to time and having it on my blog is a great place to find the code I am looking to use. I hope someone else finds this useful.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
use [DB_NAME]
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

Filed Under: Database, SQL Tagged With: Code Snippets, Sql Server, tsql

Categories

  • Accessibility (1)
  • anjularjs (5)
  • Asp.Net (3)
  • CSharp (2)
  • CSS (1)
  • Database (3)
  • Github (1)
  • Html5 (1)
  • javascript (11)
  • Linq (1)
  • Orchard Cms (9)
  • Regex (1)
  • SQL (1)
  • Uncategorized (1)
  • Videos (4)
  • vuejs (1)
  • Web Api (2)

Recent Posts

  • Commonly used regex for zip codes and addresses
  • Delete all the tables from your database
  • Orchard CMS Content Control Wrapper
  • Orchard Harvest 2017
  • Sending Emails in a Asp.net WebApi

Copyright © 2014