How to: Create, Alter, and Remove a Table in Visual Basic .NET

This section describes how to create, alter, and remove a table in Visual Basic .NET.

The code example creates a table that has several columns with different types and purposes. The code also provides examples of how to create an identity field, how to create a primary key, and how to alter table properties.

Creating, altering, and removing tables

  1. Start Visual Studio 2005.

  2. From the File menu, select New Project. The New Project dialog box appears.

  3. In the Project Types pane, select Visual Basic. In the Templates pane, select Console Application.

  4. (Optional) In the Name box, type the name of the new application.

  5. Click OK to load the Visual Basic console application template.

  6. On the Project menu, select Add Reference item. The Add Reference dialog box appears. Select Browse and locate the SMO assemblies in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder. Select the following files:





  7. On the View menu, click Code.-Or-Select the Module1.vb window to display the code window.

  8. In the code, before any declarations, type the following Imports statements to qualify the types in the SMO namespace:

    Imports Microsoft.SqlServer.Management.Smo
    Imports Microsoft.SqlServer.Management.Common
  9. Insert the code that follows this procedure into the main program.

  10. Run and build the application.


'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Table object variable by supplying the parent database and table name in the constructor. 
Dim tb As Table
tb = New Table(db, "Test_Table")
'Add various columns to the table.
Dim col1 As Column
col1 = New Column(tb, "Name", DataType.NChar(50))
col1.Collation = "Latin1_General_CI_AS"
col1.Nullable = True
Dim col2 As Column
col2 = New Column(tb, "ID", DataType.Int)
col2.Identity = True
col2.IdentitySeed = 1
col2.IdentityIncrement = 1
Dim col3 As Column
col3 = New Column(tb, "Value", DataType.Real)
Dim col4 As Column
col4 = New Column(tb, "Date", DataType.DateTime)
col4.Nullable = False
'Create the table on the instance of SQL Server.
'Add another column.
Dim col5 As Column
col5 = New Column(tb, "ExpiryDate", DataType.DateTime)
col5.Nullable = False
'Run the Alter method to make the change on the instance of SQL Server.
'Remove the table from the database.


See Also


Creating, Altering, and Removing Tables

Help and Information

Getting SQL Server 2005 Assistance