ansaurus

Question

Free Schema Export for SQL Server 2008?

Answer 1

+2 A:

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.

Paulo Santos 2009-12-17 05:19:32

I thought there were only in VS Team System Database Edition, but I also found a "Database Project" in Visual Studio 2008 Professional. I'll look at that, thanks for the heads up!

Michael Stum 2009-12-17 06:28:05

There is no "Generate Script" command in T-SQL, nor, AFAIK in SQLCMD. It's only a menu/button task in Management Studio (which calls SMO to do it). It can, however, be scripted from Powershell, (SQL 2008) which may be what you were thinking of.

RBarryYoung 2009-12-17 15:50:02

Answer 2

+3 A:

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.

Ben M 2009-12-17 05:20:28

FYI: having written several of these myself, I can tell that you are glossing over the hard part, which is setting the options correctly. This is especially so since many of the options are specific to the particular SQL-type (tables, triggers, etc.) and will frequently throw exceptions if they are left on for types that they do not apply to.

RBarryYoung 2009-12-17 15:45:16

Not sure what you mean, RBY. The options I refer to are all simple booleans: `CopyAllDatabaseTriggers`, `CopyAllTables`, `IncludeDatabaseContext`, `WithDependencies`, etc. There are a number of them, true, but it's a simple matter of setting most to `true`, the usual exceptions being security objects like logins and users, which depending on the source database may contain entries unwanted in the final script.

Ben M 2009-12-17 17:58:07

Answer 3

+1 A:

There are several free command-line tools like this at CodePlex. One of them is Scriptio, here.

RBarryYoung 2009-12-17 06:23:49

Answer 4

A:

You can always use SQL Server Management Studio to do it, right click the database, select 'Tasks' and then 'Generate scripts'.

Jonas Lincoln 2009-12-17 07:25:35

ansaurus

tags:

views:

answers:

Free Schema Export for SQL Server 2008?

related questions