This is the third part of a 3 part series with some examples of sqlcecmd usage. The first part is here and the second here. This third part deals with querying database objects and formatting query output. (In other words, running SELECT statements)
To query a table
To get query output, simply use the q- option, followed by the SELECT statement. This will give you a result listing the column names and table data. Note that long fields are truncated to 256 chars, use the xml option if you need all data in the field (see below).
To output to a file
You can use pipe characters (> or | ), or use the –o option to direct output to a file:
To avoid column names and dotted lines
Use the –h option. The number after the –h indicates the number of rows to print between the column headings. Using 0 (zero) causes the heading not to be printed.
To compress output
To make output more compact, use the –s option, which must be followed by the character you wish to use a separator, in this case semicolon. This is combined with the –w option, which removes any trailing spaces from the output to make it more compact.
To output all data as xml
Finally, the –x option will output the query results in XML DataSet format.
You can mix the various formatting options (-s, –w and –h) to get the result you desire.
This concludes the 3 part series on using SqlCeCmd to manage SQL Compact databases directly from the command line.
Republished with author's permission. See the original post here.
About the author
Erik Ejlskov Jensen, SQL Server [Compact] MVP, .NET and Windows Mobile Developer.
Erik has been using SQL Server Compact since version 1.0, and is a moderator of the SQL Server Compact MSDN Forum.
He looks forward to a bright future for SQL Server Compact 4.0, which can now be used with ASP.NET in addition to Windows Mobile and Windows desktop applications. See his blog.