Install guide Management Studio add-in
Command line utilities
Developer tips
Scripting API samples

User Guide - SSMS add-in

Script a SQL Compact database
Script a SQL Server database
Browse and Edit data
Script tables and indexes
Import CSV data
Rename a table

The addin only works with SQL Compact files on your desktop, not with SQL Compact files on a Mobile Device.

To script the entire selected database file with schema and data (or just schema), right click the database in Object Explorer:

addin1.png

The resulting script can be used to create a new database, either by running the script in SQL Server Management Studio against a new empty database (either SQL Compact or SQL Server, or by running the script using for example sqlcmd or sqlcecmd http://sqlcecmd.codeplex.com


To script a server database in SQL Compact format, right click the database node in Object Explorer:

addin3.png

Select the server database to connect to, and select the file name for the script.
Notice that the SQL Server Export currently does not support identically named tables in different schemas.


The add-in adds 4 new menu items to the table menu in Object Explorer:

fig4.png

To Show and Edit table data, just click "Show Table Data", and you can edit the data in a basic DataGridView.
Use F3 to search in a column, right click Image columns to import, export or delete images, click a column title to sort on this column, and use the Reload button to refresh data in the grid.

fig5.png

To script a table, click "Script Table as", and as you can see, various scripts can be generated: CREATE, DROP, SELECT, INSERT, UPDATE, DELETE and INSERTs (data):

fig6.png

You can also script indexes in a similar way.

To "import" data (generate a script with INSERT statements), select Import Data from->CSV (only supported format):

fig7.png

Locate the .csv file, by pressing the ".." button. The .csv file must have field names in the first line, and all field names must match the selected table. You can also specify the field separator:

fig8.png

Press OK to import the file.

Note that IDENTITY columns are not required to be present in the CSV file, nor is any other field that allows NULL or has a default value.

To rename a table, click Rename:

fig9.png

Then enter the new table name and press OK (or Enter):

fig10.png

Last edited May 7, 2011 at 11:14 AM by ErikEJ, version 42

Comments

ErikEJ Jan 26, 2012 at 7:46 AM 
jritsema: I am aware of this - see http://exportsqlce.codeplex.com/workitem/16992

jritsema Jan 25, 2012 at 9:09 PM 
I'm getting an out of memory error when scripting inserts on a 1Gb database (trying to export data from SQLCE to full SQL Server). Has anyone else experienced this and found any workarounds?

Nime Aug 26, 2011 at 2:55 PM 
How can I load the image back to DB?

UPDATE personList SET personPhoto=SqlCeCmd_LoadImage(5cf13e8ce7394517837e1afa3d0918be.blob), personFirstName='Adam', personLastName='Cloey' WHERE personID=3

DB and blob file in the same folder.

cpnet Sep 28, 2010 at 6:12 PM 
Cool utility. However, gives an error for every row while invoking "Show Table Data" if column is of type "rowversion".

ErikEJ May 19, 2010 at 6:33 PM 
Column order should not matter, but yes, you can script Create and data,
then drop and run the modified create and data (INSERT) scripts

jjchen May 18, 2010 at 10:37 PM 
The utility is very useful! Thanks a lot!
Could we use the utility to change the column orders in a DB table?

ErikEJ Nov 4, 2009 at 4:33 AM 
I have added a paragraph, thanks for the feedback.

micjohny Nov 2, 2009 at 8:10 PM 
Thanks for the utility. I have a question as to how to I create a new SQLCE database from the database script I exported.