Generate database creation scripts

Question

Is it possible to generate the database creation scripts for a SQL server database from .NET?

I am using C# and I would like to create some sort of an installer project for my application on which I can select an existing database, generate the creation scripts and run them on another SQL server instance.

Answer 1

Yes, it is possible. It's easy to do this with SMO, see Transfer class for scripting operations and Database class for database operations (create, drop, etc). Usage looks like this:

    private StringCollection GetTransferScript(Database database)
    {
        var transfer = new Transfer(database);

        transfer.CopyAllObjects = true;
        transfer.CopyAllSynonyms = true;
        transfer.CopyData = false;

        // additional options
        transfer.Options.WithDependencies = true;
        transfer.Options.DriAll = true;
        transfer.Options.Triggers = true;
        transfer.Options.Indexes = true;
        transfer.Options.SchemaQualifyForeignKeysReferences = true;
        transfer.Options.ExtendedProperties = true;
        transfer.Options.IncludeDatabaseRoleMemberships = true;
        transfer.Options.Permissions = true;
        transfer.PreserveDbo = true;

        // generates script
        return transfer.ScriptTransfer();
    }