Using User-Defined Tables
User-defined tables represent tabular information. They are used as parameters when you pass tabular data into stored procedures or user-defined functions. User-defined tables cannot be used to represent columns in a database table.
The Database object has a UserDefinedTableTypes property that references a UserDefinedTableTypeCollection object. Each UserDefinedTableType object in that collection has a Microsoft.SqlServer.Management.Smo.FileGroup.Columns property that refers to a collection of Column objects that list the columns in the user-defined table. Use the Add method to add columns to the user-defined table.
When you define a new user-defined table by using the UserDefinedTableType object, you will have to supply columns and a primary key based on one of the columns.
User-defined table types cannot be altered after they are created. The UserDefinedTableType does not support the Alter method. User-defined table types can have check constraints, but some check operations will throw an exception because the type is not alterable.
The Microsoft.SqlServer.Management.Smo.Datatype class is used to specify the data type that is associated with columns and parameters. Use this type to specify the user-defined table type as a parameter for user-defined functions and stored procedures.
Examples
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" in SQL Server Books Online.
Creating a User Defined Table in Visual Basic
For this example, you will have to include an imports statement for the class library that contains the StringCollection type.
Imports System.Collections.Specialized
The example demonstrates how to create a user-defined table, and then how to use it as a parameter in a user-defined function.
'Connect to the local, default instance of SQL Server
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 database.
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'Define a UserDefinedTableType object variable by supplying the 'database and name in the constructor.
Dim udtt As UserDefinedTableType
udtt = New UserDefinedTableType(db, "My_User_Defined_Table")
'Add three columns of different types to the
'UserDefinedTableType object.
udtt.Columns.Add(New Column(udtt, "Col1", DataType.Int))
udtt.Columns.Add(New Column(udtt, "Col2", DataType.VarCharMax))
udtt.Columns.Add(New Column(udtt, "Col3", DataType.Money))
'Define an Index object variable by supplying the user-defined
'table variable and name in the constructor.
Dim idx As Index
idx = New Index(udtt, "PK_UddtTable")
'Add the first column in the user-defined table as
'the indexed column.
idx.IndexedColumns.Add(New IndexedColumn(idx, "Col1"))
'Specify that the index is a clustered, unique, primary key.
idx.IsClustered = True
idx.IsUnique = True
idx.IndexKeyType = IndexKeyType.DriPrimaryKey
udtt.Indexes.Add(idx)
'Add the index and create the user-defined table.
udtt.Create()
'Display the Transact-SQL creation script for the
'user-defined table.
Dim sc As StringCollection
sc = udtt.Script()
For Each c As String In sc
Console.WriteLine(c)
Next
'Define a new user-defined function with a single parameter.
Dim udf As UserDefinedFunction
udf = New UserDefinedFunction(db, "My_User_Defined_Function")
udf.TextMode = False
udf.FunctionType = UserDefinedFunctionType.Scalar
udf.ImplementationType = ImplementationType.TransactSql
udf.DataType = DataType.DateTime
'Specify the parameter as a UserDefinedTableTable object.
Dim udfp As UserDefinedFunctionParameter
udfp = New UserDefinedFunctionParameter(udf, "@param")
udfp.DataType = New DataType(udtt)
udfp.IsReadOnly = True
udf.Parameters.Add(udfp)
'Specify the TextBody property to the Transact-SQL definition of the
'user-defined function.
udf.TextBody = "BEGIN RETURN (GETDATE());end"
'Create the user-defined function.
udf.Create()
Creating a User Defined Table in Visual C#
For this example, you will have to include an imports statement for the class library that contains the StringCollection type.
using System.Collections.Specialized;
The example shows how to create a user-defined table, and then how to use it as a parameter in a user-defined function.
{
//Connect to the local, default instance of SQL Server
Server srv = new Server();
//Reference the AdventureWorks2012 database.
Database db = srv.Databases["AdventureWorks2012"];
//Define a UserDefinedTableType object variable by supplying the
//database and name in the constructor.
UserDefinedTableType udtt = new UserDefinedTableType(db, "My_User_Defined_Table");
//Add three columns of different types to the
//UserDefinedTableType object.
udtt.Columns.Add(new Column(udtt, "Col1", DataType.Int));
udtt.Columns.Add(new Column(udtt, "Col2", DataType.VarCharMax));
udtt.Columns.Add(new Column(udtt, "Col3", DataType.Money));
//Define an Index object variable by supplying the user-defined
//table variable and name in the constructor.
Index idx = new Index(udtt, "PK_UddtTable");
//Add the first column in the user-defined table as
//the indexed column.
idx.IndexedColumns.Add(new IndexedColumn(idx, "Col1"));
//Specify that the index is a clustered, unique, primary key.
idx.IsClustered = true;
idx.IsUnique = true;
idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
udtt.Indexes.Add(idx);
//Add the index and create the user-defined table.
udtt.Create();
//Display the Transact-SQL creation script for the
//user-defined table.
System.Collections.Specialized.StringCollection sc;
sc = udtt.Script();
foreach (string s in sc)
{
Console.WriteLine(s);
}
//Define a new user-defined function with a single parameter.
UserDefinedFunction udf = new UserDefinedFunction(db, "My_User_Defined_Function");
udf.TextMode = false;
udf.FunctionType = UserDefinedFunctionType.Scalar;
udf.ImplementationType = ImplementationType.TransactSql;
udf.DataType = DataType.DateTime;
//Specify the parameter as a UserDefinedTableTable object.
UserDefinedFunctionParameter udfp = new UserDefinedFunctionParameter(udf, "@param");
udfp.DataType = new DataType(udtt);
udfp.IsReadOnly = true;
udf.Parameters.Add(udfp);
//Specify the TextBody property to the Transact-SQL definition of the
//user-defined function.
udf.TextBody = "BEGIN RETURN (GETDATE());end";
//Create the user-defined function.
udf.Create();
}
Creating a User Defined Table in PowerShell
For this example, you will have to include an imports statement for the class library that contains the StringCollection type.
using System.Collections.Specialized;
The example shows how to create a user-defined table, and then how to use it as a parameter in a user-defined function.
# Set the path context to the local, default instance of SQL Server and get a reference to AdventureWorks2012
CD \sql\localhost\default\databases
$db = get-item Adventureworks2012
#Define a UserDefinedTableType object variable by supplying the
#database and name in the constructor.
$udtt = New-Object -TypeName Microsoft.SqlServer.Management.SMO.UserDefinedTableType `
-argumentlist $db, "My_User_Defined_Table"
#Add three columns of different types to the UserDefinedTableType object.
$type = [Microsoft.SqlServer.Management.SMO.DataType]::Int
$col = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column `
-argumentlist $udtt, "col1",$type
$udtt.Columns.Add($col)
$type = [Microsoft.SqlServer.Management.SMO.DataType]::VarCharMax
$col = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column `
-argumentlist $udtt, "col2",$type
$udtt.Columns.Add($col)
$type = [Microsoft.SqlServer.Management.SMO.DataType]::Money
$col = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column `
-argumentlist $udtt, "col3",$type
$udtt.Columns.Add($col)
#Define an Index object variable by supplying the user-defined
#table variable and name in the constructor.
$idx = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index `
-argumentlist $udtt, "PK_UddtTable"
#Add the first column in the user-defined table as
#the indexed column.
$idxcol = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn `
-argumentlist $idx, "Col1"
$idx.IndexedColumns.Add($idxcol)
#Specify that the index is a clustered, unique, primary key.
$idx.IsClustered = $true
$idx.IsUnique = $true
$idx.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::DriPrimaryKey;
#Add the index and create the user-defined table.
$udtt.Indexes.Add($idx)
$udtt.Create();
# Display the Transact-SQL creation script for the
# user-defined table.
$sc = $udtt.Script()
$sc
# Define a new user-defined function with a single parameter.
$udf = New-Object -TypeName Microsoft.SqlServer.Management.SMO.UserDefinedFunction `
-argumentlist $db, "My_User_Defined_Function"
$udf.TextMode = $false
$udf.FunctionType = [Microsoft.SqlServer.Management.SMO.UserDefinedFunctionType]::Scalar
$udf.ImplementationType = [Microsoft.SqlServer.Management.SMO.ImplementationType]::TransactSql
$udf.DataType = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime
# Specify the parameter as a UserDefinedTableTable object.
$udfp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.UserDefinedFunctionParameter `
-argumentlist $udf, "@param"
$type = New-Object -TypeName Microsoft.SqlServer.Management.SMO.DataType `
-argumentlist $udtt
$udfp.DataType = $type
$udfp.IsReadOnly = $true
$udf.Parameters.Add($udfp)
# Specify the TextBody property to the Transact-SQL definition of the
# user-defined function.
$udf.TextBody = "BEGIN RETURN (GETDATE());end"
# Create the user-defined function.
$udf.Create()