151

I have an SDF file and I would like to retrieve its schema and query it with some UI. How can I do this? I have no Visual Studio installed on the machine and I would like to install as little software as possible.

JasonMArcher
  • 14,195
  • 22
  • 56
  • 52
agsamek
  • 8,734
  • 11
  • 36
  • 43
  • 1
    Google "sdf file viewer" and you'll see many choices, some of them free. – DOK Dec 23 '09 at 15:02
  • 8
    Use open source program "CompactView": http://www.sourceforge.net/p/compactview/home/Home/ – Ivan Nov 10 '11 at 23:01
  • 2
    See this [comprehensive list](http://erikej.blogspot.com/2009/04/sql-compact-3rd-party-tools.html) – Ash Machine May 09 '11 at 23:15
  • You can query the Information Schema to get names of tables and columns. See here: http://msdn.microsoft.com/en-us/library/ms174156(SQL.90).aspx – user227997 Dec 26 '09 at 16:16
  • Try [**SDF Viewer**](http://www.flyhoward.com/SDF_Viewer.aspx ) The schema is displayed for each table and SQL queries can be quickly generated and run. –  Jan 05 '10 at 11:11
  • 1
    If you're using Visual Studio > 2012 then you can use this extension: [SQL Server /SQLite Toolbox](http://sqlcetoolbox.codeplex.com/) – Richard Garside Jan 23 '15 at 10:47
  • 4
    ironically googling "sdf file viewer" redirects here – tampe125 Aug 21 '16 at 21:45
  • if you want to edit as well, go to : SqlCeToolbox.4.5.0.3.vsix: http://www.sokhawin.com/how-to-open-sql-compact-edition-file-sdf-in-visual-studio-or-linqpad/ – Haryono Dec 04 '18 at 00:45

3 Answers3

191

Download and install LINQPad, it works for SQL Server, MySQL, SQLite and also SDF (SQL CE 4.0).

Steps for open SDF Files:

  1. Click Add Connection

  2. Select Build data context automatically and Default (LINQ to SQL), then Next.

  3. Under Provider choose SQL CE 4.0.

  4. Under Database with Attach database file selected, choose Browse to select your .sdf file.

  5. Click OK.

Junle Li
  • 1,035
  • 1
  • 12
  • 18
Shankar Kamble
  • 2,983
  • 6
  • 24
  • 40
  • 11
    Tried the SSMS and VS way and both did not work. This one did. – Serj Sagan Apr 15 '14 at 04:21
  • Works great: here's a screenshot of it in action (see CodeCamper.sdf): https://twitter.com/johnleniel/status/470334781658783744 – Leniel Maccaferri May 24 '14 at 22:47
  • 3
    I tried this and got an error: that I needed to call SqlCeEngine.Upgrade() in order to use it. Entering that command in LinqPad did not work, so I made a quick console app to upgrade the file. Add references -> Assemblies -> Extensions -> System.Data.SqlServerCe , then new SqlCeEngine(@"Data Source=D:\mydb.sdf").Upgrade(); After running that, LinqPad worked nicely. – RandomEngy Jun 11 '14 at 20:50
  • 2
    LinqPad is a great tool in general - very light-weight and portable. – Bron Davies Nov 04 '14 at 18:35
  • Thanks for sharing this tool! It's blazingly fast and easy to use. – AnandShanbhag Jun 16 '16 at 08:38
72

Try the sql server management studio (version 2008 or earlier) from Microsoft. Download it from here. Not sure about the license, but it seems to be free if you download the EXPRESS EDITION.

You might also be able to use later editions of SSMS. For 2016, you will need to install an extension.

If you have the option you can copy the sdf file to a different machine which you are allowed to pollute with additional software.

Update: comment from Nick Westgate in nice formatting

The steps are not all that intuitive:

  1. Open SQL Server Management Studio, or if it's running select File -> Connect Object Explorer...
  2. In the Connect to Server dialog change Server type to SQL Server Compact Edition
  3. From the Database file dropdown select < Browse for more...>
  4. Open your SDF file.
speciesUnknown
  • 1,644
  • 2
  • 14
  • 27
Peter Schuetze
  • 16,185
  • 4
  • 44
  • 58
  • 4
    It didn't work for me, the open source Compact View mentioned by Ivan worked. I tried SQL Management Studio 2008 Express and my sdf file was Sql Ce version 4.0 – Nikolaos Georgiou Jan 21 '12 at 13:59
  • Isn't an SDF file from SQL Server Compact? And thus the SQL Server Management Studio will not be able to open them? I'm surprised this worked for Peter. – dumbledad Oct 24 '12 at 12:22
  • 13
    My answer edit was rejected, so excuse the poor formatting. The steps are not all that intuitive, and should be in the answer! (1) Open SQL Server Management Studio, or if it's running select File -> Connect Object Explorer... (2) In the Connect to Server dialog change Server type to SQL Server Compact Edition (3) From the Database file dropdown select (4) Open your SDF file. – Nick Westgate Oct 31 '12 at 21:41
  • 12
    I've seen this work on other machines, but doesn't work for me. "SQL Server Compact Edition" is not an option in the Connect to Server dialog for me. @Brakomen's alternative answer (opening in VS2012) did work for me. – Richard Fawcett Jul 12 '13 at 10:55
  • 13
    "SQL Server Compact has been dropped from Management Studio, use Visual Studio instead." [Source](http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d2735d0d-f593-40e5-bf7a-356704c3aaee/sql-server-compact-edition-support-for-sql-server-management-studio-2012-rc0?forum=sqltools) – Alex Oct 21 '13 at 17:47
  • My experience agrees with Richard's - Management Studio doesn't seem to do Compact. My Studio version - 11.0.3128.0. By the way, step 3 seems to have lost something in translation from Nick's comment. – D A Vincent Nov 24 '13 at 00:41
  • 9
    Note. SSMS 2012 seems to have whacked it. : – granadaCoder Jan 28 '14 at 23:10
  • 2
    Found a solution for the missing "SQL Server Compact Edition" in SSMS... https://marketplace.visualstudio.com/items?itemName=ErikEJ.SQLServerCompactSQLiteToolboxforSSMS – JanBorup Mar 07 '18 at 20:55
  • 2
    There is no "SQL server compact edition" option. Maybe this was included back in SSMS 2012 but in 2016 its not. – speciesUnknown May 09 '18 at 19:46
53

You can open SQL Compact 4.0 Databases from Visual Studio 2012 directly, by going to

  1. View ->
  2. Server Explorer ->
  3. Data Connections ->
  4. Add Connection...
  5. Change... (Data Source:)
  6. Microsoft SQL Server Compact 4.0
  7. Browse...

and following the instructions there.

If you're okay with them being upgraded to 4.0, you can open older versions of SQL Compact Databases also - handy if you just want to have a look at some tables, etc for stuff like Windows Phone local database development.

(note I'm not sure if this requires a specific SKU of VS2012, if it helps I'm running Premium)

Ilya Palkin
  • 14,687
  • 2
  • 23
  • 36
Henry C
  • 4,781
  • 4
  • 43
  • 83
  • 4
    Thanks Blakomen, best answer if you have VSS 2012 IMO. Unfortunatelly it was not OP's case, but it helped me. – Tiago César Oliveira Mar 21 '13 at 13:20
  • 5
    In VS 2013 I do not see the "Change..." button and no option for SQL Server Compact 4.0, even though I have it installed. The only thing that worked for me was with LINQPad. – RandomEngy Jun 11 '14 at 21:30
  • 50
    In 2013, support for SQL CE was removed :( – Henry C Jul 08 '14 at 05:44
  • 4
    There is also [this](https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1) extension to VS2010-2015 – metalheart Sep 28 '15 at 18:36
  • 2
    FYI - http://stackoverflow.com/a/20364011/90236 – Michael Levy May 11 '16 at 13:42
  • 1
    I installed SQL Server Compact Edition 4.0 (https://www.microsoft.com/en-gb/download/details.aspx?id=17876), then in VS 2017 installed an extension called “SQLite/SQL Server Compact Toolbox”. I was then able to connect to and query CE database. – w5m Jan 29 '20 at 10:11
  • to install `SQLite and SQL Server Compact Toolbox` extension in VS 2022: Extensions / Manage Extensions / Marketplace / Tools / (name of extension). Close VS; msix installer should automatically start. To use: VS / Server Explorer / rt-click Data Connections / Add Connection / Choose Data Source: SQL Server Compact 4.0 (Simple by ErikEJ). Then in property `Source / Data Source`, put full path to .sdf file. E.g. `C:\SomeDir\MyCompactDB.sdf`. I recommend first Adding a connection to a (full; not compact) SQL Server DB; this installs needed components. – ToolmakerSteve Jun 14 '23 at 00:32
  • ... unfortunately all I see in Data Connections pane is `MyCompactDB.sdf` / `Tables` / `(Table names)`. I don't see columns of each table. Worse, can't rt-click "Show Table Data". At this point, I stopped. I did not attempt to use that Data Source in code. – ToolmakerSteve Jun 14 '23 at 00:40