private IEnumerable <UserDefinedTableTypeColumnDetail> GetUpdatedColumnDetailsInUserDefinedTableType(
string tableType, string tableTypeSchema, string columnToBeUpdated, out List <UserDefinedTableTypeColumnDetail> existingColumnDetails)
{
var updatedColumnDetails = new List <UserDefinedTableTypeColumnDetail>();
existingColumnDetails = new List <UserDefinedTableTypeColumnDetail>();
using (var reader = SelectSqlData.SelectColumnsInUserDefinedTableType(Connection, tableType, tableTypeSchema))
{
if (reader.HasRows)
{
while (reader.Read())
{
var columnName = reader["column_name"] as string;
bool isNullable = Convert.ToBoolean(reader["is_nullable"]);
int?existingCharacterMaxLength;
var existingDataType = reader["data_type"] as string;
switch (existingDataType)
{
case "char":
case "varchar":
existingCharacterMaxLength = Convert.ToInt32(reader["character_maximum_length"]);
break;
case "int":
existingCharacterMaxLength = null; // size does not get specified in definition
break;
default:
throw new InvalidOperationException($"Error creating User-Defined Table Type definition. Can't handle type: {existingDataType}.");
}
string dataType;
int? characterMaxLength;
if (columnName == columnToBeUpdated &&
existingCharacterMaxLength != null &&
NewDataTypeName.MaxDataSize > existingCharacterMaxLength.Value)
{
dataType = NewDataTypeName.DataType;
characterMaxLength = NewDataTypeName.MaxDataSize;
}
else
{
dataType = existingDataType;
characterMaxLength = existingCharacterMaxLength;
}
existingColumnDetails.Add(new UserDefinedTableTypeColumnDetail(Connection.Database, tableType, tableTypeSchema, columnName, existingDataType, isNullable, existingCharacterMaxLength));
updatedColumnDetails.Add(new UserDefinedTableTypeColumnDetail(Connection.Database, tableType, tableTypeSchema, columnName, dataType, isNullable, characterMaxLength));
}
}
}
return(updatedColumnDetails);
}
private ColumnIdentifier GetOutboundForeignKey(ColumnIdentifier columnIdentifier)
{
var constraintReader = SelectSqlData.SelectOutboundForeignKey(Connection, columnIdentifier.Table, columnIdentifier.Column);
if (constraintReader.Read())
{
string foreignTable = constraintReader["foreign_table"] as string;
string foreignColumn = constraintReader["foreign_column"] as string;
return(new ColumnIdentifier(columnIdentifier.Database, columnIdentifier.Schema, foreignTable, foreignColumn));
}
return(null);
}
private IEnumerable <TriggerIdentifier> GetTriggersWithUserDefinedTableType(string tableType, string tableTypeSchema)
{
using (var reader = SelectSqlData.SelectTriggersWithUserDefinedTableType(Connection, tableType, tableTypeSchema))
{
if (reader.HasRows)
{
while (reader.Read())
{
string trigger = reader["trigger_name"] as string;
string schema = reader["trigger_schema"] as string;
yield return(new TriggerIdentifier(Connection.Database, schema, trigger));
}
}
reader.Close();
}
}
private IEnumerable <FuncIdentifier> GetFuncsWithUserDefinedTableType(string tableType, string tableTypeSchema)
{
using (var reader = SelectSqlData.SelectFuncsWithUserDefinedTableType(Connection, tableType, tableTypeSchema))
{
if (reader.HasRows)
{
while (reader.Read())
{
string func = reader["func_name"] as string;
string schema = reader["func_schema"] as string;
yield return(new FuncIdentifier(Connection.Database, schema, func));
}
}
reader.Close();
}
}
// Get all columns whose names match the given pattern
private IEnumerable <ColumnIdentifier> GetColumnsMatchingPattern(Regex variableNamePattern, string schema)
{
using (var reader = SelectSqlData.SelectColumnsOfTypeString(Connection))
{
if (reader.HasRows)
{
while (reader.Read())
{
string table = reader["tab"] as string;
string column = reader["col"] as string;
int columnMaxLength = Convert.ToInt32(reader["col_max_length"]);
if (column != null && variableNamePattern.IsMatch(column) && columnMaxLength < NewDataTypeName.MaxDataSize)
{
yield return(new ColumnIdentifier(Connection.Database, schema, table, column));
}
}
}
reader.Close();
}
}
// Get all columns in user-defined table types that match the given pattern
private IEnumerable <ColumnIdentifier> GetColumnsInUserDefinedTableTypesMatchingPattern()
{
using (var reader = SelectSqlData.SelectUserDefinedTableTypes(Connection))
{
if (reader.HasRows)
{
while (reader.Read())
{
string name = reader["name"] as string;
string column = reader["col"] as string;
string schema = reader["schema_name"] as string;
if (column != null && VariableNamePattern.IsMatch(column))
{
yield return(new ColumnIdentifier(Connection.Database, schema, name, column));
}
}
}
reader.Close();
}
}
private IEnumerable <TSqlFragmentIdentifier> GetUniqueReferencingEntities(string table, string schema, HashSet <TSqlFragmentIdentifier> visitedSqlFragments,
List <TSqlFragmentIdentifier> excludeSqlFragments = null)
{
string database = Connection.Database;
using (SqlDataReader sqlFragmentReader = SelectSqlData.SelectSqlFragmentsFromTable(table, schema, SqlFragmentScriptFileName, Connection))
{
while (sqlFragmentReader.Read())
{
var sqlFragmentSchema = sqlFragmentReader[SqlFragmentSchemaColumn] as string;
var sqlFragmentName = sqlFragmentReader[SqlFragmentNameColumn] as string;
TSqlFragmentIdentifier sqlFragmentIdentifier = GetSqlFragmentIdentifier(database, sqlFragmentSchema, sqlFragmentName);
if (!visitedSqlFragments.Contains(sqlFragmentIdentifier) &&
(excludeSqlFragments == null || !excludeSqlFragments.Contains(sqlFragmentIdentifier))
)
{
visitedSqlFragments.Add(sqlFragmentIdentifier);
yield return(sqlFragmentIdentifier);
}
}
}
}
private PrimaryKeyDetail GetPrimaryKeyDetails(string schemaName, string tableName, string constraintName, bool disabled)
{
return(new PrimaryKeyDetail(SelectSqlData.GetIndex(schemaName, tableName, constraintName, disabled, Connection)));
}
private IEnumerable <ConstraintDetail> GetConstraintDetails(string schemaName, string tableName, string columnName, string inboundForeignKey)
{
string databaseName = Connection.Database;
using (var constraintReader = SelectSqlData.SelectIndexesConstraintsAndStatistics(Connection, tableName, columnName))
{
while (constraintReader.Read())
{
string constraintType = constraintReader["constraint_type"] as string;
string constraintName = constraintReader["index_name"] as string;
string constraintData = constraintReader["data"] as string;
bool disabled = (int)constraintReader["is_disabled"] > 0;
bool untrusted = (int)constraintReader["is_not_trusted"] > 0;
switch (constraintType)
{
case "FK_IN":
// it's a foreign key reference from this column
if (!StringComparer.OrdinalIgnoreCase.Equals(constraintName, inboundForeignKey))
{
throw new Exception(
$"Column {databaseName}.{schemaName}.{tableName}.{columnName} is referenced by foreign key {constraintName} which comes from column {constraintData} - recursion should start from at least there.");
}
break;
case null:
// it's an index
yield return(new IndexDetail(SelectSqlData.GetIndex(schemaName, tableName, constraintName, disabled, Connection)));
break;
case "PK":
// it's a primary key constraint
yield return(GetPrimaryKeyDetails(schemaName, tableName, constraintName, disabled));
break;
case "UQ":
// it's a unique constraint
yield return(GetUniqueConstraintDetails(schemaName, tableName, constraintName, disabled));
break;
case "D":
// it's a default constraint
yield return(GetDefaultConstraintDetails(constraintName, schemaName, tableName, columnName, constraintData));
break;
case "C":
// it's a check constraint
yield return(GetCheckConstraintDetails(constraintName, schemaName, tableName, columnName, constraintData, untrusted, disabled));
break;
case "FK":
// it's a foreign key reference into this column
yield return(SelectSqlData.GetForeignKeyDetails(schemaName, tableName, constraintName, untrusted, disabled, Connection));
break;
case "STAT":
// it's a statistics object on this column
yield return(SelectSqlData.GetStatisticsDetails(schemaName, tableName, constraintName, Connection));
break;
}
}
yield break;
}
}
private ColumnDetail GetColumnDetails(string schemaName, string tableName, string columnName)
{
bool nullable = SelectSqlData.SelectColumnInfo(Connection, tableName, columnName);
return(new ColumnDetail(Connection.Database, schemaName, tableName, columnName, NewDataTypeName.ToString(), nullable));
}
