Sql-server – Auto-generate scripts to create tables, indexes, etc

indexsql-server-2008ssms

In SSMS, we can right click a database/table/index/… and select the "Script As" option to generate a drop and create script.

Is there a way to automate this script generation and drop the scripts to a temp location, rather than manually right clicking each object and generating them?

Best Answer

SQL Server Management Objects SMO is your answer. You can use it to accomplish this task. Here is an example to generate Create Table Scripts. 

    public string GetTableDescription(string pDatabaseName, string pSchemaName, string pTableName, string connectionString)
    {
        connectionString = ConnectionHelper.RemoveProviderFromConnectionString(connectionString);
        Server server = new Server(new ServerConnection(new SqlConnection(connectionString)));
        Database db = server.Databases[pDatabaseName];
        Table t = db.Tables[pTableName, pSchemaName];
        ScriptingOptions baseOptions = new ScriptingOptions();
        baseOptions.NoCollation = true;
        baseOptions.SchemaQualify = true;
        baseOptions.DriDefaults = true;
        baseOptions.IncludeHeaders = false;
        baseOptions.DriPrimaryKey = true;
        baseOptions.ExtendedProperties = true;
        //baseOptions.DriAll = true;
        //baseOptions.Indexes = true;
        //baseOptions.DriAllKeys = true;
        //baseOptions.SchemaQualifyForeignKeysReferences = true;
        baseOptions.EnforceScriptingOptions = true;
        StringCollection result = t.Script(baseOptions);
        return StringCreate(result);
    }
Related Question