private void GetColumns()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT DISTINCT
schema_name(t.schema_id) AS [schema],
t.name AS [table],
type_name(c.user_type_id) AS [typename],
c.name AS [column_name],
c.column_id AS [ordinal],
c.is_nullable AS [nullable],
CAST(ic.key_ordinal AS int) AS [primary_key_ordinal],
object_definition(c.default_object_id) AS [default_sql],
cc.definition AS [computed_sql],
CAST(CASE WHEN c.precision <> tp.precision
THEN c.precision
ELSE null
END AS int) AS [precision],
CAST(CASE WHEN c.scale <> tp.scale
THEN c.scale
ELSE null
END AS int) AS [scale],
CAST(CASE WHEN c.max_length <> tp.max_length
THEN c.max_length
ELSE null
END AS int) AS [max_length],
c.is_identity,
c.is_computed
FROM sys.index_columns ic
RIGHT JOIN (SELECT * FROM sys.indexes WHERE is_primary_key = 1) AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
RIGHT JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
RIGHT JOIN sys.types tp ON tp.user_type_id = c.user_type_id
LEFT JOIN sys.computed_columns cc ON cc.object_id = c.object_id AND cc.column_id = c.column_id
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE t.name <> '" + HistoryRepository.DefaultTableName + "'" +
TemporalTableWhereClause;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var schemaName = reader.GetValueOrDefault <string>("schema");
var tableName = reader.GetValueOrDefault <string>("table");
var columnName = reader.GetValueOrDefault <string>("column_name");
var dataTypeName = reader.GetValueOrDefault <string>("typename");
var ordinal = reader.GetValueOrDefault <int>("ordinal");
var nullable = reader.GetValueOrDefault <bool>("nullable");
var primaryKeyOrdinal = reader.GetValueOrDefault <int?>("primary_key_ordinal");
var defaultValue = reader.GetValueOrDefault <string>("default_sql");
var computedValue = reader.GetValueOrDefault <string>("computed_sql");
var precision = reader.GetValueOrDefault <int?>("precision");
var scale = reader.GetValueOrDefault <int?>("scale");
var maxLength = reader.GetValueOrDefault <int?>("max_length");
var isIdentity = reader.GetValueOrDefault <bool>("is_identity");
var isComputed = reader.GetValueOrDefault <bool>("is_computed");
Logger.LogDebug(
RelationalDesignEventId.FoundColumn,
() => SqlServerDesignStrings.FoundColumn(
schemaName, tableName, columnName, dataTypeName, ordinal, nullable,
primaryKeyOrdinal, defaultValue, computedValue, precision, scale, maxLength, isIdentity, isComputed));
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
Logger.LogDebug(
RelationalDesignEventId.ColumnSkipped,
() => SqlServerDesignStrings.ColumnNotInSelectionSet(columnName, schemaName, tableName));
continue;
}
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(
SqlServerDesignEventId.ColumnMustBeNamedWarning,
() => SqlServerDesignStrings.ColumnNameEmptyOnTable(schemaName, tableName));
continue;
}
TableModel table;
if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table))
{
Logger.LogWarning(
RelationalDesignEventId.MissingTableWarning,
() => SqlServerDesignStrings.UnableToFindTableForColumn(columnName, schemaName, tableName));
continue;
}
if (dataTypeName == "nvarchar" ||
dataTypeName == "nchar")
{
maxLength /= 2;
}
if (dataTypeName == "decimal" ||
dataTypeName == "numeric")
{
// maxlength here represents storage bytes. The server determines this, not the client.
maxLength = null;
}
var dateTimePrecision = default(int?);
if (_dateTimePrecisionTypes.Contains(dataTypeName))
{
dateTimePrecision = scale ?? DefaultDateTimePrecision;
scale = null;
}
var column = new ColumnModel
{
Table = table,
DataType = dataTypeName,
Name = columnName,
Ordinal = ordinal - 1,
IsNullable = nullable,
PrimaryKeyOrdinal = primaryKeyOrdinal,
DefaultValue = defaultValue,
ComputedValue = computedValue,
Precision = precision,
Scale = scale,
MaxLength = maxLength <= 0 ? default(int?) : maxLength,
ValueGenerated = isIdentity
? ValueGenerated.OnAdd
: isComputed || dataTypeName == "timestamp"
? ValueGenerated.OnAddOrUpdate
: default(ValueGenerated?)
};
column.SqlServer().IsIdentity = isIdentity;
column.SqlServer().DateTimePrecision = dateTimePrecision;
table.Columns.Add(column);
_tableColumns.Add(ColumnKey(table, column.Name), column);
}
}
}