Article Options
Premium Sponsor
Premium Sponsor

 »  Home  »  Data Programming  »  SQL Server  »  An introduction to SQL Server Management Objects  »  Creating and dropping tables
An introduction to SQL Server Management Objects
by David Jeavons | Published  01/02/2007 | SQL Server | Rating:
Creating and dropping tables

Creating a new table involves creating a Table object and adding new columns to the Table’s Columns collection. Once this is done, you can then call the table’s Create method to create the table on the specified database. The following example creates a basic table containing two columns (IDField and TextField):

    1         Dim db As Database = sqlServer.Databases("MyNewDatabase")

    2         Dim tb As New Table(db, "MyNewTable")

    3 

    4         Dim newColumn As New Column(tb, "IDField", DataType.Int)

    5         tb.Columns.Add(newColumn)

    6 

    7         newColumn = New Column(tb, "TextField", DataType.Char(25))

    8         tb.Columns.Add(newColumn)

    9 

   10         tb.Create()

 

However, the code above does not specify whether the IDField is indexed nor does it specify if the IDField should be used as an Identity column. To do this, we first need to create the indexes and state that the IDField column will be used as an Identity column. The following is the above code with the added statements for creating the index:

 

    1         Dim db As Database = sqlServer.Databases("MyNewDatabase")

    2         Dim tb As New Table(db, "MyNewTable")

    3 

    4         Dim newColumn As New Column(tb, "IDField", DataType.Int)

    5         tb.Columns.Add(newColumn)

    6 

    7         newColumn.Identity = True

    8         newColumn.IdentitySeed = 1

    9         newColumn.IdentityIncrement = 1

   10 

   11         Dim pkIndex As New Index(tb, "IDFieldIndex")

   12         tb.Indexes.Add(pkIndex)

   13         pkIndex.IndexedColumns.Add(New IndexedColumn(pkIndex, newColumn.Name))

   14         pkIndex.IsUnique = True

   15         pkIndex.IndexKeyType = IndexKeyType.DriPrimaryKey

   16 

   17         newColumn = New Column(tb, "TextField", DataType.Char(25))

   18         tb.Columns.Add(newColumn)

   19 

   20         tb.Create()

 

To drop a table you simply call the Drop method of the table object you wish to delete:

 

    1         sqlServer.Databases("MyNewDatabase").Tables("MyNewTable").Drop()

Sponsored Links