SMO - Schemas

In SQL Server 2005, Microsoft introduced the concept of database schemas as opposed to object owners. A schema is a container of objects distinct from the users who created those objects.

Schema in SMO is represented by the Schema object. The Schema object represents an ownership context for database objects.

Enumerating schemas
You can enumerate all schemas of a database using the Schemas property of the Database object. The Schemas property is an ordinary collection of Schema objects.

Database myNewDatabase = srv.Databases["myNewDatabase"];
foreach (Schema schema in myNewDatabase.Schemas)
{
    Console.WriteLine(schema.Name);
}

Creating schemas
The following example demonstrates how to create a new database table named MySecondSMOTable in the newly created NewSchema schema:

Database myNewDatabase = srv.Databases["myNewDatabase"];

Schema newSchema = new Schema(myNewDatabase, "NewSchema");
newSchema.Owner = "dbo";
newSchema.Create();

Table mySecondSMOTable = new Table(myNewDatabase, 
      "MySecondSMOTable","NewSchema");

DataType dt = new DataType(SqlDataType.Int);
Column idColumn = new Column(mySecondSMOTable, "ID", dt);
idColumn.Nullable = false;
idColumn.Identity = true;
idColumn.IdentityIncrement = 1;
idColumn.IdentitySeed = 1;
mySecondSMOTable.Columns.Add(idColumn);
dt = new DataType(SqlDataType.VarChar, 50);
Column nameColumn = new Column(mySecondSMOTable, "Name", dt);
mySecondSMOTable.Columns.Add(nameColumn);
mySecondSMOTable.Create();