public void uses_correct_literal_type()
{
var table = _model.GetObject <TSqlTable>(new ObjectIdentifier("dbo", "TheTable"), DacQueryScopes.UserDefined);
Assert.IsNotNull(table);
var descriptor = new TableDescriptor(table);
Assert.AreEqual(LiteralType.Integer, descriptor.Columns.First().DataType);
}
public void TestTableType()
{
using (TSqlTypedModel model = new TSqlTypedModel(SqlServerVersion.Sql120, new TSqlModelOptions()
{
}))
{
model.AddObjects(@"CREATE TYPE [dbo].[UserDefinedTableType1] AS TABLE
(
Id INT PRIMARY KEY,
Name VARCHAR(128) CHECK( Id <> ' '),
MI char(1) DEFAULT (' '),
UniqueValue int UNIQUE,
ComputedColumn AS ( Name + ' ' + MI + ' ' + UniqueValue),
INDEX IX_1 (Name)
)");
var tableType1 = model.GetObject <TSqlTableType>(
new ObjectIdentifier("dbo", "UserDefinedTableType1"), DacQueryScopes.UserDefined);
Assert.IsNotNull(tableType1, "table type should not be null");
Assert.AreEqual(4, tableType1.Constraints.Count(), "Incorrect number of constraints");
Assert.AreEqual(1, tableType1.DefaultConstraints.Count(), "Incorrect number of Default constraints");
Assert.AreEqual(1, tableType1.CheckConstraints.Count(), "Incorrect number of Check constraints");
Assert.AreEqual(1, tableType1.PrimaryKeyConstraints.Count(), "Incorrect number of Primary Key constraints");
Assert.AreEqual(1, tableType1.UniqueConstraints.Count(), "Incorrect number of Unique Key constraints");
Assert.AreEqual(1, tableType1.Indexes.Count(), "Incorrect number of indexes");
Assert.AreEqual(5, tableType1.Columns.Count(), "Incorrect number of columns");
TSqlTableTypeColumn computedColumn = tableType1.Columns
.SingleOrDefault(c => c.Name.Parts[2] == "ComputedColumn");
Assert.IsNotNull(computedColumn, "computed column ComputedColumn missing");
Assert.AreEqual(3, computedColumn.ExpressionDependencies.Count(), "incorrect number of dependencies");
}
}
public void TestTSqlDataType()
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions()
{
}))
{
model.AddObjects(@"
CREATE PARTITION FUNCTION [pf1]
(
int
)
AS RANGE LEFT
FOR VALUES (1,100,1000)
");
model.AddObjects(@"
");
TSqlTypedModel typedModel = new TSqlTypedModel(model);
TSqlPartitionFunction function = typedModel.GetObject <TSqlPartitionFunction>(
new ObjectIdentifier("pf1"), DacQueryScopes.UserDefined);
foreach (var parameterType in function.ParameterType)
{
Assert.AreEqual(SqlDataType.Int, parameterType.SqlDataType, "DataType on partition function is not correct");
}
var boundaryValues = function.BoundaryValues.ToList();
Assert.AreEqual(3, boundaryValues.Count, "Incorrect number of boundary values");
Assert.AreEqual("1", boundaryValues[0].Expression, "incorrect boundary value");
Assert.AreEqual("100", boundaryValues[1].Expression, "incorrect boundary value");
Assert.AreEqual("1000", boundaryValues[2].Expression, "incorrect boundary value");
}
}
public void gets_table_name()
{
var table = _model.GetObject <TSqlTable>(new ObjectIdentifier("dbo", "TheTable"), DacQueryScopes.UserDefined);
Assert.IsNotNull(table);
var descriptor = new TableDescriptor(table);
Assert.AreEqual("dbo", descriptor.Name.GetSchema());
Assert.AreEqual("TheTable", descriptor.Name.GetName());
}
public void TestIndexIncludedColumns()
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions()
{
}))
{
model.AddObjects(@"CREATE TABLE [dbo].[Table1]
(
[Id] INT NOT NULL PRIMARY KEY,
col1 int not null,
[c] AS (Id + ' ')
);
GO
CREATE INDEX ix_1 on Table1 (c) INCLUDE ( col1)
");
TSqlTypedModel typedModel = new TSqlTypedModel(model);
var index = typedModel.GetObject <TSqlIndex>(new ObjectIdentifier("dbo", "Table1", "ix_1"), DacQueryScopes.UserDefined);
Assert.IsNotNull(index, "Index should not be null");
Assert.AreEqual(1, index.IncludedColumns.Count(), "Incorrect number of included columns");
}
}
public void TestColumnUserDefinedTypes()
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
{
model.AddObjects(@"
CREATE ASSEMBLY [SampleUDT]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300004BFF550000000000000000E00002210B010B00000800000006000000000000BE270000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000682700005300000000400000A802000000000000000000000000000000000000006000000C000000302600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C4070000002000000008000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000A02700000000000048000000020005000C210000240500000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133001000B00000001000011007E0700000A0A2B00062A00133001000C0000000200001100027B020000040A2B00062A133002001700000003000011001200FE15020000021200177D02000004060B2B00072A00133002002500000004000011000F00280800000A16FE010C082D0828030000060B2B0C1200FE1502000002060B2B00072A000000133001000B00000001000011007E0700000A0A2B00062A00133001001000000005000011007201000070730900000A0A2B00062A42534A4201000100000000000C00000076342E302E33303331390000000005006C000000E8010000237E0000540200001002000023537472696E6773000000006404000004000000235553006804000010000000234755494400000078040000AC00000023426C6F6200000000000000020000015717A2010900000000FA253300160000010000000D0000000200000002000000060000000100000001000000090000000400000005000000010000000200000002000000010000000200000000000A0001000000000006003C0035000A00670052000A008E0052000600DB00C8001300EF00000006002401040106004401040106006C0135000A009D0182010A00B90182010600DF01C0010600F501C001060000023500000000000100000000000100010009211000180000000500010001000600AE0023000100B4002600502000000000C60071000A000100682000000000E6097A000E0001008020000000009608850012000100A420000000009600980017000100D8200000000086009E000A000200F020000000009600A6001E00020000000100C600020009002100FE0032003100FE0038003900FE003D004100FE003D004900FE0041005900FE00500069000702560019007A000E001900FE0070002E000B007A002E00130083002E001B008C0043002B00470059005D00610068007500020001000000BA0029000000C1002D000200020003000200030005000480000000000000000000000000000000006201000004000000000000000000000001002C0000000000040000000000000000000000010046000000000000000000003C4D6F64756C653E0053616D706C655544542E646C6C0053716C55736572446566696E65645479706531006D73636F726C69620053797374656D0056616C7565547970650053797374656D2E446174610053797374656D2E446174612E53716C547970657300494E756C6C61626C6500546F537472696E67006765745F49734E756C6C006765745F4E756C6C0053716C537472696E67005061727365004D6574686F6431004D6574686F6432005F76617231005F6E756C6C0049734E756C6C004E756C6C00730053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F646573002E63746F720053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053616D706C655544540053657269616C697A61626C65417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C55736572446566696E65645479706541747472696275746500466F726D61740053797374656D2E52756E74696D652E496E7465726F705365727669636573005374727563744C61796F7574417474726962757465004C61796F75744B696E6400537472696E6700456D7074790000000000010000F29E39299A1EBD40A56A399A5D1E10610008B77A5C561934E0890320000E0320000204000011080600011108110D040000110D02060802060203280002040800110805200101111504200101080320000105200101112908010001000000000005200101113102060E0307010E03070102060702110811080707031108110802042001010E040701110D0801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000004BFF5500000000020000001C0100004C2600004C08000052534453DFC29DC30B515C4ABFC3FD3DBB7A048801000000643A5C75736572735C6C6F6E6E79625C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C53616D706C655544545C53616D706C655544545C6F626A5C44656275675C53616D706C655544542E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000902700000000000000000000AE270000002000000000000000000000000000000000000000000000A027000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D0065000000530061006D0070006C0065005500440054002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530061006D0070006C0065005500440054002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000C03700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;
GO
ALTER ASSEMBLY [SampleUDT]
DROP FILE ALL
ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A445300000000020000020000001B000000840000000000000018000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF380000FEFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF0BCA3101380000000010000000100000000000000E00FFFF04000000FFFF03000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000BCA3101380000000010000000100000000000000F00FFFF04000000FFFF0300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000942E3101004BFF5501000000DFC29DC30B515C4ABFC3FD3DBB7A048800000000000000000100000001000000000000000000000000000000DC51330100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000BCA310138000000001000000010000000000000FFFFFFFF04000000FFFF030000000000FFFFFFFF00000000FFFFFFFF00000000FFFFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000BCA310138000000001000000010000000000000FFFFFFFF04000000FFFF030000000000FFFFFFFF00000000FFFFFFFF00000000FFFFFFFF000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F862513FC607D311905300C04FA302A1C4454B99E9E6D211903F00C04FA302A10B9D865A1166D311BD2A0000F80849BD60A66E40CF64824CB6F042D48172A7991000000000000000A2EEC7934A3D37151FD88F759E2C495E0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006E000000000000006E000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FEEFFEEF01000000C400000000643A5C75736572735C6C6F6E6E79625C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C53616D706C655544545C53616D706C655544545C53716C55736572446566696E656454797065312E63730000643A5C75736572735C6C6F6E6E79625C646F63756D656E74735C76697375616C2073747564696F20323031335C70726F6A656374735C73616D706C657564745C73616D706C657564745C73716C75736572646566696E656474797065312E637300040000006200000001000000630000000000000003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001BE2300180000000D74A34F301F4D001010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000200000001000000020000000000000063000000280000001BE23001B5D25C0C580000000100000062000000630000006500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000400000032002A11000000001C010000000000000B000000000000000000000001000006000000000100000000546F537472696E670000001600031104000000E80000000B00000000000000010000000A0024115553797374656D00120024115553797374656D2E44617461000000001A0024115553797374656D2E446174612E53716C436C69656E7400001A0024115553797374656D2E446174612E53716C54797065730000001E002411554D6963726F736F66742E53716C5365727665722E536572766572001E00201100000000010000110000000000000400435324312430303030000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040000000C000000010005000200060032002A1100000000C0010000000000000C0000000000000000000000020000060B00000001000000006765745F49734E756C6C0016000311200100008C0100000C0000000B000000010000001E00201100000000020000110000000000000400435324312430303030000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040172000C000000010000060200060032002A11000000007C02000000000000170000000000000000000000030000061700000001000000006765745F4E756C6C00000016000311C4010000480200001700000017000000010000001E00201101000000030000110000000000000400435324312430303030000000160020110000000003000011000000000000000068000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040186000C00000001000006020006002E002A11000000005403000000000000250000000000000000000000040000062E000000010000000050617273650000160003118002000020030000250000002E000000010000001E002011010000000400001100000000000004004353243124303030300000001E00201102000000040000110000000000000400435324342430303031000000160020110000000004000011000000000000000075000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040186000C000000010000060200060032002A1100000000F8030000000000000B0000000000000000000000050000065300000001000000004D6574686F6431000000001600031158030000C40300000B00000053000000010000001E00201100000000010000110000000000000400435324312430303030000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040195760C000000010000060200060032002A11000000009C04000000000000100000000000000000000000060000065E00000001000000004D6574686F64320000000016000311FC03000068040000100000005E000000010000001E00201100000000050000110000000000000400435324312430303030000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040195760C0000000100000602000600F20000003C00000000000000010001000B000000000000000300000030000000000000000D000080010000000F00008009000000100000800500060009001D0005000600F20000003C0000000B000000010001000C000000000000000300000030000000000000001500008001000000170000800A0000001800008009000A000D001A0009000A00F200000054000000170000000100010017000000000000000500000048000000000000001E000080010000001F00008009000000200000801100000021000080150000002200008009000A000D003F000D001C000D00160009000A00F20000006C0000002E0000000100010025000000000000000700000060000000000000002600008001000000270000800C000000EEEFFE800F0000002800008017000000290000801F0000002B000080230000002C0000800500060009001600000000000D00190009003B000900120005000600F20000003C00000053000000010001000B0000000000000003000000300000000000000030000080010000003200008009000000330000800500060009001D0005000600F20000003C0000005E0000000100010010000000000000000300000030000000000000003700008001000000390000800E0000003A000080050006000900220005000600F4000000080000000100000000000000300000000000000018000000300000004C000000640000007C00000094000000A8000000C0000000D8000000F00000000801000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFF1A092FF160000000340200004D000000010000007D000000010000000100000001000000190000000100000009010000010000006500000001000000A900000001000000D9000000010000003100000001000000F100000001000000C1000000010000009500000001000000010000000000000000000000000000000000000000000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000020000000000000000010000000000000000000000000000000000000000000000000000000000000001000200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000000000000000000000000000000000000000000000000000000000001000400000000000000000000000000000000000000000000600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0000001800000024000000300000003C0000004800000054000000600000006C00000078000000840000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001600251100000000040000000100546F537472696E6700001600291100000000040000000100303630303030303100001A002511000000002001000001006765745F49734E756C6C000000001600291100000000200100000100303630303030303200001600251100000000C401000001006765745F4E756C6C00001600291100000000C4010000010030363030303030330000120025110000000080020000010050617273650016002911000000008002000001003036303030303034000016002511000000005803000001004D6574686F64310000001600291100000000580300000100303630303030303500001600251100000000FC03000001004D6574686F64320000001600291100000000FC030000010030363030303030360000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000000000000000000000000000000000000000000000000000FFFFFFFF1A092FF10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFF77093101010000000B00008E0C004F000D00000060000000AC0000002C00000070000000000000000000000016000000190000000000EEC00000000000000000FFFF000000000000FFFFFFFF00000000FFFF0000000000000000000000000A00A004000000000000F001000001000000C02E8600000000000000000053716C55736572446566696E65645479706531003241393744463435000000002DBA2EF101000000000000000B00000000000000000000000000000000000000010000000B0000000C0000000000000000000000000000000000000001000000170000001700000000000000000000000000000000000000010000002E000000250000000000000000000000000000000000000001000000530000000B00000000000000000000000000000000000000010000005E0000001000000000000000000000000000000000000000020002000D01000000000100FFFFFFFF000000006E0000000802000000000000FFFFFFFF00000000FFFFFFFF010001000000010000000000643A5C75736572735C6C6F6E6E79625C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C53616D706C655544545C53616D706C655544545C53716C55736572446566696E656454797065312E637300000000FEEFFEEF010000000100000000010000000000000000000000FFFFFFFFFFFFFFFFFFFF0900FFFFFFFFFFFFFFFFFFFF000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000942E3101004BFF5501000000DFC29DC30B515C4ABFC3FD3DBB7A04888E0000002F4C696E6B496E666F002F6E616D6573002F7372632F686561646572626C6F636B002F7372632F66696C65732F643A5C75736572735C6C6F6E6E79625C646F63756D656E74735C76697375616C2073747564696F20323031335C70726F6A656374735C73616D706C657564745C73616D706C657564745C73716C75736572646566696E656474797065312E6373000400000006000000010000001B00000000000000220000000800000011000000070000000A00000006000000000000000500000000000000DC51330100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E00000020000000EA00000038000000170200003800000000000000E8000000800000005800000028000000C4060000A40200002C000000200100000300000016000000060000001400000015000000070000000A0000000B00000008000000090000000C0000000D0000000E0000000F000000100000001100000013000000120000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000170000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 AS N'SampleUDT.pdb';
GO
CREATE TYPE [dbo].[SqlUserDefinedType1]
EXTERNAL NAME [SampleUDT].[SqlUserDefinedType1];
GO
CREATE TYPE SSN
FROM varchar(11) NOT NULL;
GO
CREATE TABLE [dbo].[Table1]
(
[Id] INT NOT NULL PRIMARY KEY,
SSN SSN,
ClrUDT [dbo].[SqlUserDefinedType1] NULL
);");
TSqlTypedModel typedModel = new TSqlTypedModel(model);
var table = typedModel.GetObject<TSqlTable>(new ObjectIdentifier("dbo", "Table1"), DacQueryScopes.UserDefined);
Assert.IsNotNull(table, "table1 should not be null");
Assert.AreEqual(3, table.Columns.Count(), "Incorrect number of columns");
// validate columns to UserDefinedDataType and built-in types
ValidateColumnDataType(table, "Id", SqlDataType.Int, null);
ValidateColumnDataType(table, "SSN", SqlDataType.Unknown, SqlDataType.VarChar);
// Validate column with UserDefined types
TSqlColumn ssnColomn = table.Columns.SingleOrDefault(c => c.Name.Parts[2] == "ClrUDT");
Assert.IsNotNull(ssnColomn, "Missing ClrUDT Column");
var columnDataType = ssnColomn.DataType.SingleOrDefault() as TSqlUserDefinedTypeReference;
Assert.IsNotNull(columnDataType, "ClrUDT column data type should not be null");
Assert.AreEqual("UserDefinedType", columnDataType.ObjectType.Name);
}
}
public void TestTableValuedFunction()
{
string assemblyScript = @"CREATE ASSEMBLY [CLR_TVF]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103006D425E550000000000000000E00002210B010B000008000000060000000000002E270000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000D42600005700000000400000A002000000000000000000000000000000000000006000000C0000009C2500001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000034070000002000000008000000020000000000000000000000000000200000602E72737263000000A00200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E0000000000000000000000000000400000420000000000000000000000000000000010270000000000004800000002000500C0200000DC0400000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133004003600000001000011000214FE0116FE010C082D0A00168D0A0000010B2B1E02178D0B0000010D0916039D096F0600000A0A06280100002B00060B2B00072A00001330020016000000020000110002740A0000010A0306280800000A730900000A512A1E02280A00000A2A000042534A4201000100000000000C00000076342E302E33303331390000000005006C00000098010000237E000004020000E801000023537472696E677300000000EC0300000800000023555300F403000010000000234755494400000004040000D800000023426C6F620000000000000002000001471502000908000000FA253300160000010000000D0000000200000003000000040000000A000000040000000200000001000000020000000100000000000A00010000000000060037003000060051003E000A00890074000600D900BA000600F900E60017000D01000006003C011C0106005C011C010A009D0182010600B20130000600B90130000600C40130000A00CF0174000000000001000000000001000100010010001600000005000100010050200000000096005D000A0001009420000000009600920011000300B6200000000086189A001900050000000100A00000000200A60000000100B00002000200B40021009A00190029009A001D0039009A00230041009A00190049009A0019005100BE0178006100CA017F006900D901950019009A009B0009009A00190020002B0028002E001300A5002E001B00AE002E002300B7008B00A1000480000000000000000000000000000000007A010000040000000000000000000000010027000000000004000000000000000000000001006800000000000F00870000000000003C4D6F64756C653E00434C525F5456462E646C6C00546162756C61724576656E744C6F6742006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261626C6500496E69744D6574686F640053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730046696C6C526F77002E63746F7200696E70757400736570617261746F72006F626A0076616C75650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500434C525F545646004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500537472696E6700436861720053706C697400417272617900536F72740053716C537472696E67006F705F496D706C696369740000000000032000000000006A03DD2B8352F240A89BC91F5A7890600008B77A5C561934E08906000212090E03070002011C10120D0320000105200101111904200101084F01000200540E1146696C6C526F774D6574686F644E616D650D53706C6974416E644F72646572540E0F5461626C65446566696E6974696F6E165B76616C75655D206E766172636861722834303030290620011D0E1D0307100101011D1E00030A010E0907041D0E1209021D0305000111350E0520010111350307010E0801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000006D425E5500000000020000001C010000B8250000B807000052534453FC017B641174E84696DA41D68EB73EFA03000000633A5C55736572735C6C6F6E6E79625C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C434C52205456535C434C52205456535C6F626A5C44656275675C434C525F5456462E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FC26000000000000000000001E270000002000000000000000000000000000000000000000000000102700000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000440200000000000000000000440234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004A4010000010053007400720069006E006700460069006C00650049006E0066006F0000008001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000038000C00010049006E007400650072006E0061006C004E0061006D006500000043004C0052005F005400560046002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000040000C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043004C0052005F005400560046002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000303700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;";
string functionScript = @"
CREATE FUNCTION [dbo].[SplitAndOrder]
(@input NVARCHAR (MAX), @separator NCHAR (1))
RETURNS
TABLE (
[value] NVARCHAR (4000) NULL)
ORDER ([value])
AS
EXTERNAL NAME [CLR_TVF].[TabularEventLogB].[InitMethod]";
using (TSqlTypedModel model = new TSqlTypedModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
{
model.AddObjects(assemblyScript);
model.AddObjects(functionScript);
var function = model.GetObject<TSqlTableValuedFunction>(new ObjectIdentifier("dbo", "SplitAndOrder"), DacQueryScopes.UserDefined);
Assert.AreEqual(1, function.Columns.Count(), "Incorrect number of columns");
Assert.AreEqual(1, function.TableOption.First().OrderColumns.Count(), "Incorrect number of Order columns");
ClrTableOptionOrderColumnsReference orderColumn = function.TableOption.First().OrderColumns.First();
Assert.AreEqual("value", orderColumn.Name.Parts[2], "Incorrect number of Order columns");
Assert.AreEqual(true, orderColumn.Ascending, "Incorrect Ascending metadata value");
//validate Function Metadata property
Assert.AreEqual(FunctionType.MultiStatementTableValuedFunction, function.FunctionType, "Incorrect function type");
Assert.AreEqual(FunctionType.MultiStatementTableValuedFunction, function.GetMetadata<FunctionType>(TableValuedFunction.FunctionType), "Incorrect function type");
}
}
public void TestTableType()
{
using (TSqlTypedModel model = new TSqlTypedModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
{
model.AddObjects(@"CREATE TYPE [dbo].[UserDefinedTableType1] AS TABLE
(
Id INT PRIMARY KEY,
Name VARCHAR(128) CHECK( Id <> ' '),
MI char(1) DEFAULT (' '),
UniqueValue int UNIQUE,
ComputedColumn AS ( Name + ' ' + MI + ' ' + UniqueValue),
INDEX IX_1 (Name)
)");
var tableType1 = model.GetObject<TSqlTableType>(
new ObjectIdentifier("dbo", "UserDefinedTableType1"), DacQueryScopes.UserDefined);
Assert.IsNotNull(tableType1, "table type should not be null");
Assert.AreEqual(4, tableType1.Constraints.Count(), "Incorrect number of constraints");
Assert.AreEqual(1, tableType1.DefaultConstraints.Count(), "Incorrect number of Default constraints");
Assert.AreEqual(1, tableType1.CheckConstraints.Count(), "Incorrect number of Check constraints");
Assert.AreEqual(1, tableType1.PrimaryKeyConstraints.Count(), "Incorrect number of Primary Key constraints");
Assert.AreEqual(1, tableType1.UniqueConstraints.Count(), "Incorrect number of Unique Key constraints");
Assert.AreEqual(1, tableType1.Indexes.Count(), "Incorrect number of indexes");
Assert.AreEqual(5, tableType1.Columns.Count(), "Incorrect number of columns");
TSqlTableTypeColumn computedColumn = tableType1.Columns
.SingleOrDefault(c => c.Name.Parts[2] == "ComputedColumn");
Assert.IsNotNull(computedColumn, "computed column ComputedColumn missing");
Assert.AreEqual(3, computedColumn.ExpressionDependencies.Count(), "incorrect number of dependencies");
}
}
public void TestPartitionFunction()
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
{
model.AddObjects(@"
CREATE PARTITION FUNCTION [pf1]
(
int
)
AS RANGE LEFT
FOR VALUES (1,100,1000)
");
model.AddObjects(@"
");
TSqlTypedModel typedModel = new TSqlTypedModel(model);
TSqlPartitionFunction function = typedModel.GetObject<TSqlPartitionFunction>(
new ObjectIdentifier("pf1"), DacQueryScopes.UserDefined);
foreach (var parameterType in function.ParameterType)
{
Assert.AreEqual(SqlDataType.Int, parameterType.SqlDataType, "DataType on partition function is not correct");
}
var boundaryValues = function.BoundaryValues.ToList();
Assert.AreEqual(3, boundaryValues.Count, "Incorrect number of boundary values");
Assert.AreEqual("1", boundaryValues[0].Expression, "incorrect boundary value");
Assert.AreEqual("100", boundaryValues[1].Expression, "incorrect boundary value");
Assert.AreEqual("1000", boundaryValues[2].Expression, "incorrect boundary value");
}
}
public void TestParamterWithUserDefinedTypes()
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
{
model.AddObjects(@"
CREATE ASSEMBLY [SampleUDT]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300004BFF550000000000000000E00002210B010B00000800000006000000000000BE270000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000682700005300000000400000A802000000000000000000000000000000000000006000000C000000302600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C4070000002000000008000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000A02700000000000048000000020005000C210000240500000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133001000B00000001000011007E0700000A0A2B00062A00133001000C0000000200001100027B020000040A2B00062A133002001700000003000011001200FE15020000021200177D02000004060B2B00072A00133002002500000004000011000F00280800000A16FE010C082D0828030000060B2B0C1200FE1502000002060B2B00072A000000133001000B00000001000011007E0700000A0A2B00062A00133001001000000005000011007201000070730900000A0A2B00062A42534A4201000100000000000C00000076342E302E33303331390000000005006C000000E8010000237E0000540200001002000023537472696E6773000000006404000004000000235553006804000010000000234755494400000078040000AC00000023426C6F6200000000000000020000015717A2010900000000FA253300160000010000000D0000000200000002000000060000000100000001000000090000000400000005000000010000000200000002000000010000000200000000000A0001000000000006003C0035000A00670052000A008E0052000600DB00C8001300EF00000006002401040106004401040106006C0135000A009D0182010A00B90182010600DF01C0010600F501C001060000023500000000000100000000000100010009211000180000000500010001000600AE0023000100B4002600502000000000C60071000A000100682000000000E6097A000E0001008020000000009608850012000100A420000000009600980017000100D8200000000086009E000A000200F020000000009600A6001E00020000000100C600020009002100FE0032003100FE0038003900FE003D004100FE003D004900FE0041005900FE00500069000702560019007A000E001900FE0070002E000B007A002E00130083002E001B008C0043002B00470059005D00610068007500020001000000BA0029000000C1002D000200020003000200030005000480000000000000000000000000000000006201000004000000000000000000000001002C0000000000040000000000000000000000010046000000000000000000003C4D6F64756C653E0053616D706C655544542E646C6C0053716C55736572446566696E65645479706531006D73636F726C69620053797374656D0056616C7565547970650053797374656D2E446174610053797374656D2E446174612E53716C547970657300494E756C6C61626C6500546F537472696E67006765745F49734E756C6C006765745F4E756C6C0053716C537472696E67005061727365004D6574686F6431004D6574686F6432005F76617231005F6E756C6C0049734E756C6C004E756C6C00730053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F646573002E63746F720053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053616D706C655544540053657269616C697A61626C65417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C55736572446566696E65645479706541747472696275746500466F726D61740053797374656D2E52756E74696D652E496E7465726F705365727669636573005374727563744C61796F7574417474726962757465004C61796F75744B696E6400537472696E6700456D7074790000000000010000F29E39299A1EBD40A56A399A5D1E10610008B77A5C561934E0890320000E0320000204000011080600011108110D040000110D02060802060203280002040800110805200101111504200101080320000105200101112908010001000000000005200101113102060E0307010E03070102060702110811080707031108110802042001010E040701110D0801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000004BFF5500000000020000001C0100004C2600004C08000052534453DFC29DC30B515C4ABFC3FD3DBB7A048801000000643A5C75736572735C6C6F6E6E79625C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C53616D706C655544545C53616D706C655544545C6F626A5C44656275675C53616D706C655544542E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000902700000000000000000000AE270000002000000000000000000000000000000000000000000000A027000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D0065000000530061006D0070006C0065005500440054002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530061006D0070006C0065005500440054002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000C03700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;
GO
ALTER ASSEMBLY [SampleUDT]
DROP FILE ALL
ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A445300000000020000020000001B000000840000000000000018000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF380000FEFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF0BCA3101380000000010000000100000000000000E00FFFF04000000FFFF03000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000BCA3101380000000010000000100000000000000F00FFFF04000000FFFF0300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000942E3101004BFF5501000000DFC29DC30B515C4ABFC3FD3DBB7A048800000000000000000100000001000000000000000000000000000000DC51330100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000BCA310138000000001000000010000000000000FFFFFFFF04000000FFFF030000000000FFFFFFFF00000000FFFFFFFF00000000FFFFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000BCA310138000000001000000010000000000000FFFFFFFF04000000FFFF030000000000FFFFFFFF00000000FFFFFFFF00000000FFFFFFFF000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F862513FC607D311905300C04FA302A1C4454B99E9E6D211903F00C04FA302A10B9D865A1166D311BD2A0000F80849BD60A66E40CF64824CB6F042D48172A7991000000000000000A2EEC7934A3D37151FD88F759E2C495E0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006E000000000000006E000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FEEFFEEF01000000C400000000643A5C75736572735C6C6F6E6E79625C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C53616D706C655544545C53616D706C655544545C53716C55736572446566696E656454797065312E63730000643A5C75736572735C6C6F6E6E79625C646F63756D656E74735C76697375616C2073747564696F20323031335C70726F6A656374735C73616D706C657564745C73616D706C657564745C73716C75736572646566696E656474797065312E637300040000006200000001000000630000000000000003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001BE2300180000000D74A34F301F4D001010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000200000001000000020000000000000063000000280000001BE23001B5D25C0C580000000100000062000000630000006500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000400000032002A11000000001C010000000000000B000000000000000000000001000006000000000100000000546F537472696E670000001600031104000000E80000000B00000000000000010000000A0024115553797374656D00120024115553797374656D2E44617461000000001A0024115553797374656D2E446174612E53716C436C69656E7400001A0024115553797374656D2E446174612E53716C54797065730000001E002411554D6963726F736F66742E53716C5365727665722E536572766572001E00201100000000010000110000000000000400435324312430303030000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040000000C000000010005000200060032002A1100000000C0010000000000000C0000000000000000000000020000060B00000001000000006765745F49734E756C6C0016000311200100008C0100000C0000000B000000010000001E00201100000000020000110000000000000400435324312430303030000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040172000C000000010000060200060032002A11000000007C02000000000000170000000000000000000000030000061700000001000000006765745F4E756C6C00000016000311C4010000480200001700000017000000010000001E00201101000000030000110000000000000400435324312430303030000000160020110000000003000011000000000000000068000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040186000C00000001000006020006002E002A11000000005403000000000000250000000000000000000000040000062E000000010000000050617273650000160003118002000020030000250000002E000000010000001E002011010000000400001100000000000004004353243124303030300000001E00201102000000040000110000000000000400435324342430303031000000160020110000000004000011000000000000000075000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040186000C000000010000060200060032002A1100000000F8030000000000000B0000000000000000000000050000065300000001000000004D6574686F6431000000001600031158030000C40300000B00000053000000010000001E00201100000000010000110000000000000400435324312430303030000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040195760C000000010000060200060032002A11000000009C04000000000000100000000000000000000000060000065E00000001000000004D6574686F64320000000016000311FC03000068040000100000005E000000010000001E00201100000000050000110000000000000400435324312430303030000000020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040195760C0000000100000602000600F20000003C00000000000000010001000B000000000000000300000030000000000000000D000080010000000F00008009000000100000800500060009001D0005000600F20000003C0000000B000000010001000C000000000000000300000030000000000000001500008001000000170000800A0000001800008009000A000D001A0009000A00F200000054000000170000000100010017000000000000000500000048000000000000001E000080010000001F00008009000000200000801100000021000080150000002200008009000A000D003F000D001C000D00160009000A00F20000006C0000002E0000000100010025000000000000000700000060000000000000002600008001000000270000800C000000EEEFFE800F0000002800008017000000290000801F0000002B000080230000002C0000800500060009001600000000000D00190009003B000900120005000600F20000003C00000053000000010001000B0000000000000003000000300000000000000030000080010000003200008009000000330000800500060009001D0005000600F20000003C0000005E0000000100010010000000000000000300000030000000000000003700008001000000390000800E0000003A000080050006000900220005000600F4000000080000000100000000000000300000000000000018000000300000004C000000640000007C00000094000000A8000000C0000000D8000000F00000000801000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFF1A092FF160000000340200004D000000010000007D000000010000000100000001000000190000000100000009010000010000006500000001000000A900000001000000D9000000010000003100000001000000F100000001000000C1000000010000009500000001000000010000000000000000000000000000000000000000000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000020000000000000000010000000000000000000000000000000000000000000000000000000000000001000200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000000000000000000000000000000000000000000000000000000000001000400000000000000000000000000000000000000000000600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0000001800000024000000300000003C0000004800000054000000600000006C00000078000000840000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001600251100000000040000000100546F537472696E6700001600291100000000040000000100303630303030303100001A002511000000002001000001006765745F49734E756C6C000000001600291100000000200100000100303630303030303200001600251100000000C401000001006765745F4E756C6C00001600291100000000C4010000010030363030303030330000120025110000000080020000010050617273650016002911000000008002000001003036303030303034000016002511000000005803000001004D6574686F64310000001600291100000000580300000100303630303030303500001600251100000000FC03000001004D6574686F64320000001600291100000000FC030000010030363030303030360000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000000000000000000000000000000000000000000000000000FFFFFFFF1A092FF10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFF77093101010000000B00008E0C004F000D00000060000000AC0000002C00000070000000000000000000000016000000190000000000EEC00000000000000000FFFF000000000000FFFFFFFF00000000FFFF0000000000000000000000000A00A004000000000000F001000001000000C02E8600000000000000000053716C55736572446566696E65645479706531003241393744463435000000002DBA2EF101000000000000000B00000000000000000000000000000000000000010000000B0000000C0000000000000000000000000000000000000001000000170000001700000000000000000000000000000000000000010000002E000000250000000000000000000000000000000000000001000000530000000B00000000000000000000000000000000000000010000005E0000001000000000000000000000000000000000000000020002000D01000000000100FFFFFFFF000000006E0000000802000000000000FFFFFFFF00000000FFFFFFFF010001000000010000000000643A5C75736572735C6C6F6E6E79625C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C53616D706C655544545C53616D706C655544545C53716C55736572446566696E656454797065312E637300000000FEEFFEEF010000000100000000010000000000000000000000FFFFFFFFFFFFFFFFFFFF0900FFFFFFFFFFFFFFFFFFFF000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000942E3101004BFF5501000000DFC29DC30B515C4ABFC3FD3DBB7A04888E0000002F4C696E6B496E666F002F6E616D6573002F7372632F686561646572626C6F636B002F7372632F66696C65732F643A5C75736572735C6C6F6E6E79625C646F63756D656E74735C76697375616C2073747564696F20323031335C70726F6A656374735C73616D706C657564745C73616D706C657564745C73716C75736572646566696E656474797065312E6373000400000006000000010000001B00000000000000220000000800000011000000070000000A00000006000000000000000500000000000000DC51330100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E00000020000000EA00000038000000170200003800000000000000E8000000800000005800000028000000C4060000A40200002C000000200100000300000016000000060000001400000015000000070000000A0000000B00000008000000090000000C0000000D0000000E0000000F000000100000001100000013000000120000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000170000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 AS N'SampleUDT.pdb'
;
GO
CREATE TYPE [dbo].[SqlUserDefinedType1]
EXTERNAL NAME [SampleUDT].[SqlUserDefinedType1];
GO
CREATE TYPE SSN
FROM varchar(11) NOT NULL;
GO
CREATE TYPE [dbo].[UserDefinedTableType1] AS TABLE
(
Id SSN PRIMARY KEY,
Name VARCHAR(128) ,
CustomUDT SqlUserDefinedType1 NULL,
MI char(1) DEFAULT (' '),
UniqueValue int UNIQUE,
ComputedColumn AS ( Name + ' ' + MI + ' ' + UniqueValue)
)
GO
CREATE PROC TestUDT @UDDT SSN, @UDT SqlUserDefinedType1, @UDTT UserDefinedTableType1 READONLY
AS
BEGIN
SELECT * from @UDTT
END;
");
TSqlTypedModel typedModel = new TSqlTypedModel(model);
TSqlProcedure proc = typedModel.GetObject<TSqlProcedure>(new ObjectIdentifier("dbo","TestUDT"), DacQueryScopes.UserDefined);
Assert.IsNotNull(proc, "Missing procedure TestUDT");
var parameters = proc.Parameters.ToList();
Assert.AreEqual(3, parameters.Count, "Incorrect number of parameters");
var uddtParameter = parameters[0];
var parameterName = "@UDDT";
var parameterSqlDataType = SqlDataType.Unknown;
var baseSqlType = SqlDataType.VarChar;
var parameterDataType = uddtParameter.DataType.SingleOrDefault() as TSqlDataTypeReference;
Assert.IsNotNull(parameterDataType, "parameter data type should not be null");
Assert.AreEqual(parameterSqlDataType, parameterDataType.SqlDataType, "{0} data type should be based off {1}", parameterName, parameterSqlDataType);
var baseType = parameterDataType.Type.SingleOrDefault();
Assert.IsNotNull(baseType, "parameter base type should not be null");
Assert.AreEqual(baseSqlType, baseType.SqlDataType, "Incorrect SQLDataType for base type");
var udtParameterType = parameters[1].DataType.SingleOrDefault() as TSqlUserDefinedTypeReference;
Assert.IsNotNull(udtParameterType, "UDT Parameter data type should be of type TSqlUserDefinedType" );
Assert.AreEqual("SqlUserDefinedType1", udtParameterType.Name.Parts[1]);
var udttParameterType = parameters[2].DataType.SingleOrDefault() as TSqlTableTypeReference;
Assert.IsNotNull(udttParameterType, "Parameter @UDTT' data type should not be null");
Assert.AreEqual("UserDefinedTableType1", udttParameterType.Name.Parts[1], "Incorrect table type name");
}
}
public void TestIndexIncludedColumns()
{
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
{
model.AddObjects(@"CREATE TABLE [dbo].[Table1]
(
[Id] INT NOT NULL PRIMARY KEY,
col1 int not null,
[c] AS (Id + ' ')
);
GO
CREATE INDEX ix_1 on Table1 (c) INCLUDE ( col1)
");
TSqlTypedModel typedModel = new TSqlTypedModel(model);
var index = typedModel.GetObject<TSqlIndex>(new ObjectIdentifier("dbo", "Table1", "ix_1"), DacQueryScopes.UserDefined);
Assert.IsNotNull(index, "Index should not be null");
Assert.AreEqual(1, index.IncludedColumns.Count(), "Incorrect number of included columns");
}
}
public void TestIndexColumnMetadata()
{
using (TSqlTypedModel model = new TSqlTypedModel(SqlServerVersion.Sql120, new TSqlModelOptions() { }))
{
string createTable = @"
CREATE TABLE [dbo].[Table1]
(
[Id] INT NOT NULL,
[two] AS (ID +1),
columnSet1 int sparse,
columnSet2 int sparse,
cs xml column_set for all_sparse_columns
)
";
model.AddObjects(createTable);
model.AddObjects("CREATE INDEX IX ON Table1 (Id ASC, two DESC)");
TSqlIndex index = model.GetObject<TSqlIndex>(new ObjectIdentifier("dbo", "Table1", "IX"), DacQueryScopes.UserDefined);
var columns = index.Columns.ToList();
Assert.AreEqual(2, columns.Count, "Incorrect number of index columns");
Assert.AreEqual(true, columns[0].Ascending, "Incorrect Ascending value");
Assert.AreEqual(true, columns[0].GetMetadataProperty<bool>(Index.ColumnsRelationship.Ascending), "Incorrect Ascending value from GetMetadataProperty");
Assert.AreEqual(false, columns[1].Ascending, "Incorrect Ascending value");
Assert.AreEqual(false, columns[1].GetMetadataProperty<bool>(Index.ColumnsRelationship.Ascending), "Incorrect Ascending value from GetMetadataProperty");
}
}