creating-altering-and-removing-tables.md

title	Creating, Altering, and Removing Tables | Microsoft Docs
ms.custom
ms.date	06/13/2017
ms.prod	sql-server-2014
ms.reviewer
ms.technology
ms.topic	reference
topic_type	apiref
helpviewer_keywords	tables [SMO]
ms.assetid	ff0bcfff-812f-4999-b0c7-736a97804c2b
author	stevestein
ms.author	sstein
manager	craigg

Creating, Altering, and Removing Tables

In [!INCLUDEssNoVersion] Management Objects (SMO), tables are represented by the xref:Microsoft.SqlServer.Management.Smo.Table object. In the SMO object hierarchy, the xref:Microsoft.SqlServer.Management.Smo.Table object is below the xref:Microsoft.SqlServer.Management.Smo.Database object.

Example

To use any code example that is provided, you have to choose the programming environment, template, and language in which to create your application. For more information, see Create a Visual Basic SMO Project in Visual Studio .NET or 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.

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

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.

{  
            //Connect to the local, default instance of SQL Server.   
        Server srv;   
        srv = new Server();   
        //Reference the AdventureWorks2012 database.   
        Database db;   
        db = srv.Databases["AdventureWorks2012"];   
        //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();   
        }  

Creating, Altering, and Removing a Table in PowerShell

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.

#Load the assembly containing the objects used in this example  
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Types")  
  
# Set the path context to the local, default instance of SQL Server.  
CD \sql\localhost\default\Databases\  
  
#And the database object corresponding to AdventureWorks2012.  
$db = Get-Item AdventureWorks2012  
  
#Create a SMO Table  
$tb = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Table -argumentlist $db, "Test_Table"  
  
#Add various columns to the table.   
$Type = [Microsoft.SqlServer.Management.SMO.DataType]::NChar(50)  
$col1 =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"Name", $Type  
$col1.Collation = "Latin1_General_CI_AS"  
$col1.Nullable = $true  
$tb.Columns.Add($col1)  
  
$Type = [Microsoft.SqlServer.Management.SMO.DataType]::Int  
$col2 =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"ID", $Type  
$col2.Identity = $true  
$col2.IdentitySeed = 1  
$col2.IdentityIncrement = 1  
$tb.Columns.Add($col2)
  
$Type = [Microsoft.SqlServer.Management.SMO.DataType]::Real  
$col3 =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"Value", $Type  
$tb.Columns.Add($col3)
  
$Type = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime  
$col4 =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"Date", $Type  
$col4.Nullable = $false  
$tb.Columns.Add($col4)
  
#Create the table  
$tb.Create()  
  
$Type = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime  
$col5 =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"ExpiryDate", $Type  
$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()  

See Also

xref:Microsoft.SqlServer.Management.Smo.Table