Delete all data from all tables

Question

Is there an elegant way to wipe all data in the database (removing every row from every table) without destroying the architecture and having to remove/read constraints?

I could just delete data from each table but this seems tedious. I've seen plenty of solutions for full on SQL implementations that make use of a stored procedure but obviously that functionality is not available to me. It's likely I won't be making this function available to end users of the product but it may be enabled for in-house testing purposes. As I stated I can do this manually so put this one down to curiosity

Manish Mishra · Accepted Answer · 2013-03-06T10:14:59.677

1

No. Since you want the db structure to remain intact including all relationships. Hence, first delete all master data and then slave data.

your script should contain truncate queries in a specific order.

Otherwise what you can do is generate a entire db schema and keep it at your disposal. wheneever you need to empty the db, drop the db, recreate it :)

on how to generate schema for SQL-CE db..use SQLCE-ToolBox

also for more details about operation upon SQLCE db, I loved this blog, and most of the sqlce users all over the world :)

edited Mar 06 '13 at 10:14

answered Mar 06 '13 at 10:05

Manish Mishra

12,163
5
35
59

This sounds really useful and I have thought about creating the database (including some "default entries") on installation rather than having to provide that in the installer however I wrote the database using [DataportConsole](http://www.primeworks-mobile.com/Products/DataPortConsole.html) to let me design the database using a diagram. Is there an easy to way to get a SQL CE scheme from an existing .SDF? – Simon Johnson Mar 06 '13 at 10:11
SQLCE TOOLBOX..this will give you options to generate schema with or without data for a .sdf file – Manish Mishra Mar 06 '13 at 10:14
That looks really useful but I only have access to VisualStudio2008 – Simon Johnson Mar 06 '13 at 10:18
not a problem, there are bunch of tools in there for both SQLCE3.5 and SQLCE4.0, some of them are meant to integrate inside visual studio ide and some function stand alone. – Manish Mishra Mar 06 '13 at 10:19
The standalone version requires a version 4.0 database, same as the add in installer – Simon Johnson Mar 06 '13 at 10:23
1

so in that case go for command line utility : http://sqlcecmd.codeplex.com/ also, read the blog..link in the answer – Manish Mishra Mar 06 '13 at 10:29
Looked though the some of the posts on that blog (Favorited!) and found this which gave me the exact functionality I wanted in an easy to way use: http://exportsqlce.codeplex.com/documentation This allows SQL Server Management Studio to create a script of the Database scheme using an existing database. – Simon Johnson Mar 06 '13 at 10:49
great (meanwhile, try upgrading both the visual studio and sqlce version :D) – Manish Mishra Mar 06 '13 at 10:51

score 1 · Answer 2 · edited May 23 '17 at 12:11

Thanks to some fantastic community help from Manish Mishra the best solution is to delete the database file and rebuild the database using a schema. I found my way to an addin for SQL Server Management Studio called ExportSqlCe though {this blog](http://erikej.blogspot.co.uk/). If anyone else needs this functionality they will need Microsoft SQL Sever Management Studio 2008 to be able to connect to a SQL CE 3.5 database.

Once connected to the database you can then create a database schema with this addon following these instructions

score 0 · Answer 3 · answered Mar 06 '13 at 10:02

0

if there are end users, then you can alternately use export option (in Express edition of SQL Server it automatically avoids any data in the tables)

So using export option, you can easily create a replica of database including all the things but no data in the tables

answered Mar 06 '13 at 10:02

Usman Waheed

555
5
14

i guess, when you do export in sql server, all relationships are lost, that's the only way it can export, without caring about the dependencies – Manish Mishra Mar 06 '13 at 10:07
I have tested it, it retains all the things except the data as I have express edition installed – Usman Waheed Mar 06 '13 at 10:10

score 0 · Answer 4 · answered Mar 06 '13 at 10:04

0

I think of this: correct me if I did not understood your question: you must iterate each table and then empty the table so a truncate table will just do the work.

You may get some help from http://how-to-code-net.blogspot.ro/2012/07/how-to-delete-all-tables-from-database.html so

DECLARE @TableName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.tables WHERE type in ('u') 

      OPEN cur

      FETCH NEXT FROM cur INTO @TableName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('truncate TABLE ' + @TableName)
            --select @TableName
            FETCH NEXT FROM cur INTO @TableName
      END
      CLOSE cur
      DEALLOCATE cur

Is this what you need?

answered Mar 06 '13 at 10:04

Alexa Adrian

1,778
2
23
38

Well this will fail when there is a relationship between the tables. – Monie corleone Mar 06 '13 at 10:09
I don' think SQL CE (3.5 at least) supports the TRUNCATE functionality – Simon Johnson Mar 06 '13 at 10:15
I once had to to the same think, but I used delete command; I run this code multiple times until it deleted all content (after first runs of course you get relationship errors) – Alexa Adrian Mar 06 '13 at 10:31

score 0 · Answer 5 · answered Feb 02 '15 at 05:39

Little Late to the party but I found this while looking for something "elegant",

select 'DELETE FROM ' + name+';' from sys.objects where type = 'U';

Will generate a something that can be turned into a string. For me I use LINQPad and just copy and paste. Good little trick! Thanks http://www.sqlservercentral.com/Forums/Topic1396480-392-1.aspx

Delete all data from all tables

5 Answers5