SMO - Working with tables

In SMO, tables are represented by the Table object. In SMO objects hierarchy, the Table object is below the Database object.

Enumerating tables and columns
This example demonstrates how to enumerate all the tables and columns of the AdventureWorks database.

Server srv = new Server(conn);
Database db = srv.Databases["AdventureWorks"];

foreach (Table table in db.Tables)
{
    Console.WriteLine(" " + table.Name);
    foreach (Column col in table.Columns)
    {
        Console.WriteLine("  " + col.Name + " " + col.DataType.Name);
    }
}

Creating and removing tables
The following example shows how to create a database table. It creates MyFirstSMOTable in the MyNewDatabase database. The table contains two columns (ID and Name) and a Primary Key index on the first column.

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

Table myFirstSMOTable = new Table(myNewDatabase, "MyFirstSMOTable ");

DataType dt = new DataType(SqlDataType.Int);
Column idColumn = new Column(myFirstSMOTable, "ID", dt);
idColumn.Nullable = false;
idColumn.Identity = true;
idColumn.IdentityIncrement = 1;
idColumn.IdentitySeed = 1;
myFirstSMOTable.Columns.Add(idColumn);

dt = new DataType(SqlDataType.VarChar, 50);
Column nameColumn = new Column(myFirstSMOTable, "Name", dt);
myFirstSMOTable.Columns.Add(nameColumn);

myFirstSMOTable.Create();

Index primaryKey = new Index(myFirstSMOTable, "PK_ID");
IndexedColumn indexedColumn = new IndexedColumn(primaryKey, "ID");
primaryKey.IndexedColumns.Add(indexedColumn);
primaryKey.IndexKeyType = IndexKeyType.DriPrimaryKey;
primaryKey.Create();

In this example, the Table object is instantiated and two parameters are passed into the constructor of this class. The first parameter is a database represented by the myNewDatabase object of the Database class. The second parameter is the name of the table to be created. The columns of the table are created by instantiating the Column object. Before creating a column object, it is important to define the data type of the newly created column. The data type is represented by the DataType object. Next, this DataType object is passed into the constructor of the Column object. The constructor of the Column object has two other parameters: table and name of column.

In this example, two columns are created and a Primary Key index. For creating Primary Key index, serves the Index object with two parameters in the constructor. First is the table and the second, the ID name of the index. The IndexedColumn object is important to be created with two parameters in the constructor (index and name of column).

Checking table integrity
The following example demonstrates how to check the integrity of all the tables and their indexes in the database. In this example, we call the method CheckIntegrity() with a single parameter. This parameter specifies the RepairType.

Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
StringCollection sc = database.CheckTables(RepairType.None);
foreach (object o in sc)
{
    Console.WriteLine(o.ToString());
}