drop all tables from sql ce database

Question

Is it possible to executa a command which drops all tables from sql ce database ?

thanks for help

Answer 1

You can use information SCHEMA to generate a script:

select 'drop table ' || table_name || ';'
  from information_schema.tables;

Then run that script. You might need to run the script several times, since there is no CASCADE option (AFAIK) in SQLCE.

Answer 2

System.IO.File.Delete and then run the requires CREATE TABLE scripts

Answer 3

You can also drop database, and create it again, but it will erase all your configuration, so may not be the best option.

Answer 4

This is how I do it:

1. Using SQL Server Compact/SQLite Toolbox in Visual Studio, right-click on the .sdf file and select Script Database > Script Database Schema...
2. Save it as CreateTables.sqlce

3. Paste the following in a PowerShell console:

   function GenerateDropScript($DbSchemaFile)
   {
       function Reverse($inputArr)
       { 
           $arr = @($inputArr)
           [array]::Reverse($arr)
           return $arr
       }
       $TableNames = Get-Content $DbSchemaFile |
           ? { $_.StartsWith('CREATE TABLE') } |
           % { $_.Substring(14).Replace('] (', '') }
       $ReverseNames = Reverse($TableNames)
   
       "Generating DROP script for all tables in: `n`t$DbSchemaFile`n"
   
       $ReverseNames | % { "DROP TABLE [$_]; GO;" }
   }
   

4. Run GenerateDropScript CreateTables.sqlce

This will read the CREATE script and generate DROP scripts for all tables in the reverse order they are created, so you don't get any dependency errors.