Export (0) Print
Expand All

DataType.UserDefinedDataType Method (String)

Returns an object that represents the specified type.

Namespace:  Microsoft.SqlServer.Management.Smo
Assembly:  Microsoft.SqlServer.Smo (in Microsoft.SqlServer.Smo.dll)

public static DataType UserDefinedDataType(
	string type
)

Parameters

type
Type: String
A String value that specifies the type.

Return Value

Type: Microsoft.SqlServer.Management.Smo.DataType
A DataType object value.

The following code example shows how to create a table from user-defined data types.

C#

Server srv = new Server("(local)");
Database db = srv.Databases("AdventureWorks2012");
Schema schema1 = new Schema(db, "schema1");
schema1.Create();

UserDefinedDataType uddt = new UserDefinedDataType(db, "uddt");
uddt.SystemType = "int";
uddt.Create();

UserDefinedDataType uddt1 = new UserDefinedDataType(db, "uddt1");
uddt1.Schema = "schema1";
uddt1.SystemType = "int";
uddt1.Create();

UserDefinedDataType uddt2 = new UserDefinedDataType(db, "uddt2", "schema1");
uddt2.SystemType = "nvarchar";
uddt2.Length = 90;
uddt2.Create();

Table table = new Table(db, "mytable");
table.Columns.Add((new Column(table, "c1", Microsoft.SqlServer.Management.Smo.DataType.NVarChar(100))));
table.Columns.Add((new Column(table, "c2", Microsoft.SqlServer.Management.Smo.DataType.UserDefinedDataType("uddt"))));
Column c = new Column(table, "c3");
c.DataType = new DataType(SqlDataType.UserDefinedDataType, "uddt1", "schema1");
table.Columns.Add(c);
Column c1 = new Column(table, "c4");
c1.DataType = new DataType(uddt);
table.Columns.Add(c1);
DataType dt = new DataType(uddt2); 
Column c2 = new Column(table, "c5");
c2.DataType = dt;
table.Columns.Add(c2);
table.Create();

PowerShell

#Connect to the default, local server and open the AdventureWorks2012 database
$srv = new-object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = $srv.Databases.Item("AdventureWorks2012")

#Create the schema and a pair of user-defined data types
$schema1 = new-object Microsoft.SqlServer.Management.Smo.Schema($db, "schema1")
$schema1.Create()
$uddt = new-object Microsoft.SqlServer.Management.Smo.UserDefinedDataType($db, "uddt")
$uddt.SystemType = "int"
$uddt.Create()
$uddt1 = new-object Microsoft.SqlServer.Management.Smo.UserDefinedDataType($db, "uddt1")
$uddt1.schema = "schema1"
$uddt1.SystemType = "int"
$uddt1.Create()
$uddt2 = new-object Microsoft.SqlServer.Management.Smo.UserDefinedDataType($db, "uddt2")
$uddt2.SystemType = "nvarchar"
$uddt2.Length = 90
$uddt2.Create()

#Define the table columns
$table = new-object Microsoft.SqlServer.Management.Smo.Table($db, "mytable")
$c1 = new-object Microsoft.SqlServer.Management.Smo.Column($table, "c1", [Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(100))
$c2 = new-object Microsoft.SqlServer.Management.Smo.Column($table, "c2", [Microsoft.SqlServer.Management.Smo.DataType]::UserDefinedDataType("uddt"))
$c3 = new-object Microsoft.SqlServer.Management.Smo.Column($table, "c3")
$c3.DataType = new-object Microsoft.SqlServer.Management.Smo.DataType([Microsoft.SqlServer.Management.Smo.SqlDataType]::UserDefinedDataType, "uddt1", "schema1")
$c4 = new-object Microsoft.SqlServer.Management.Smo.Column($table, "c4")
$c4.DataType = new-object Microsoft.SqlServer.Management.Smo.DataType($uddt)
$dt = new-object Microsoft.SqlServer.Management.Smo.DataType($uddt2)
$c5 = new-object Microsoft.SqlServer.Management.Smo.Column($table, "c5")
$c5.DataType = $dt

#Add the columns and create the table
$table.Columns.Add($c1)
$table.Columns.Add($c2) 
$table.Columns.Add($c3)
$table.Columns.Add($c4)
$table.Columns.Add($c5)
$table.Create()

Community Additions

ADD
Show:
© 2014 Microsoft