public Column(UserDefinedTableType userDefinedTableType, String name)
: this(userDefinedTableType.Schema.Database.Server.Configuration, name)
{
this.UserDefinedTableType = userDefinedTableType;
}
public Columns(UserDefinedTableType userDefinedTableType)
: this()
{
this.Name = "";
var sql = @"
;WITH foreign_keys_CTE (FOREIGN_KEY_TABLE, FOREIGN_KEY_COLUMN, PRIMARY_KEY_SCHEMA, PRIMARY_KEY_TABLE, PRIMARY_KEY_COLUMN)
AS
(
SELECT
FOREIGN_KEY_TABLE = OBJECT_NAME(FKC.parent_object_id),
FOREIGN_KEY_COLUMN = C.NAME,
PRIMARY_KEY_SCHEMA = OBJECT_SCHEMA_NAME(FKC.referenced_object_id),
PRIMARY_KEY_TABLE = OBJECT_NAME(FKC.referenced_object_id),
PRIMARY_KEY_COLUMN = CREF.NAME
FROM
sys.foreign_key_columns AS FKC
INNER JOIN sys.columns AS C ON FKC.parent_column_id = C.column_id AND FKC.parent_object_id = c.object_id
INNER JOIN sys.columns AS CREF ON FKC.referenced_column_id = CREF.column_id AND FKC.referenced_object_id = cref.object_id
),
primary_keys_CTE (OBJECT_ID, COLUMN_ID, PRIMARY_KEY_ORDINAL, PRIMARY_KEY_DIRECTION)
AS
(
SELECT
i.object_id,
c.column_id,
ic.key_ordinal,
CASE
WHEN ic.is_descending_key = 0 THEN 'ASC'
ELSE 'DESC'
END
FROM
sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id and i.is_primary_key = 1
INNER JOIN sys.columns AS C ON C.[object_id] = IC.[object_id] AND c.[column_id] = ic.column_id
),
column_type_CTE (USER_TYPE_ID, SERVER_DATATYPE_NAME, NATIVE_SERVER_DATATYPE_NAME)
AS
(
SELECT
T1.user_type_id,
SERVER_DATATYPE_NAME = UPPER(T1.name),
NATIVE_SERVER_DATATYPE_NAME = UPPER(COALESCE(T2.name, T1.name))
FROM
sys.types AS T1
LEFT OUTER JOIN sys.types AS T2 ON T2.user_type_id = T1.system_type_id AND T1.is_table_type = 0
)
SELECT
DISTINCT
TABLE_TYPE_NAME = TT.name,
COLUMN_NAME = C.[name],
COLUMN_ORDINAL = C.column_id - 1,
C.user_type_id,
C.system_type_id,
SERVER_DATATYPE_NAME = CT_CTE.SERVER_DATATYPE_NAME,
NATIVE_SERVER_DATATYPE_NAME = CT_CTE.NATIVE_SERVER_DATATYPE_NAME,
PHYSICAL_LENGTH = C.max_length,
C.[precision],
C.scale,
IS_NULLABLE = CASE C.is_nullable WHEN 0 THEN 'N' ELSE 'Y' END,
IS_IDENTITY = CASE C.is_identity WHEN 0 THEN 'N' ELSE 'Y' END,
IS_XML_DOCUMENT = CASE C.is_xml_document WHEN 0 THEN 'N' ELSE 'Y' END,
XML_COLLECTION_NAME = COALESCE(XMLCOLL.name, ''),
IS_PRIMARY_KEY = CASE WHEN (PK_CTE.primary_key_ordinal IS NULL) THEN 'N' ELSE 'Y' END,
PRIMARY_KEY_ORDINAL = COALESCE(PK_CTE.primary_key_ordinal, -1),
PRIMARY_KEY_DIRECTION = COALESCE(PK_CTE.PRIMARY_KEY_DIRECTION, ''),
IS_FOREIGN_KEY = CASE WHEN (FK_CTE.foreign_key_table IS NULL) THEN 'N' ELSE 'Y' END,
PRIMARY_KEY_SCHEMA = COALESCE(FK_CTE.primary_key_schema, ''),
PRIMARY_KEY_TABLE = COALESCE(FK_CTE.primary_key_table, ''),
PRIMARY_KEY_COLUMN = COALESCE(FK_CTE.primary_key_column, '')
FROM
sys.table_types AS TT
INNER JOIN sys.columns AS C ON TT.type_table_object_id = C.object_id
LEFT OUTER JOIN sys.xml_schema_collections AS XMLCOLL ON XMLCOLL.xml_collection_id = C.xml_collection_id
LEFT OUTER JOIN foreign_keys_CTE AS FK_CTE ON (FK_CTE.foreign_key_table = TT.[name]) AND (FK_CTE.foreign_key_column = C.[name])
LEFT OUTER JOIN primary_keys_CTE AS PK_CTE ON PK_CTE.object_id = TT.type_table_object_id AND PK_CTE.column_id = C.column_id
LEFT OUTER JOIN column_type_CTE AS CT_CTE ON CT_CTE.USER_TYPE_ID = C.user_type_id
WHERE
TT.[name] = '{0}';";
var select = String.Format(sql, userDefinedTableType.Name);
var t = userDefinedTableType.Schema.Database.Server.Configuration.Connection.GetDataSet(select).Tables[0];
foreach (DataRow row in t.Rows)
{
var columnType = ColumnType.Unknown;
if (row["IS_IDENTITY"].ToString().EqualsCI("Y"))
{
columnType |= ColumnType.ID;
}
if (row["IS_PRIMARY_KEY"].ToString().EqualsCI("Y"))
{
columnType |= ColumnType.PrimaryKey;
}
if (row["IS_FOREIGN_KEY"].ToString().EqualsCI("Y"))
{
columnType |= ColumnType.ForeignKey;
}
if (columnType == ColumnType.Unknown)
{
columnType |= ColumnType.NonKeyAndNonID;
}
var nativeServerDataTypeName = row["NATIVE_SERVER_DATATYPE_NAME"].ToString();
if (nativeServerDataTypeName.NotEqualsCI("TIMESTAMP") && !columnType.HasFlag(ColumnType.ID))
{
columnType |= (ColumnType.CanAppearInInsertStatement | ColumnType.CanAppearInUpdateSetClause);
}
if (nativeServerDataTypeName != "XML")
{
columnType |= ColumnType.CanAppearInSqlWhereClause;
}
var physicalLength = Convert.ToInt32(row["PHYSICAL_LENGTH"]);
this.Add(
new Column(userDefinedTableType, row["COLUMN_NAME"].ToString())
{
Ordinal = Convert.ToInt32(row["COLUMN_ORDINAL"]),
ColumnType = columnType,
ServerDataTypeName = row["SERVER_DATATYPE_NAME"].ToString(),
NativeServerDataTypeName = nativeServerDataTypeName,
PhysicalLength = physicalLength,
LogicalLength = this.GetLogicalLength(nativeServerDataTypeName, physicalLength),
Precision = Convert.ToInt32(row["PRECISION"]),
Scale = Convert.ToInt32(row["SCALE"]),
IsNullable = row["IS_NULLABLE"].ToString().EqualsCI("Y"),
IsXmlDocument = row["IS_XML_DOCUMENT"].ToString().EqualsCI("Y"),
XmlCollectionName = row["XML_COLLECTION_NAME"].ToString(),
PrimaryKeyOrdinal = Convert.ToInt32(row["PRIMARY_KEY_ORDINAL"]),
PrimaryKeyDirection = row["PRIMARY_KEY_DIRECTION"].ToString(),
PrimaryKeySchema = row["PRIMARY_KEY_SCHEMA"].ToString(),
PrimaryKeyTable = row["PRIMARY_KEY_TABLE"].ToString(),
PrimaryKeyColumn = row["PRIMARY_KEY_COLUMN"].ToString()
});
}
}
