Create table with SMO

  • Article

Once you are connected to SQL Server 2005 with a SMO connection, you can start manage and create object. Here is an example on how you can create a table with a promary key (this sample is written in C# and use a beta version of SQL Server 2005 and of the .Net Framework 2.0).

The Object sqlserver, is the object created after the connection in this post about smo connection

Of course, In order to connect to SQL Server using SMO you have to reference the following namespace (and the corresponding assembly):

using

Microsoft.SqlServer.Management.Smo;

using

Microsoft.SqlServer.Management.Smo.Agent;

using

Microsoft.SqlServer.Management.Common;

 

And the code to create the table is :

Table tbl;

Column col;

Index idx;

tbl =

new Table(sqlserver.database, "MyTable");

col =

new Column(tbl, "MyCol1", DataType.Int);

tbl.Columns.Add(col);

col.Nullable =

false;

// Add the primary key index

idx =

new Index(tbl, "PK_MyTable");

tbl.Indexes.Add(idx);

idx.IndexedColumns.Add(

new IndexedColumn(idx, col.Name));

idx.IsClustered =

true;

idx.IsUnique =

true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

col =

new Column(tbl, "myCol2", DataType.SmallInt);

tbl.Columns.Add(col);

col.Nullable =

false;

// Create the table

tbl.Create();