drop all tables from sql ce database

Is it possible to executa a com开发者_高级运维mand which drops all tables from sql ce database ?

thanks for help

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.

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

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

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.