private void AddPropertiesForIndex(Panel panel, TSqlObject index)
{
foreach (var reference in index.GetReferencedRelationshipInstances(Index.Columns))
{
panel.Children.Add(GetPropertyLabel("Column: ", reference.ObjectName + " " + (reference.GetProperty <bool>(Index.ColumnsRelationship.Ascending) ? "ASC" : "DESC")));
}
if (index.GetReferencedRelationshipInstances(Index.IncludedColumns).Any())
{
foreach (var reference in index.GetReferencedRelationshipInstances(Index.IncludedColumns))
{
panel.Children.Add(GetPropertyLabel("Included Column: ", reference.ObjectName.ToString()));
}
}
}
private void AddPropertiesForPrimaryKey(Panel panel, TSqlObject key)
{
foreach (var reference in key.GetReferencedRelationshipInstances(PrimaryKeyConstraint.Columns))
{
panel.Children.Add(GetPropertyLabel("Column: ", reference.ObjectName + " " + (reference.GetProperty <bool>(PrimaryKeyConstraint.ColumnsRelationship.Ascending) ? "ASC" : "DESC")));
}
}
private void AddPropertiesForIndex(Panel panel, TSqlObject index)
{
foreach (var reference in index.GetReferencedRelationshipInstances(Index.Columns))
{
panel.Children.Add(GetPropertyLabel("Column: ", reference.ObjectName + " " + (reference.GetProperty<bool>(Index.ColumnsRelationship.Ascending) ? "ASC" : "DESC")));
}
if (index.GetReferencedRelationshipInstances(Index.IncludedColumns).Any())
{
foreach (var reference in index.GetReferencedRelationshipInstances(Index.IncludedColumns))
{
panel.Children.Add(GetPropertyLabel("Included Column: ", reference.ObjectName.ToString()));
}
}
}
/// <summary>
/// Filter referenced objects to only return composed children. These are objects that have a real
/// parent-child relationship and couldn't be defined on their own. The canonical example is that
/// a Table->Column is a composing relationship and we'd only get to navigate to the Column via the Table
/// reference. To avoid loops we don't want to traverse Hierarchical or Peer relationships. Those can refer to other
/// top-level objects or objects that are composed children of a different top-level object, and hence would
/// cause us to iterate multiple times over an object in the model.
///
/// Note that <see cref="TSqlObject.GetReferencedRelationshipInstances()"/> may return relationships
/// where there is no object to in the model for the reference. This can happen if the object on that
/// side of the relationship is from a referenced dacpac. Hence we check that the
/// <see cref="ModelRelationshipInstance.Object"/> is not null when filtering our objects
/// </summary>
private IEnumerable <TSqlObject> GetComposedChildren(TSqlObject tSqlObject)
{
return(from rel
in tSqlObject.GetReferencedRelationshipInstances(DacExternalQueryScopes.UserDefined)
where rel.Relationship.Type == RelationshipType.Composing && rel.Object != null
select rel.Object);
}
private void AddPropertiesForForeignKey(Panel panel, TSqlObject key)
{
foreach (var reference in key.GetReferencedRelationshipInstances(ForeignKeyConstraint.Columns))
{
panel.Children.Add(GetPropertyLabel("Column: ", reference.ObjectName.ToString()));
}
foreach (var reference in key.GetReferencedRelationshipInstances(ForeignKeyConstraint.ForeignTable))
{
panel.Children.Add(GetPropertyLabel("Foreign Table: ", reference.ObjectName.ToString()));
}
foreach (var reference in key.GetReferencedRelationshipInstances(ForeignKeyConstraint.ForeignColumns))
{
panel.Children.Add(GetPropertyLabel("Foreign Column: ", reference.ObjectName.ToString()));
}
}
private void AddPropertiesForDefaultConstraint(Panel panel, TSqlObject key)
{
foreach (var reference in key.GetReferencedRelationshipInstances(DefaultConstraint.TargetColumn))
{
panel.Children.Add(GetPropertyLabel("Target Column: ", reference.ObjectName.ToString()));
}
}
private static bool HasParameterBeenRemoved(ModelRelationshipInstance parameter, TSqlObject oldProcedure)
{
/*
A parameter does not exist in the old model
*/
return
oldProcedure.GetReferencedRelationshipInstances(Procedure.Parameters)
.FirstOrDefault(p => p.ObjectName.Parts.Last() == parameter.ObjectName.Parts.Last()) == null;
}
private static bool HasParameterBeenRemoved(ModelRelationshipInstance parameter, TSqlObject oldProcedure)
{
/*
* A parameter does not exist in the old model
*/
return
(oldProcedure.GetReferencedRelationshipInstances(Procedure.Parameters)
.FirstOrDefault(p => p.ObjectName.Parts.Last() == parameter.ObjectName.Parts.Last()) == null);
}
private static void ShowColumnsDataType(TSqlObject table)
{
foreach (var child in table.GetReferencedRelationshipInstances(Table.Columns))
{
var type = child.Object.GetReferenced(Column.DataType).FirstOrDefault();
var isNullable = type.GetProperty<bool?> (DataType.UddtNullable);
var length = type.GetProperty<int?>(DataType.UddtLength);
//do something useful with this information!
}
}
private static void ShowColumnsDataType(TSqlObject table)
{
foreach (var child in table.GetReferencedRelationshipInstances(Table.Columns))
{
var type = child.Object.GetReferenced(Column.DataType).FirstOrDefault();
var isNullable = type.GetProperty <bool?> (DataType.UddtNullable);
var length = type.GetProperty <int?>(DataType.UddtLength);
//do something useful with this information!
}
}
public static ForeignKeyInfo GetFKInfo(this TSqlObject fk)
{
if (fk == null)
{
throw new ArgumentNullException(nameof(fk));
}
if (fk.ObjectType != ForeignKeyConstraint.TypeClass)
{
throw new ArgumentException("The parameter is not of type ForeignKeyConstraint", nameof(fk));
}
var fkColumns = fk.GetReferencedRelationshipInstances(ForeignKeyConstraint.Columns, DacQueryScopes.All)
.Select(x => x.ObjectName).ToList();
var fkForeignColumns = fk.GetReferencedRelationshipInstances(ForeignKeyConstraint.ForeignColumns, DacQueryScopes.All)
.Select(x => x.ObjectName).ToList();
return(new ForeignKeyInfo()
{
Name = fk.Name.GetName(),
TableName = new ObjectIdentifier(GetTableOrAliasName(fkColumns.FirstOrDefault())),
ToTableName = new ObjectIdentifier(GetTableOrAliasName(fkForeignColumns.FirstOrDefault())),
ColumnNames = fkColumns,
ToColumnNames = fkForeignColumns
});
}
private void AddPropertiesForDmlTrigger(Panel panel, TSqlObject key)
{
var duplicateRemover = new Dictionary <string, bool>();
foreach (var reference in key.GetReferencedRelationshipInstances(DmlTrigger.BodyDependencies))
{
var name = reference.ObjectName.ToString();
if (!duplicateRemover.ContainsKey(name))
{
panel.Children.Add(GetPropertyLabel("Dependency ", name));
duplicateRemover[name] = true;
}
}
}
private static void DumpIndex(TSqlObject index)
{
//Each TSqlObject has a name property:
ObjectIdentifier indexName = index.Name;
//Top level objects like tables, procedures and indexes will let you get the underlying script to generate them, doing this on things like columns fails
string script = "";
if (!index.TryGetScript(out script))
{
script = "Can only script top level objects";
}
//To get to individual properties we need to use the static schema container classes, each property can be called directly or you can ask an object for all it's child properties
var allowPageLocks = index.GetProperty<bool?>(Index.AllowPageLocks);
var isClustered = index.GetProperty<bool?>(Index.Clustered);
Console.WriteLine("Index: " + indexName);
Console.WriteLine("Properties: Is Clustered: {0}, Allow Page Locks: {1}", isClustered, allowPageLocks);
//To get the columns we need to ask for the relationships of the index and then enumerate through them
foreach (ModelRelationshipInstance column in index.GetReferencedRelationshipInstances(Index.Columns))
{
DumpColumn(column, "Column");
}
//Included columns are referenced using the relationships but are a slightly different class
foreach (ModelRelationshipInstance column in index.GetReferencedRelationshipInstances(Index.IncludedColumns))
{
DumpColumn(column, "Included");
}
Console.WriteLine("Script:");
Console.WriteLine(script);
Console.WriteLine("===============================");
}
private static void DumpIndex(TSqlObject index)
{
//Each TSqlObject has a name property:
ObjectIdentifier indexName = index.Name;
//Top level objects like tables, procedures and indexes will let you get the underlying script to generate them, doing this on things like columns fails
string script = "";
if (!index.TryGetScript(out script))
{
script = "Can only script top level objects";
}
//To get to individual properties we need to use the static schema container classes, each property can be called directly or you can ask an object for all it's child properties
var allowPageLocks = index.GetProperty <bool?>(Index.AllowPageLocks);
var isClustered = index.GetProperty <bool?>(Index.Clustered);
Console.WriteLine("Index: " + indexName);
Console.WriteLine("Properties: Is Clustered: {0}, Allow Page Locks: {1}", isClustered, allowPageLocks);
//To get the columns we need to ask for the relationships of the index and then enumerate through them
foreach (ModelRelationshipInstance column in index.GetReferencedRelationshipInstances(Index.Columns))
{
DumpColumn(column, "Column");
}
//Included columns are referenced using the relationships but are a slightly different class
foreach (ModelRelationshipInstance column in index.GetReferencedRelationshipInstances(Index.IncludedColumns))
{
DumpColumn(column, "Included");
}
Console.WriteLine("Script:");
Console.WriteLine(script);
Console.WriteLine("===============================");
}
private List <ColumnDescriptor> GetColumnDefinitions(TSqlObject table)
{
var columns = new List <ColumnDescriptor>();
foreach (var column in table.GetReferencedRelationshipInstances(Microsoft.SqlServer.Dac.Model.Table.Columns))
{
var definition = CreateColumnDefinition(column);
if (definition != null)
{
columns.Add(definition);
}
}
return(columns);
}
private bool HasDefaultValueBeenRemoved(ModelRelationshipInstance parameter, TSqlObject oldProcedure)
{
var newParameterHasDefault = parameter.Object.GetProperty(Parameter.DefaultExpression) != null;
var parameterInOldModel =
oldProcedure.GetReferencedRelationshipInstances(Procedure.Parameters)
.FirstOrDefault(p => p.ObjectName.Parts.Last() == parameter.ObjectName.Parts.Last());
/*
* A parameter in the new model has no default but it had a default in the old model...
*/
if (parameterInOldModel != null && !newParameterHasDefault && (parameterInOldModel.Object.GetProperty(Parameter.DefaultExpression) != null))
{
return(true);
}
return(false);
}
private static bool HasParameterBeenAddedWithoutDefaultValue(ModelRelationshipInstance parameter,
TSqlObject newProcedure)
{
var hasDefault = parameter.Object.GetProperty(Parameter.DefaultExpression) != null;
var parameterInOldModel =
newProcedure.GetReferencedRelationshipInstances(Procedure.Parameters)
.FirstOrDefault(p => p.ObjectName.Parts.Last() == parameter.ObjectName.Parts.Last());
/*
* A parameter did not exist in the old model and has no default
*/
if (parameterInOldModel == null && !hasDefault)
{
return(true);
}
return(false);
}
private static bool HasParameterBeenAddedWithoutDefaultValue(ModelRelationshipInstance parameter,
TSqlObject newProcedure)
{
var hasDefault = parameter.Object.GetProperty(Parameter.DefaultExpression) != null;
var parameterInOldModel =
newProcedure.GetReferencedRelationshipInstances(Procedure.Parameters)
.FirstOrDefault(p => p.ObjectName.Parts.Last() == parameter.ObjectName.Parts.Last());
/*
A parameter did not exist in the old model and has no default
*/
if (parameterInOldModel == null && !hasDefault)
{
return true;
}
return false;
}
private void AddPropertiesForDmlTrigger(Panel panel, TSqlObject key)
{
var duplicateRemover = new Dictionary<string, bool>();
foreach (var reference in key.GetReferencedRelationshipInstances(DmlTrigger.BodyDependencies))
{
var name = reference.ObjectName.ToString();
if (!duplicateRemover.ContainsKey(name))
{
panel.Children.Add(GetPropertyLabel("Dependency ", name));
duplicateRemover[name] = true;
}
}
}
private void AddPropertiesForDefaultConstraint(Panel panel, TSqlObject key)
{
foreach (var reference in key.GetReferencedRelationshipInstances(DefaultConstraint.TargetColumn))
{
panel.Children.Add(GetPropertyLabel("Target Column: ", reference.ObjectName.ToString()));
}
}
private void AddPropertiesForPrimaryKey(Panel panel, TSqlObject key)
{
foreach (var reference in key.GetReferencedRelationshipInstances(PrimaryKeyConstraint.Columns))
{
panel.Children.Add(GetPropertyLabel("Column: ", reference.ObjectName + " " + (reference.GetProperty<bool>(PrimaryKeyConstraint.ColumnsRelationship.Ascending) ? "ASC" : "DESC")));
}
}
private void AddPropertiesForForeignKey(Panel panel, TSqlObject key)
{
foreach (var reference in key.GetReferencedRelationshipInstances(ForeignKeyConstraint.Columns))
{
panel.Children.Add(GetPropertyLabel("Column: ", reference.ObjectName.ToString()));
}
foreach (var reference in key.GetReferencedRelationshipInstances(ForeignKeyConstraint.ForeignTable))
{
panel.Children.Add(GetPropertyLabel("Foreign Table: ", reference.ObjectName.ToString()));
}
foreach (var reference in key.GetReferencedRelationshipInstances(ForeignKeyConstraint.ForeignColumns))
{
panel.Children.Add(GetPropertyLabel("Foreign Column: ", reference.ObjectName.ToString()));
}
}
private bool HasDefaultValueBeenRemoved(ModelRelationshipInstance parameter, TSqlObject oldProcedure)
{
var newParameterHasDefault = parameter.Object.GetProperty(Parameter.DefaultExpression) != null;
var parameterInOldModel =
oldProcedure.GetReferencedRelationshipInstances(Procedure.Parameters)
.FirstOrDefault(p => p.ObjectName.Parts.Last() == parameter.ObjectName.Parts.Last());
/*
A parameter in the new model has no default but it had a default in the old model...
*/
if (parameterInOldModel != null && !newParameterHasDefault && (parameterInOldModel.Object.GetProperty(Parameter.DefaultExpression) != null))
{
return true;
}
return false;
}
/// <summary>
/// Filter referenced objects to only return composed children. These are objects that have a real
/// parent-child relationship and couldn't be defined on their own. The canonical example is that
/// a Table->Column is a composing relationship and we'd only get to navigate to the Column via the Table
/// reference. To avoid loops we don't want to traverse Hierarchical or Peer relationships. Those can refer to other
/// top-level objects or objects that are composed children of a different top-level object, and hence would
/// cause us to iterate multiple times over an object in the model.
///
/// Note that <see cref="TSqlObject.GetReferencedRelationshipInstances()"/> may return relationships
/// where there is no object to in the model for the reference. This can happen if the object on that
/// side of the relationship is from a referenced dacpac. Hence we check that the
/// <see cref="ModelRelationshipInstance.Object"/> is not null when filtering our objects
/// </summary>
private IEnumerable<TSqlObject> GetComposedChildren(TSqlObject tSqlObject)
{
return from rel
in tSqlObject.GetReferencedRelationshipInstances(DacExternalQueryScopes.UserDefined)
where rel.Relationship.Type == RelationshipType.Composing && rel.Object != null
select rel.Object;
}
private List<ColumnDescriptor> GetColumnDefinitions(TSqlObject table)
{
var columns = new List<ColumnDescriptor>();
foreach (var column in table.GetReferencedRelationshipInstances(Microsoft.SqlServer.Dac.Model.Table.Columns))
{
var definition = CreateColumnDefinition(column);
if(definition != null)
columns.Add(definition);
}
return columns;
}
