Free Schema Export for SQL Server 2008?

Question

Not sure if this belongs here or on ServerFault, but I wonder if someone knows a free tool to export a SQL Server 2008 Schema? It's only for Tables and their Indexes, Foreign-Keys etc. and it needs to be a command line tool to run as part of a build process. If it can read a .net connection string, that would be awesome (hence the .net tag)

Data is not needed and any sort of versioning/diff is also "Nice, but not needed". And yes, I am aware of Red-Gate's awesome SQL Server tools, sadly this is a hobby project with 0 budget :-(

Answer 1

Not sure about a readymade tool, but it's easy enough to do with the SMO library (Microsoft.SqlServer.Smo, .SmoEnum, .SqlEnum):

using Microsoft.SqlServer.Management.Smo;

var server = new Server("localhost");
var database = server.Databases["databaseName"];
var transfer = new Transfer(database);
var options = new ScriptingOptions();
// set transfer and options object properties to reflect what you want to script:
// i.e. all tables, indexes, triggers, etc.
options.FileName = "c:\\temp\\databaseName_schema.sql";
transfer.Options = options;
transfer.ScriptTransfer();

I built a simple tool using this method to regenerate my product's database creation script as part of the pre-build steps in the setup builder.

Answer 2

Just out of curiosity have you tried using SQL Server Projects within Visual Studio?

One other way to do this is through SQL scripts as I'm sure you're aware of. The generate script command can be made to run in command line I think.