Generate SQL Script from SMO Without CREATE TABLE Statement

17 views Asked by At

I'm trying to use SMO in C# to generate a script that contains all of the indexes and constraints on a SQL Server table. My problem is that when I use the code below, it always scripts out the CREATE TABLE statement. Even if I set the ScriptForCreateDrop option to false, it still creates the statement. Is there a way to generate the script so that it doesn't contain the CREATE TABLE statement?

public static string GenerateScript(string ServerName, string DatabaseName, string SchemaName, string TableName, bool ScriptDrops)
{
    var sb = new StringBuilder();
    string dbName = DatabaseName;

    Server srv = new Server(ServerName);

    // Reference the database.    
    Database db = srv.Databases[dbName];

    // Define a Scripter object and set the required scripting options.   
    Scripter scrp = new Scripter(srv);
    scrp.Options.TargetServerVersion = SqlServerVersion.Version140;
    scrp.Options.SchemaQualify = true;
    scrp.Options.SchemaQualifyForeignKeysReferences = true;
    scrp.Options.NoCollation = true;
    scrp.Options.DriAllConstraints = true;
    scrp.Options.ColumnStoreIndexes = true;
    scrp.Options.DriAll = true;
    scrp.Options.DriAllKeys = true;
    scrp.Options.DriIndexes = true;
    scrp.Options.Indexes = true;                 
    scrp.Options.ScriptBatchTerminator = true;

    Table tbl = db.Tables[TableName, SchemaName];
 
    // Generate script for table  
    StringCollection sc = scrp.Script(new Urn[] { tbl.Urn });
    foreach (string st in sc)
    {
         sb.AppendLine(st);
    }

    sb.AppendLine("GO");
   
    return sb.ToString();
}
0

There are 0 answers