Creating, Altering, and Removing Tables

Creating, Altering, and Removing Ta...

Community Content

In this section

Statistics Annotations (1)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (November 2009)

Creating, Altering, and Removing Tables

In SQL Server Management Objects (SMO), tables are represented by the Table object. In the SMO object hierarchy, the Table object is below the Database object.

Example

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see How to: Create a Visual Basic SMO Project in Visual Studio .NET or How to: Create a Visual C# SMO Project in Visual Studio .NET.

Creating, Altering, and Removing a Table in Visual Basic

This 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.

Visual Basic

'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
tb.Columns.Add(col1)
Dim col2 As Column
col2 = New Column(tb, "ID", DataType.Int)
col2.Identity = True
col2.IdentitySeed = 1
col2.IdentityIncrement = 1
tb.Columns.Add(col2)
Dim col3 As Column
col3 = New Column(tb, "Value", DataType.Real)
tb.Columns.Add(col3)
Dim col4 As Column
col4 = New Column(tb, "Date", DataType.DateTime)
col4.Nullable = False
tb.Columns.Add(col4)
'Create the table on the instance of SQL Server.
tb.Create()
'Add another column.
Dim col5 As Column
col5 = New Column(tb, "ExpiryDate", DataType.DateTime)
col5.Nullable = False
tb.Columns.Add(col5)
'Run the Alter method to make the change on the instance of SQL Server.
tb.Alter()
'Remove the table from the database.

tb.Drop()

Creating, Altering, and Removing a Table in Visual C#

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

Reference

Table

Help and Information

Getting SQL Server 2008 Assistance