SQL Server Compact Toolbox – Extension #12

  • Article

Today’s post comes from Guest Blogger Erik Ejlskov Jensen, SQL Server Compact MVP, @ErikEJ

Toolbox for SQL Server Compact developers

If you use SQL Server Compact, Microsoft’s embedded database for devices, desktops and soon ASP.NET, this extension is for you.

It adds several features not found in Server Explorer Data Connections:

  • Support for both version 3.5 and 4.0 database files
  • Script tables, including data
  • Script entire schema, optionally with data, both of SQL Server Compact and SQL Server 2005 or later databases
  • Import to SQL Server Compact from a SQL Server 2005/2008 database or a CSV file
  • Basic, free form query execution, with Excel friendly output
  • Parse SQL scripts
  • Display graphical estimated query plan
  • Generate detailed DGML files for visualizing table columns and relationships (requires VS 2010 Premium or higher to view)
  • Create and manage SQL Server Merge Replication subscriptions

clip_image002

To install

https://visualstudiogallery.msdn.microsoft.com/en-us/0e313dfd-be80-4afb-b5e9-6e74d369f7a1

To use

To start using the add-in, go to Tools, and select SQL Server Compact Toolbox:

clip_image004

The add-in will list any version 3.5 database connections, which you have defined in Server Explorer. For 4.0 (which is currently not supported in Server Explorer, but will be so in VS 2010 SP1), you can add a connection from the context menu (or even create an empty 4.0 database file):

clip_image005

To refresh the listed connections, click the refresh button on the add-in toolbar.

Scripting an entire database

To create a SQL script file with all object definitions and data in your SQL Server Compact database (for documentation, backup or migration use), select “Script database schema and data” from the context menu:

clip_image006

Viewing estimated execution plan

To find out if you are missing indexes in your database, you can examine the graphical estimated query plan.

clip_image007

With the SQL editor open, press Estimated Plan:

clip_image008

It looks like an index is used here, which is a “good thing”.

Displaying a graphical view of your database

From the database context menu, select “Create Database Graph (DGML)”

clip_image009

This will allow you to explore an interactive graph of your database tables, relationships, and columns:

clip_image011

More information

You can submit feedback/patches, request features and browse/download the source code at the CodePlex site: https://sqlcetoolbox.codeplex.com – and keep up to date with the latest SQL Server Compact news on my blog: Everything SQL Server Compact

Have you written an extension? Want to see it featured here? Let me know!